Django filtering based on count of related model

Question

I have the following working code:

houses_of_agency = House.objects.filter(agency_id=90)
area_list = AreaHouse.objects.filter(house__in=houses_of_agency).values('area')
area_ids = Area.objects.filter(area_id__in=area_list).values_list('area_id', flat=True)

That returns a queryset with a list of area_ids. I want to filter further so that I only get area_ids where there are more than 100 houses belonging to the agency.

I tried the following adjustment:

houses_of_agency = House.objects.filter(agency_id=90)
area_list = AreaHouse.objects.filter(house__in=houses_of_agency).annotate(num_houses=Count('house_id')).filter(num_houses__gte=100).values('area')
area_ids = Area.objects.filter(area_id__in=area_list).values_list('area_id', flat=True)

But it returns an empty queryset.

My models (simplified) look like this:

class House(TimeStampedModel):
    house_pk = models.IntegerField()
    agency = models.ForeignKey(Agency, on_delete=models.CASCADE)


class AreaHouse(TimeStampedModel):
    area = models.ForeignKey(Area, on_delete=models.CASCADE)
    house = models.ForeignKey(House, on_delete=models.CASCADE)


class Area(TimeStampedModel):
    area_id = models.IntegerField(primary_key=True)
    parent = models.ForeignKey('self', null=True)
    name = models.CharField(null=True, max_length=30)

Edit: I'm using MySQL for the database backend.

Answer 1

You are querying for agency_id with just one underscore. I corrected your queries below. Also, in django it's more common to use pk instead of id however the behaviour is the same. Further, there's no need for three separate queries as you can combine everything into one.

Also note that your fields area_id and house_pk are unnecessary, django automatically creates primary key fields which area accessible via modelname__pk.

# note how i inlined your first query in the .filter() call
area_list = AreaHouse.objects \
            .filter(house__agency__pk=90) \
            .annotate(num_houses=Count('house')) \  # <- 'house'
            .filter(num_houses__gte=100) \
            .values('area')

# note the double underscore
area_ids = Area.objects.filter(area__in=area_list)\
                       .values_list('area__pk', flat=True)

you could simplify this even further if you don't need the intermediate results. here are both queries combined:

area_ids = AreaHouse.objects \
            .filter(house__agency__pk=90) \
            .annotate(num_houses=Count('house')) \
            .filter(num_houses__gte=100) \
            .values_list('area__pk', flat=True)

Finally, you seem to be manually defining a many-to-many relation in your model (through AreaHouse). There are better ways of doing this, please read the django docs.