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.
@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 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:
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:
@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. )
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.)
…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?
That is not an accurate statement. There is no requirement to use the related_name attribute in those situations. If you are getting an error without it, then you have something else that isn’t correct.
Thanks for your reply, I am not pretty sure what’s happening though regarding learning Django for just a few weeks.
api_views.py
class BookViewSet(viewsets.ReadOnlyModelViewSet):
queryset = Book.objects.prefetch_related('review_set').annotate(
avg_rating=Avg('review_set__rating')
)
serializer_class = BookSerializer
models.py
class Publisher(models.Model):
name = models.CharField(max_length=50, help_text="Name of Publisher.")
website = models.URLField(blank=True, help_text="Website of Publisher.")
email = models.EmailField(blank=True, help_text="Email of Publisher.")
def __str__(self):
return self.name
class Book(models.Model):
title = models.CharField(max_length=70, help_text="Title of the Book.")
publication_date = models.DateField(verbose_name="Publication date of the Book.")
isbn = models.CharField(blank=True, max_length=20, verbose_name="ISBN number of the Book.")
publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
contributors = models.ManyToManyField("Contributor", through="BookContributor")
cover = models.ImageField(upload_to="book_covers/", blank=True)
sample = models.FileField(upload_to="book_samples/", blank=True)
def __str__(self):
return self.title
class Review(models.Model):
content = models.TextField(help_text="The Review text.")
RATING_CHOICES = [(i, '☆' * i) for i in range(1, 6)]
rating = models.IntegerField(choices=RATING_CHOICES, help_text="The rating the reviewer has given.")
date_created = models.DateTimeField(auto_now_add=True, help_text="The date and time the review was created.")
date_edited = models.DateTimeField(auto_now_add=True, help_text="The date and time the review was edited.")
creator = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
book = models.ForeignKey(Book, on_delete=models.CASCADE, help_text="The Book that this review is for.")
def __str__(self):
return self.book.title
Side note: Please do not post images of text data here. Copy past the text data into the body of your post, surrounded by lines of three backtick - ` characters. This means you’ll have a line of ```, then your lines of text, then another line of ```.
Your syntax for the annotate is incorrect. In that situation, it should be avg_rating=Avg('review__rating')
)