How to filter Django annotations on reverse foreign key fields

Question

I am trying to get a count of all related models with a particular field value.

Here is some code...

models.py:

class Author(models.Model):
  name = models.CharField(max_length=100)

class Book(models.Model):
  BAD = "BAD"
  MEH = "MEH"
  GOOD = "GOOD"
  GREAT = "GREAT"
  REVIEW_CHOICES = (
    (BAD, BAD.title()),
    (MEH, MEH.title()),
    (GOOD, GOOD.title()),
    (GREAT, GREAT.title()),
  )
  title = models.CharField(max_length=100)
  review = models.CharField(max_length=100, choices=REVIEW_CHOICES)
  author = models.ForeignKey(Author, related_name="books")

Suppose I want to list the number of each type of reviews for each author.

I have tried:

Authors.object.annotate(n_good_books=Count("books")).filter(books__review="GOOD").values("name", "n_good_books")

I have also tried:

Authors.object.annotate(n_good_books=Count("books", filter=Q(books_review="GOOD"))).values("name", "n_good_books")

But neither of these works.

Any suggestions?

What Django version do you use? – Willem Van Onsem Sep 24 '18 at 16:39 — Willem Van Onsem, Sep 24 '18 at 16:39

Willem Van Onsem · Accepted Answer · 2018-09-24T16:47:19.343

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 django-2.0, so in django-1.11 this will not work.

Is there a way to do add annotations for _each_ book review type all at once, so I would wind up w/ a count for "n_good_books", "n_bad_books", "n_great_books", etc.? — trubliphone, Sep 24 '18 at 16:50
@trubliphone: yes but it is very inelegant. I would advice to upgrade to Django-2.0, such that you can make use of the `Count(..,filter=...)` approach. — Willem Van Onsem, Sep 24 '18 at 16:55

score 1 · Answer 2 · answered Sep 25 '18 at 07:13

@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.

How to filter Django annotations on reverse foreign key fields

2 Answers2