[Django]-How to filter Django annotations on reverse foreign key fields

by

8👍

✅

You need to .filter(..) before the .annotate(..), so:

Authors.object.filter(
    books__review="GOOD"  # before the annotate
).annotate(
    n_good_books=Count("books")
)

This will result in a QuerySet of Authors, where each Author has an extra attribute .n_good_books that contains the number of good Books. The opposite means that you only will retrieve Authors for which at least one related Book has had a good review. As is specified in the documentation:

When used with an annotate() clause, a filter has the effect of
constraining the objects for which an annotation is calculated. For example, you can generate an annotated list of all books that have
a title starting with “Django” using the query:

>>> from django.db.models import Count, Avg
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

(..)

Annotated values can also be filtered. The alias for the annotation can be used in filter() and exclude() clauses in the
same way as any other model field.

For example, to generate a list of books that have more than one
author, you can issue the query:

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

This query generates an annotated result set, and then generates a
filter based upon that annotation.

The Count(..., filter=Q(..)) approach only works since , so in this will not work.

👤willeM_ Van Onsem

1👍

@willem-van-onsem has the correct answer to the question I asked.

However, if I wanted to get a count for all book types at once, I could do something like:

from django.db.models import Case, When, IntegerField

Authors.object.annotate(
  n_bad_books=Count(Case(When(books__review="BAD", then=1), output_field=IntegerField())),
  n_meh_books=Count(Case(When(books__review="MEH", then=1), output_field=IntegerField())),
  n_good_books=Count(Case(When(books__review="GOOD", then=1), output_field=IntegerField())),
  n_great_books=Count(Case(When(books__review="GREAT", then=1), output_field=IntegerField())),
)

And he’s right, it is very inelegant.

👤trubliphone

Leave a comment