Django annotation with nested filter

Question

Is it possible to filter within an annotation?

In my mind something like this (which doesn't actually work)

Student.objects.all().annotate(Count('attendance').filter(type="Excused"))

The resultant table would have every student with the number of excused absences. Looking through documentation filters can only be before or after the annotation which would not yield the desired results.

A workaround is this

for student in Student.objects.all():
    student.num_excused_absence = Attendance.objects.filter(student=student, type="Excused").count()

This works but does many queries, in a real application this can get impractically long. I think this type of statement is possible in SQL but would prefer to stay with ORM if possible. I even tried making two separate queries (one for all students, another to get the total) and combined them with |. The combination changed the total :(

Some thoughts after reading answers and comments

I solved the attendance problem using extra sql here.

• Timmy's blog post was useful. My answer is based off of it.
• hash1baby's answer works but seems equally complex as sql. It also requires executing sql then adding the result in a for loop. This is bad for me because I'm stacking lots of these filtering queries together. My solution builds up a big queryset with lots of filters and extra and executes it all at once.
• If performance is no issue - I suggest the for loop work around. It's by far the easiest to understand.

Answer 1

As of Django 1.8 you can do this directly in the ORM:

students = Student.objects.all().annotate(num_excused_absences=models.Sum(
    models.Case(
        models.When(absence__type='Excused', then=1),
    default=0,
    output_field=models.IntegerField()
)))

Answer adapted from another SO question on the same topic

I haven't tested the sample above but did accomplish something similar in my own app.

Answer 2

You are correct - django does not allow you to filter the related objects being counted, without also applying the filter to the primary objects, and therefore excluding those primary objects with a no related objects after filtering.

But, in a bit of abstraction leakage, you can count groups by using a values query.

So, I collect the absences in a dictionary, and use that in a loop. Something like this:

# a query for students
students = Students.objects.all()
# a query to count the student attendances, grouped by type.
attendance_counts = Attendence(student__in=students).values('student', 'type').annotate(abs=Count('pk'))
# regroup that into a dictionary {student -> { type -> count }}
from itertools import groupby
attendance_s_t = dict((s, (dict(t, c) for (s, t, c) in g)) for s, g in groupby(attendance_counts, lambda (s, t, c): s))
# then use them efficiently:
for student in students:
    student.absences = attendance_s_t.get(student.pk, {}).get('Excused', 0)