CODEWITHSUNDEEP
pythondjangodjango-models
Kevin Bedell
Kevin Bedell

Reputation: 13404

Find Django model records that have more than one 'child' objects?

If I have two models in Django, Parent and Child, where Child has a foreign key relationship to Parent like this:

class Parent(models.Model):
    parent_name = models.CharField(max_length=128, blank=False, default='no name')

class Child(models.Model):
    child_name = models.CharField(max_length=128, blank=False, default='no name')
    parent = models.ForeignKey('app.Parent', on_delete=models.CASCADE, null=False)

How can I find all Parent records that have at least two Child records? Ideally, the solution would use a single .filter() statement on Parent.

Upvotes: 3

Views: 1161

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476594

You can annotate on the number of Childs and then filter on that number, like:

from django.db.models import Count

Parent.objects.annotate(
    nchild=Count('child')
).filter(nchild__gt=1)

This will generate a query like:

SELECT parent.*, COUNT(child.id) AS nchild
FROM parent
LEFT OUTER JOIN child ON parent.id = child.parent_id
GROUP BY parent.id
HAVING COUNT(child.id) > 1

One can change the .filter(..) condition to all sorts of conditions on the number of childs nchilds, for example nchild=4 filters on Parents with exactly four children, whereas ~Q(nchild=7) will exclude all Parents with exactly seven children. We can thus make more complicated filters.

Upvotes: 6

Related Questions