Django: Filter by aggregate Count using annotate

A very simple problem in SQL made a little unclear by the Django modelling system.

Thankfully, like the rest of Django it's quite easy to use.

qs = Category.objects.filter(user = current_user)
qs = qs.annotate(num_items = Count(items'))
qs = qs.filter(num_items__gt = 0)

In this example, we're able to filter prior to grouping using annotate(), then using the annotated field name "num_items" to filter out any categories without items.

The reason why we use annotate() rather than aggregate() is because annotate() will simply create an alias for the Count (or Avg, Min, Max, etc).

The aggregate() function on the other hand will execute the QuerySet and return the results, not making it possible to do further filters.

Another building block for knowledge.


Copyright © Twig's Tech Tips
Theme by BloggerThemes & TopWPThemes Sponsored by iBlogtoBlog