StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODjango annotation with nested filter
text
Body
copied!<p>Is it possible to filter within an annotation? </p> <p>In my mind something like this (which doesn't actually work)</p> <p><code>Student.objects.all().annotate(Count('attendance').filter(type="Excused"))</code></p> <p>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.</p> <p>A workaround is this</p> <pre><code>for student in Student.objects.all(): student.num_excused_absence = Attendance.objects.filter(student=student, type="Excused").count() </code></pre> <p>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 :(</p> <h1>Some thoughts after reading answers and comments</h1> <p>I solved the attendance problem using extra sql <a href="https://github.com/burke-software/django-sis/blob/a43cb7bfa8a99fdabdd7a783f55139b484b28d3c/ecwsp/sis/scaffold_reports.py#L118">here</a>. </p> <ul> <li><a href="https://timmyomahony.com/blog/filtering-annotations-django/">Timmy's blog post</a> was useful. My answer is based off of it.</li> <li>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.</li> <li>If performance is no issue - I suggest the for loop work around. It's by far the easiest to understand.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload