How to filter objects for count annotation in Django?
Just discovered that Django 1.8 has new conditional expressions feature, so now we can do like this:
events = Event.objects.all().annotate(paid_participants=models.Sum(
models.Case(
models.When(participant__is_paid=True, then=1),
default=0, output_field=models.IntegerField()
)))
Conditional aggregation in Django 2.0 allows you to further reduce the amount of faff this has been in the past. This will also use Postgres' filter
logic, which is somewhat faster than a sum-case (I've seen numbers like 20-30% bandied around).
Anyway, in your case, we're looking at something as simple as:
from django.db.models import Q, Count
events = Event.objects.annotate(
paid_participants=Count('participants', filter=Q(participants__is_paid=True))
)
There's a separate section in the docs about filtering on annotations. It's the same stuff as conditional aggregation but more like my example above. Either which way, this is a lot healthier than the gnarly subqueries I was doing before.
UPDATE
The sub-query approach which I mention is now supported in Django 1.11 via subquery-expressions.
Event.objects.annotate(
num_paid_participants=Subquery(
Participant.objects.filter(
is_paid=True,
event=OuterRef('pk')
).values('event')
.annotate(cnt=Count('pk'))
.values('cnt'),
output_field=models.IntegerField()
)
)
I prefer this over aggregation (sum+case), because it should be faster and easier to be optimized (with proper indexing).
For older version, the same can be achieved using .extra
Event.objects.extra(select={'num_paid_participants': "\
SELECT COUNT(*) \
FROM `myapp_participant` \
WHERE `myapp_participant`.`is_paid` = 1 AND \
`myapp_participant`.`event_id` = `myapp_event`.`id`"
})