Django filter by the number of rows matching a certain condition in a ManyToMany

Question

I need to filter for objects where the number of elements in a ManyToMany relationship matches a condition. Here's some simplified models:

Place(models.Model):
     name = models.CharField(max_length=100)
Person(models.Model):
     type = models.CharField(max_length=1)
     place = models.ManyToManyField(Place, related_name="people")

I tried to do this:

c = Count(Q(people__type='V'))
p = Places.objects.annotate(v_people=c)

But this just makes the .v_people attribute count the number of People.

Answer 1

Since django-2.0, you can use the filter=... parameter of the Count(…) function ^[Django-doc] for this:

Place.objects.annotate(
    v_people=Count('people', filter=Q(people__type='V'))
)

So this will assign to v_people the number of people with type='V' for that specific Place object.

An alternative is to .filter(..) the relation first:

Place.objects.filter(
    Q(people__type='V') | Q(people__isnull=True)
).annotate(
    v_people=Count('people')
)

Here we thus filter the relation such that we allow people that either have type='V', or with no people at all (since it is possible that the Place has no people. We then count the related model.

This generates a query like:

SELECT `place`.*, COUNT(`person_place`.`person_id`) AS `v_people`
FROM `place`
LEFT OUTER JOIN `person_place` ON `place`.`id` = `person_place`.`place_id`
LEFT OUTER JOIN `person` ON `person_place`.`person_id` = `person`.`id`
WHERE `person`.`type` = V OR `person_place`.`person_id` IS NULL