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.

olieidel · Accepted Answer · 2017-09-20 11:51:33Z

9

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.

edited Sep 20, 2017 at 11:51

answered Sep 20, 2017 at 10:42

olieidel

1,57510 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wessi Over a year ago

Actually it does seem to work. num_houses is consistently 1 and thus doesn't give the actual count of houses in that area.

Wessi Over a year ago

that was a typo. It doesn't work. num_houses is equal to 1 and doesn't give the actual count of houses in that area

olieidel Over a year ago

You may want to check your data. The query looks correct.

Wessi Over a year ago

I made an edit to your answer. It's working for me with that addition. It believes this might only be needed for MySQL as the database backend. I've specified in my question that I'm using MySQL. I'll accept your answer once my edit has been accepted. Thanks for the help.

olieidel Over a year ago

I can't accept those edits. They are not django best practices and will confuse other people who find this question. I suggest you post your fix in the comments - other people should rather implement a many-to-many relation following django best practices and using the appropriate ORM Field.

Collectives™ on Stack Overflow

Django filtering based on count of related model

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related