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?

Vladimir Danilov · Accepted Answer · 2016-07-25 12:27:50Z

11

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
)

edited Jul 25, 2016 at 12:27

answered Jul 25, 2016 at 12:17

Vladimir Danilov

2,39816 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A K · Accepted Answer · 2017-03-08 15:52:46Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related