Get average of multiple ratings values from different model instances (Django)-CodePudding

I'm working on this web-app that lets a project manager rate a vendor after finishing a task/job. Here is the models.py content:

class Rating(models.Model):
    RATE_CHOICES = [
        (1, 'Below Expectation greater than 0.02'),
        (2, 'Meet Expectaion 0.02'),
        (3, 'Exceed Expectaions less than 0.02'),
    ]
    
    reviewer = models.ForeignKey(CustomUser, related_name="evaluator", 
    on_delete=models.CASCADE, null=True, blank=True)
    reviewee = models.ForeignKey(Vendor, null=True, blank=True, 
     related_name="evaluated_vendor", on_delete=models.CASCADE)
    job = models.ForeignKey(Job, on_delete=models.CASCADE, null=True, blank=True, 
    related_name='jobrate')

    date = models.DateTimeField(auto_now_add=True)
    text = models.TextField(max_length=200, blank=True)
    rate = models.PositiveSmallIntegerField(choices=RATE_CHOICES)

class Job(models.Model):
    title = models.CharField(null=True, blank=True, max_length=100)
    project_manager = models.ForeignKey(CustomUser, on_delete = models.CASCADE, 
    related_name="assigned_by")
    startDate = models.DateField(blank=True, null=True)
    deadlineDate = models.DateField(blank=True, null=True)
    status = models.CharField(choices=STATUS, default='In Preparation', max_length=100)
    evaluated = models.BooleanField(default=False)
    #Related Fields
    purchaseOrder = models.ForeignKey(PurchaseOrder, blank=True, null=True, 
    on_delete=models.CASCADE)
    project = models.ForeignKey(Project, blank=True, null=True, 
    on_delete=models.CASCADE, related_name="Main_Project")
    assigned_to = models.ForeignKey(Vendor, blank=True, null=True, 
    on_delete=models.CASCADE, related_name="Job_owner")

class Vendor(models.Model):

    #Basic Fields.
    vendorName = models.CharField(null=True, blank=True, max_length=200)
    addressLine1 = models.CharField(null=True, blank=True, max_length=200)
    country = models.CharField(blank=True, choices=COUNTRY_CHOICES, max_length=100)
    postalCode = models.CharField(null=True, blank=True, max_length=10)
    phoneNumber = models.CharField(null=True, blank=True, max_length=100)
    emailAddress = models.CharField(null=True, blank=True, max_length=100)
    taxNumber = models.CharField(null=True, blank=True, max_length=100)
    mother_language = models.CharField(blank=True, choices=LANGUAGE_CHOICES, 
    max_length=300)

Here is my view.py:

@login_required
def vendors(request):
    context = {}
    vendors = Vendor.objects.all()
    context['vendors'] = vendors
    

    if request.method == 'GET':
        form = VendorForm()
        context['form'] = form
        return render(request, 'projects/vendors.html', context)

    if request.method == 'POST':
        form = VendorForm(request.POST, request.FILES)

        if form.is_valid():
            form.save()

            messages.success(request, 'New Vendor Added')
            return redirect('vendors')
        else:
            messages.error(request, 'Problem processing your request')
            return redirect('vendors')


    return render(request, 'projects/vendors.html', context)

Then In my HTML, I want to render the average rating of multiple jobs done by a given vendor in an Average rate column. For Example if a vendor completed 2 jobs and get ratings of 1 and 2 respectively. The average rate for the vendor will be 1.5. I want the table to show this average rate.

Here is my current setting in my HTML:

 <table >
      <thead>
        <tr>
          <th scope="col">Vendor Name</th>
          <th scope="col">Mother Tongue</th>
          <th scope="col"> Average Rating</th>
          <th scope="col">Email Address</th>
          <th scope="col">Phone Number</th>
          <th scope="col">Address Line</th>
          <th scope="col">Country</th>
          
        </tr>
      </thead>
      <tbody>
        
      {% for vendor in vendors %}
      <tr>
        <td>{{vendor.vendorName}}</td>
        <td>{{vendor.mother_language}}</td>
        <td>{{vendor.averagerating | stringformat:".2f"}}</td>
        <td>{{vendor.emailAddress}}</td>
        <td>{{vendor.phoneNumber}}</td>
        <td>{{vendor.addressLine1}}</td>
        <td>{{vendor.country}}</td>
       
      </tr>
        {% endfor %}

      </tbody>
    </table>

So the question is How can I get the average rating of a given vendor for all jobs that he /she has done and display it in my Vendor List Table. Note that the average rating column currently doesn't display nothing it is empty. Your answer will be appreciated. Thanks in advance.

CodePudding user response：

Annotate the vendors in your view with an average of the related rating objects.

vendors = Vendor.objects.all().annotate(
    averagerating=Avg("evaluated_vendor__rate"),
)

See this section of the docs for details on how this works.

CodePudding user response：

Related to AKX post: You could also try to get an aggregation from the Rating model like this:

vendor_ranking = Rating.objects.filter(reviewee=vendor).aggregate(Avg("rate"))