In Django is there a way to aggregate over relationships with a condition on the related object

Question

The Django aggregation documentation give this example that counts the number of Books related to each Publisher and returns a query set with the top 5 publishers annotated with thier book count (like appending a new field with this count):

>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323

But I need to count only a specific type of book. Something like.

>>> pubs = Publisher.objects.annotate(num_books=Count('book__type="nonfiction"')).order_by('-num_books')[:5]

Is there a filter I can use to accomplish this or do I need to resort to raw SQL?

The above is the docs example analogous to my real problem which is to identify and quantify the biggest medical groups by the number of hospitals in their system, when systems and hospitals are both modeled as healthcare Entitys:

>>> biggest_systems = Entity.objects.filter(relationships__type_id=NAME_TO_TYPE_ID.get('hospital')).annotate(num_hospitals=Count('relationships')).order_by('-num_hospitals')[:5]
>>> biggest_systems[0].num_hospitals
25

relationships is an Entity M2M field with a through table, type_id is a field within Entity as well:

class Entity(models.Model):
    id = models.AutoField(verbose_name='ID',primary_key=True, db_column='ID') 
    type_id = models.IntegerField(verbose_name='detailed type',db_column='TypeID', blank=True, editable=True, choices=ENTITYTYPE_CHOICES, help_text="A category for this healthcare entity.")
    relationships = models.ManyToManyField('self', verbose_name='affiliated with', through='EntityRelationship', symmetrical=False, related_name='related_to+')

Answer 1

See Order of annotate() and filter() clauses:

Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))

Or in your case:

Publisher.objects.filter(book__type='nonfiction').annotate(num_books=Count('book'))