CODEWITHSUNDEEP
djangodjango-aggregation
trubliphone
trubliphone

Reputation: 4504

How to filter Django annotations on reverse foreign key fields

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?

Upvotes: 3

Views: 4129

Answers (2)

trubliphone
trubliphone

Reputation: 4504

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

Upvotes: 1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476574

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.

Upvotes: 8

Related Questions