Aggregate an average from two fields from separate models

I have two models which have a many-to-one relationship:

class Review(models.Model):
  artist = models.CharField(max_length=100)
  album = models.CharField(max_length=200)
  rating = models.IntegerField(
    validators=[MinValueValidator(0), MaxValueValidator(10)],
    default=10,
  )
  ...

class AddedReview(models.Model):
  user = models.ForeignKey(User, on_delete=models.CASCADE)
  review = models.ForeignKey(Review, on_delete=models.CASCADE)
  rating = models.IntegerField(default=10)
  ...

When a Review is created by a user, other users will then be able to add their own AddedReview to the Review, and add their own rating to the initial Review.
I know how to aggregate an average of the AddedReview ratings but I’m having difficulty adding those AddedReview ratings to the initial Review rating. Is it possible to gather both models’ ratings to create an average for them all?

Yes.

If you query on the Review and annotate each instance with the sum of the AddedReview.rating and the count of AddedReview.rating, you can then annotate the Review with that sum plus the Review.rating, that count + 1, and then annotate with the rating sum divided by the count.

Just to inquire: does the Many-To-One relationship of the AddedReview model with the Review model allow me to build an aggregate average between the two models? Or do I need to add a One-To-Many relationship from the Review model to the AddedReview model?

I’m sorry, I’m not sure I’m following what you’re trying to ask here.

In general, I’m going to say that the answer to your first question is yes.

All relationships in Django are defined as Many-To-One because by definition, the ForeignKey field is the “Many” side of the relationship. By defining that ForeignKey field, Django automatically creates the reverse-relationship manager. See Related objects reference | Django documentation | Django

Side note: What you really have created with the AddedReview class is a Many-To-Many relationship between User and Review, with AddedReview as the “through” table.

After working on this in my Python shell, I managed to get the correct number for my ratings_avg with this code:

def average(request, pk):
  added_ratings_sum = Review.objects.get(id=pk).aggregate(Sum('addedreview__rating'))

  for sum in added_ratings_sum.values():
    a_r_sum = sum

  orig_ratings_sum = Review.objects.get(id=pk).aggregate(Sum('rating'))

  for sum in orig_ratings_sum.values():
    r_sum = sum

  total_sum = a_r_sum + r_sum

  ratings_count = Review.objects.get(id=pk).addedreview_set.count() + 1

  ratings_avg = total_sum / ratings_count

  return render(request, 'base/feed_component.html', {'ratings_avg': ratings_avg})

But when I add this tag in my feed_component.html, the number doesn’t show:
<p>Avg. Rating: <span class="rating">{{ ratings_avg }}</span></p>

Do I need to add a pk in my tag?

First thing I notice is that you’re trying to use sum as a variable, when it’s a predefined function in Python

Aside from that, I don’t really see anything wrong. I’d suggest adding some print statements in this view to be able to verify that the values are what you expect them to be each step along the way.

Could the structure of my project be the source behind why the {{ ratings_avg }} tag isn’t working?

record_review/
   base/
      views.py
      models.py
      templates/
         base/
            home.html
            feed_component.html
   templates/
      main.html
      navbar.html

The feed_component.html file is included in the home.html file.
Perhaps the {{ ratings_avg }} can’t be found through the url path?

Also, if I put the code inside the average() view in my already existing home() view, I get this error (adding a pk param in home(): `home() missing 1 required positional argument: ‘pk’.

URLs aren’t involved in the rendering process. All the rendering occurs in the server before the page is sent to the browser.

The urls being used to call a view need to match the requirement of the view. If the view requires a parameter, then the url definition must contain that parameter.

I am a little curious about the way you worded this.

How are you calling average? Is there a url assigned to that view?

So if I have an already existing url for each review:

path('review/<str:pk>/', views.review, name="review"),

…should I have put the code from my average() view inside the code for my review() view?

(Sorry if this is a simple question but I’m still so new to Django.)

Or you can call that function from your view.

Somehow that code needs to be executed in order for that data to be available to be rendered.

And if average is not the view, then it should not be rendering a template. It should be returning the values to be included in the caller’s context to be rendered by the caller.

I think I’ve figured out my problem. The template where I’m trying to populate my ratings_avg is in a for loop {% for review in reviews %}:

{% for review in reviews %} 
  <div class="card mb-3">
    <div class="card-header py-3">
      <div class="d-flex align-items-center">
        <div class="flex-shrink-0 d-none d-md-block">
          <img src="images/#" class="rounded review-img" />
        </div>
        <div class="flex-grow-1 ps-md-3">
          <div class="d-flex justify-content-between album-header">
            <h5 class="card-title"><a href="{% url 'review' review.id %}">{{ review.artist }} - {{ review.album }}</a></h5>
            <p>Avg. Rating: <span class="rating">{{ ratings_avg }}</span></p>
          </div>
          <div class="d-flex justify-content-between user-header">
            <p class="text-muted">Reviewed by <a href="{% url 'user-profile' review.author.id %}" class="username">{{ review.author }}</a> on {{ review.created|date:"N j, Y" }}</p>
            <p class="text-muted">User Rating: <span class="added-rating">{{ review.rating }}</span></p>
          </div>
          ...

So the ratings_avg needs to be a field in my Review model(?)
Can you point me in the right direction for creating an average aggregate in my Review model, pulling the averages from both my Review model and my AddedReview model?

Is it possible to create a model method in my Review model and extract the result from the method to populate it in an avg_ratings field in my Review model?

I’m not following what you’re trying to describe here.

You can create a model method in a Model, and render it directly in your template.

For example, if you have the following method in a model:

def double_id(self):
    return self.id * 2

and in your context you have an instance of that model an_instance, then in your template, you can render {{ an_instance.double_id }}

See the greenish “Behind the scenes” box in Variables.

1 Like

Thanks, @KenWhitesell! I figured out that I can use the name of my model method as a variable to be used in my template: def avg_rating(self):{{ review.avg_rating }}
(Well, I mean, you figured that out for me. Thanks!)