Search code examples
djangodjango-querysetdjango-aggregation

Django conditional annotation

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')
Solution

  • 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.