How to filter objects for count annotation in Django?
Solution 1
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.
Solution 2
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()
)))
Solution 3
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`"
})
Solution 4
I would suggest to use the .values
method of your Participant
queryset instead.
For short, what you want to do is given by:
Participant.objects\
.filter(is_paid=True)\
.values('event')\
.distinct()\
.annotate(models.Count('id'))
A complete example is as follow:
-
Create 2
Event
s:event1 = Event.objects.create(title='event1') event2 = Event.objects.create(title='event2')
-
Add
Participant
s to them:part1l = [Participant.objects.create(event=event1, is_paid=((_%2) == 0))\ for _ in range(10)] part2l = [Participant.objects.create(event=event2, is_paid=((_%2) == 0))\ for _ in range(50)]
-
Group all
Participant
s by theirevent
field:Participant.objects.values('event') > <QuerySet [{'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 1}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, {'event': 2}, '...(remaining elements truncated)...']>
Here distinct is needed:
Participant.objects.values('event').distinct() > <QuerySet [{'event': 1}, {'event': 2}]>
What
.values
and.distinct
are doing here is that they are creating two buckets ofParticipant
s grouped by their elementevent
. Note that those buckets containParticipant
. -
You can then annotate those buckets as they contain the set of original
Participant
. Here we want to count the number ofParticipant
, this is simply done by counting theid
s of the elements in those buckets (since those areParticipant
):Participant.objects\ .values('event')\ .distinct()\ .annotate(models.Count('id')) > <QuerySet [{'event': 1, 'id__count': 10}, {'event': 2, 'id__count': 50}]>
-
Finally you want only
Participant
with ais_paid
beingTrue
, you may just add a filter in front of the previous expression, and this yield the expression shown above:Participant.objects\ .filter(is_paid=True)\ .values('event')\ .distinct()\ .annotate(models.Count('id')) > <QuerySet [{'event': 1, 'id__count': 5}, {'event': 2, 'id__count': 25}]>
The only drawback is that you have to retrieve the Event
afterwards as you only have the id
from the method above.
Solution 5
What result I am looking for:
- People (assignee) who have tasks added to a report. - Total Unique count of People
- People who have tasks added to a report but, for task whose billability is more than 0 only.
In general, I would have to use two different queries:
Task.objects.filter(billable_efforts__gt=0)
Task.objects.all()
But I want both in one query. Hence:
Task.objects.values('report__title').annotate(withMoreThanZero=Count('assignee', distinct=True, filter=Q(billable_efforts__gt=0))).annotate(totalUniqueAssignee=Count('assignee', distinct=True))
Result:
<QuerySet [{'report__title': 'TestReport', 'withMoreThanZero': 37, 'totalUniqueAssignee': 50}, {'report__title': 'Utilization_Report_April_2019', 'withMoreThanZero': 37, 'totalUniqueAssignee': 50}]>
Related videos on Youtube
Comments
-
rudyryk almost 2 years
Consider simple Django models
Event
andParticipant
:class Event(models.Model): title = models.CharField(max_length=100) class Participant(models.Model): event = models.ForeignKey(Event, db_index=True) is_paid = models.BooleanField(default=False, db_index=True)
It's easy to annotate events query with total number of participants:
events = Event.objects.all().annotate(participants=models.Count('participant'))
How to annotate with count of participants filtered by
is_paid=True
?I need to query all events regardless of number of participants, e.g. I don't need to filter by annotated result. If there are
0
participants, that's ok, I just need0
in annotated value.The example from documentation doesn't work here, because it excludes objects from query instead of annotating them with
0
.Update. 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() )))
Update 2. Django 2.0 has new Conditional aggregation feature, see the accepted answer below.
Update 3. For Django 3.x please check this answer below.
-
rudyryk about 9 yearsThanks Todor! Seems like I've found the way without using
.extra
, as I prefer to avoid SQL in Django :) I'll update the question. -
Todor about 9 yearsYou are welcome, btw I'm aware of this approach, but it was a non-working solution until now, that's why I didn't mention about it. However I just found that it has been fixed in
Django 1.8.2
, so i guess you are with that version and that's why its working for you. You can read more about that here and here -
SverkerSbrg over 7 yearsIs this an eligible solution when the matching items are many? Let us say that i want to count click events which occurred the latest week.
-
rudyryk about 7 yearsWhy not? I mean, why your case is different? In the case above there may by any number of paid participants on event.
-
Hayden Crocker almost 7 yearsI think the question @SverkerSbrg is asking is whether this is inefficient for large sets, rather than whether or not it would work.... correct? Most important thing to know is that it's not doing it in python, it's creating a SQL case clause - see github.com/django/django/blob/master/django/db/models/… - so it'll be reasonably performant, simple example would be better than a join, but more complex versions could include subqueries etc.
-
rudyryk over 6 yearsBTW, there's no such example by the documentation link, only
aggregate
usage is shown. Have you already tested such queries? (I haven't and I want to believe! :) -
Oli over 6 yearsI have. They work. I actually hit a weird patch where an old (super-complicated) subquery stopped working after upgrading to Django 2.0 and I managed to replace it with a super-simple filtered-count. There is a better in-doc example for annotations so I'll pull that in now.
-
Stefan Collier over 6 yearsI'm getting that this produces a None when it should be 0. Anyone else getting this?
-
Ryan Castner about 6 yearsThere are a few answers here, this is the Django 2.0 way, and below you will find the Django 1.11 (Subqueries) way, and the Django 1.8 way.
-
djvg over 5 yearsWhen using this with
Count
(instead ofSum
) I guess we should setdefault=None
(if not using the django 2filter
argument). -
djvg over 5 yearsBeware, if you try this in Django <2, e.g. 1.9, it will run without exception, but the filter simply is not applied. So it may appear to work with Django <2, but does not.
-
Adam Taylor over 4 years@StefanJCollier Yes, I got
None
too. My solution was to useCoalesce
(from django.db.models.functions import Coalesce
). You use it like this:Coalesce(Subquery(...), 0)
. There may be a better approach, though. -
Tobit about 4 yearsIf you need to add multiple filter you can add them in the Q() argument with separated by , as example filter=Q(participants__is_paid=True, somethingelse=value)
-
djvg over 3 years
-
Joseph Victor Zammit over 2 yearsThis is great because the approach in the more upvoted answer by Oli below, whlie "better" in terms of readability, results in "LEFT OUTER JOIN"s on MySQL. Which is very unfriendly in terms of performance. So upvoting both answers!
-
Alexander over 2 yearsYou can even use RawSQL instead of Q to do something more complicated
.annotate(lines=Count("mm_items", filter=RawSQL("coalesce(qty, sug_qty) > 0", [])))
it will just paste your sql likeFILTER (WHERE (coalesce(qty, sug_qty) > 0))
-
Matthijs Kooijman over 2 yearsThis does not look like an answer to this question. It involves annotation and filtering, but the original question asks about counting, and filtering the objects that are counted, not filtering based on the resulting count or sum.
-
Matthijs Kooijman over 2 yearsIs this an answer to the question? It looks more like a new question that you then answer yourself, that is somewhat related to the original question (the solution uses some of the same tools), but I'm not sure that this answer adds something new to the existing set of answers...