In my model, I have a book and review class and I want to calculate the average rating. I use aggregate and Avg for that. What I want is to display the list of books with their average rating.

models.py

class Book(models.Model):
    author = models.ManyToManyField(Author, related_name='books')
    title = models.CharField(max_length=200)
    description = models.TextField(max_length=3000)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    publisher = models.CharField(max_length=200)
    language = models.CharField(max_length=200)
    pages = models.PositiveSmallIntegerField()
    isbn = models.CharField(max_length=13, validators=[validate_isbn(), MaxLengthValidator(13)])
    cover_image = models.ImageField(upload_to='books/images')
    publish = models.BooleanField(default=True)

    @property
    def average_rating(self):
        avg_rating = Review.objects.filter(book_id=self.id).aggregate(Avg('rating'))
        return avg_rating['rating__avg']

    def __str__(self):
        return self.title

class Review(models.Model):
    RATING = [
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, 5),
    ]

    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name='reviews')
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    description = models.TextField()
    rating = models.PositiveSmallIntegerField(choices=RATING, default=5)
    date_added = models.DateField(auto_now_add=True)

    objects = ReviewManager()

    def __str__(self):
        return f"{self.user.username} {self.book.title}"

Now for each book, I have one extra query

How can I fix this issue?

CodePudding user response：

please take a look at select_related and prefetch_related

Review.objects.filter(book_id=self.id).prefetch_related('author').select_related('author__book', 'author__user').aggregate(Avg('rating'))

CodePudding user response：

You can use the values() to query. as mentioned in the docs https://docs.djangoproject.com/en/4.1/ref/models/querysets/#django.db.models.query.QuerySet.values

from django.db.models import Avg

qs = Book.objects.values("id").annotate(average_rating=Avg('reviews__rating'))

The result will be similar to

qs
<QuerySet [{'id': 1, 'average_rating': 5.0}, {'id': 2, 'average_rating': 2.0}]>