Annotate filtering -- sum only some of related objects' fields

Question

Let's say there's an Author and he has Books. In order to fetch authors together with the number of written pages, the following can be done:

Author.objects.annotate(total_pages=Sum('book__pages'))

But what if I wanted to sum pages of sci-fi and fantasy books separately? I'd like to end up with an Author, that has total_pages_books_scifi_pages and total_pages_books_fantasy_pages properties.

I know I can do following:

Author.objects.filter(book__category='scifi').annotate(total_pages_books_scifi_pages=Sum('book__pages'))
Author.objects.filter(book__category='fantasy').annotate(total_pages_books_fantasy_pages=Sum('book__pages'))

But how do it in one queryset?

Answer 1

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

categories = ['scifi', 'fantasy']
annotations = {}

for category in categories:
    annotation_name = 'total_pages_books_{}'.format(category)
    case = Case(
        When(book__category=category, then=F('book__pages')),
        default=0,
        output_field=IntegerField()
    )
    annotations[annotation_name] = Sum(case)

Author.objects.filter(
    book__category__in=categories
).annotate(
    **annotations
)

Answer 2

Try:

Author.objects.values("book__category").annotate(total_pages=Sum('book__pages'))

From Django docs: https://docs.djangoproject.com/en/1.10/topics/db/aggregation/#values:

values()

Ordinarily, annotations are generated on a per-object basis - an annotated QuerySet will return one result for each object in the original QuerySet. However, when a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.