Aggregate makes extra queries

Question

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?

Answer 1

I used annotate in the ViewSet and my problem solved

views.py

class BookViewSet(ModelViewSet):
    http_method_names = ["get", "post", "patch", "delete"]
    queryset = Book.objects.prefetch_related('author').annotate(average_rating=Avg('reviews__rating'))

    def get_serializer_class(self):
        if self.action == 'list':
            return BookSerializer
        else:
            return BookDetailSerializer

serializer.py

class BookSerializer(serializers.ModelSerializer):
    average_rating = serializers.FloatField()
    isbn = serializers.IntegerField()

    class Meta:
        model = Book
        fields = [
            "id",
            "author",
            "title",
            "description",
            "price",
            "publisher",
            "language",
            "pages",
            "isbn",
            'publish',
            "cover_image",
            'average_rating',
        ]