I have a “Highest Rated” list of albums I want ordered by an average of ratings from both an Album model and a Review model.
I was able to accumulate the averages with an
avg_rating custom model field I built in the
Album model:
class Album(models.Model):
creator = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
artist = models.CharField(max_length=255)
title = models.CharField(max_length=255)
# image =
rating = models.IntegerField(
choices=[(i, i) for i in range(1, 11)],
validators=[MaxValueValidator(10), MinValueValidator(1)],
default=10,
)
comment = models.TextField(null=True, blank=True)
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(default=timezone.now)
# vvv HERE IS THE CUSTOM MODEL vvv
def avg_rating(self):
review_ratings = 0
orig_rating = self.rating
review_ratings_count = self.review_set.count()
if review_ratings_count > 0:
for review in self.review_set.all():
review_ratings += review.rating
ratings_sum = review_ratings + orig_rating
ratings_count = review_ratings_count + 1
ratings_avg = ratings_sum / ratings_count
avg_return = round(ratings_avg, 1)
if avg_return == 10.0:
avg_return = 10
else:
avg_return = avg_return
return avg_return
else:
return self.rating
...
class Review(models.Model):
reviewer = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
album = models.ForeignKey(Album, on_delete=models.CASCADE)
rating = models.IntegerField(
choices=[(i, i) for i in range(1, 11)],
validators=[MaxValueValidator(10), MinValueValidator(1)],
default=10,
)
comment = models.TextField()
updated = models.DateTimeField(auto_now=True)
created = models.DateTimeField(default=timezone.now)
But when I try to order the “Highest Rated” list in my view like so:
ratings_list = Album.objects.all().order_by('-avg_rating')[0:5]
…it doesn’t work.
Do I have to create another custom model just so I can order the list by the averages?
In order to use the order_by clause with the “average rating” value, it must exist in the database. Something like this should work:
from django.db.models import Sum, Count
from django.db.models.functions import Round
ratings_list = Album.objects.annotate(
# This is an average rating without `Album.rating`. You'll need to update this calculation.
db_avg_rating=Round(Sum("review_set__rating") / Count("review_set")),
).order_by("-db_avg_rating")[0:5]
I want to nit at a term you used:
I was able to accumulate the averages with an avg_rating custom model field I built in the Album model:
You created a model method. This distinction is important. The database has no idea about this method. It is not a field. The calculation as you had written it is entirely in python.
Sorry, @massover, I’ve been on the road. I will try this, thank you!
@massover, I get an error when I use “review_set” as a lookup: “django.core.exceptions.FieldError: Cannot resolve keyword 'review_set' into field. Choices are: artist, comment, created, creator, creator_id, id, rating, review, title, updated”
I thought I was close with this query:
ratings_list = Album.objects.annotate(
avg_rating=Round((Sum('review__rating') + Sum('rating')) / (Count('review__rating') + 1))
).order_by('-avg_rating')
But I think this just returns an average from only one of the ratings in album.review__rating.
My original syntax with review_set was not correct. Your query looks correct as we can see in the docs for following relationships backwards.
If you’re not sure, I’d suggest you write a test (or some code in the shell), for example something like:
from django.test import TestCase
class TestAlbum(TestCase):
def test_annotations(self):
album = Album.objects.create()
rating6 = Rating.objects.create(album=album, rating=6)
rating8 = Rating.objects.create(album=album, rating=8)
annotated_album = Album.objects.annotate(
sum=Sum("review__rating"),
count=Count("review"),
avg=F("sum") / F("count"),
).first()
self.assertEqual(annotated_album.sum, 14)
self.assertEqual(annotated_album.count, 2)
self.assertEqual(annotated_album.avg, 7)
@massover, mind if I ask a question? Due to my query above not working, I’m trying to take this back to basics.
So when I create this query:
ratings_list = Album.objects.annotate(
num_of_review_ratings=Count('review__rating')
)
I get the correct number of ratings from Album.review.rating.
I get the correct results when I create the same kind of query for Album.rating.
But when I create a query to try to get the total of Album.ratings and Album.review.ratings:
ratings_list = Album.objects.annotate(
total_count=(Count('rating', distinct=True) + Count('review__rating', distinct=True))
)
I get incorrect results. The total of the Count()s added up is not correct.
Have you tried setting up a test?
Try annotating each field separately. What do you get when you count each field? Is it what you expect? What do get after you add the distinct? Is it what you expect?
@massover, thanks for getting me to check the differences in adding distinct to the separate fields. It helped a lot. Now I’m SO close to getting this problem solved! I put this query together and I can feel I’m close to coding this correctly but I’m still not getting the results I want:
ratings_list = Album.objects.annotate(
ratings_avg=Round((Sum('rating', distinct=True) + Sum('review__rating'))
/ (Count('review__rating') + 1), precision=1)
).order_by('-ratings_avg')
From the image in my original post, the order and results should be:
The War on Drugs - 9.5
The Sundays - 8.3
The Black Keys - 8.0
Nine Inch Nails - 8.0
But the order and results I’m getting are:
The War on Drugs - 9
The Black Keys - 8
Nine Inch Nails - 8
The Sundays - 8
@massover, BTW…
ratings_list = Album.objects.annotate(
ratings_sum=(Sum('rating', distinct=True) + Sum('review__rating'))
).order_by('-ratings_sum')
…and…
ratings_list = Album.objects.annotate(
ratings_count=(Count('review__rating') + 1)
).order_by('-ratings_count')
…give the correct results and ordering. I’m just having difficulty combining them into one query.
What happens if you put the two annotate calls in the same query?
e.g.
Album.objects.annotate(...).annotate(...).order_by('-ratings_sum')
@KenWhitesell, the Album objects are ordered by the total sum of ALL the ratings. (But not the average of all the ratings. – But I feel you’re leading me somewhere. 
)
Ahh, no - my mistake. I missed the difference in the order_by calls.
Nope, since you want two different orderings, you’re going to need to do this with two separate queries.
@KenWhitesell, I was already able to display the averages for each Album object. But now I need to find a way to order them by average in views.py.
Ok, I’m kinda lost here with what you’re trying to produce.
Your last two queries have dropped the ratings_avg calculation. (Which, BTW, is returning an integer because the values you’re working with are all integer values. You’d need to cast them to Float for the division to return a floating point value.)
@KenWhitesell, I split apart the original query:
ratings_list = Album.objects.annotate(
ratings_avg=Round((Sum('rating', distinct=True) + Sum('review__rating'))
/ (Count('review__rating') + 1), precision=1)
).order_by('-ratings_avg')
…just to show that the two different parts work on their own.
But this original ratings_avg query isn’t returning the correct results.
Would I need to use output_field=FloatField() for it to return the correct results?
Well, it is, for some definition of “correct”. 
Not on the final result, no. The Cast needs to be before the division is performed.
I’m not entirely sure if you need to Cast the entire numerator:
or if you need to cast both of them, but I think that either way should be sufficient.
1 Like
