CODEWITHSUNDEEP
djangopostgresqldjango-models
superdee
superdee

Reputation: 697

How do I filter by occurrences count in another table in Django?

Here are my models:

class Zoo(TimeStampedModel):
    id = models.AutoField(primary_key=True)

class Animal(models.Model):
    id = models.AutoField(primary_key=True)
    zoo = models.ForeignKey(Zoo, on_delete=models.PROTECT, related_name='diffbot_results')

I would like to run a query like this:

Zoo.objects.filter("WHERE zoo.id IN (select zoo_id from animal_table having count(*) > 10 group by zoo_id)")

Upvotes: 1

Views: 329

Answers (1)

AdamKG
AdamKG

Reputation: 14056

One way is to use a raw queryset:

>>> from testapp.models import Zoo, Animal
>>> z1, z2 = Zoo(), Zoo()
>>> z1.save(), z2.save()
(None, None)
>>> z1_animals = [Animal(zoo=z1) for ii in range(5)]
>>> z2_animals = [Animal(zoo=z2) for ii in range(15)]
>>> x = [a.save() for a in z1_animals+z2_animals]
>>> qs = Zoo.objects.raw("select * from testapp_zoo zoo WHERE zoo.id IN (select zoo_id from testapp_animal group by zoo_id having count(1) > 10)")
>>> list(qs)
[<Zoo: Zoo object (2)>]

In theory, per these docs, it should be possible to pass in a queryset to a regular .filter(id__in=<queryset>), but the queryset must only select one column, and I can't find a way of adding the HAVING clause without also causing the queryset to select a num_animals column, preventing it from being used with an __in filter expression.

Upvotes: 1

Related Questions