[FIXED] How can I filter an annotated query set

Issue

I have the code below that returns an aggregate of authors that have a book and the total amount of authors. This works well, but I want to get the count of authors that have a particular type of book and the total count of books. I have tried so many ways but giving wrong results. I would like to achieve this using annotate and aggregate as it will help performance. Below is my current code that works but it returns all authors with a book and total books, but I want authors with a particular type of book.

from django.db.models import Count, Case, When, IntegerField, Sum


data = self.author_set.annotate(
    total_books=Count("book", distinct=True),
).annotate(
    has_book=Case(
        When(total_books__gt=0, then=1),
        default=0,
        output_field=IntegerField(),
    )
).aggregate(
    authors_with_books=Sum("has_book", default=0),
    authors_count=Count("pk", distinct=True),
)

My Book model

class Book(models.Model):
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    title = models.CharField(null=True, blank=True, max_length=50)
    narration = models.CharField(null=True, blank=True, max_length=200)
    book_type = models.IntegerField(default=1)
    released = models.DateField()

I have tried many tweaks but it returns wrong data. Please help

Solution

You can achieve this by combining the Count function with Q object filter.

from django.db.models import Count, Case, When, IntegerField, Sum, Q


book_type: int = 2

data = (
    self.author_set.annotate(
        total_books=Count(
            "book", 
            distinct=True, 
            filter=Q(book__book_type=book_type) # Adding this will get the job done.
        )
    )
    .annotate(
        has_book=Case(
            When(total_books__gt=0, then=1),
            default=0,
            output_field=IntegerField(),
        )
    )
    .aggregate(
        authors_with_books=Sum("has_book", default=0),
        authors_count=Count("pk", distinct=True),
    )
)

Answered By - Temi