Django conditional annotation

Question

I'm surprised that this question apparently doesn't yet exist. If it does, please help me find it.

I want to use annotate (Count) and order_by, but I don't want to count every instance of a related object, only those that meet a certain criteron.

To wit, that I might list swallows by the number of green coconuts they have carried:

swallow.objects.annotate(num_coconuts=Count('coconuts_carried__husk__color = "green"').order_by('num_coconuts')

score 18 · Answer 1 · answered Nov 09 '16 at 21:58

For Django >= 1.8:

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

swallow.objects.annotate(
    num_coconuts=Sum(Case(
        When(coconuts_carried__husk__color="green", then=1),
        output_field=IntegerField(),
    ))
).order_by('num_coconuts')

score 10 · Accepted Answer · answered Aug 09 '11 at 18:56

10

This should be the right way.

swallow.objects.filter(
    coconuts_carried__husk__color="green"
).annotate(
    num_coconuts=Count('coconuts_carried')
).order_by('num_coconuts')

Note that when you filter for a related field, in raw SQL it translates as a LEFT JOIN plus a WHERE. In the end the annotation will act on the result set, which contains only the related rows which are selected from the first filter.

answered Aug 09 '11 at 18:56

rewritten

16,280
2
47
50

Indeed this does seem to work, but for reasons you point out, it's utterly counter-intuitive. I'd expect this queryset to give me all swallows that have ever carried at least one green coconut, ordered by the total number of coconuts carried regardless of color. – jMyles Aug 09 '11 at 19:02
1

@jMyles: the documentation points out that this depends on the [order of the annotate and filter clauses](https://docs.djangoproject.com/en/1.3/topics/db/aggregation/#order-of-annotate-and-filter-clauses) - the other way round, it will behave as you describe. – Daniel Roseman Aug 09 '11 at 19:07
2

What if you want to include the swallow's that haven't carried any green coconuts? – Gordon Wrigley Oct 28 '13 at 09:17
@tolomea a lot of time has passed, and Django may have changed meanwhile, but a LEFT JOIN preserves the records where the right part of the join is empty, and sets the columns as NULL. This should translate to including the no-green-coconut-carrying swallows. – rewritten Oct 28 '13 at 14:03

Django conditional annotation

2 Answers2

Linked