CODEWITHSUNDEEP
pythondjangodjango-queryset
Prithvi Raj
Prithvi Raj

Reputation: 1981

Filter queryset using child object fields

I have two models Parent, Child

class Parent(models.Model):
    id = models.IntegerField(...)

class Child(models.Model)
    id = models.IntegerField(...)
    parent = models.ForeignKey(Parent, ...)
    wanted = models.CharField(default="yes")

I want to filter all Parent objects where all the children present with that parent will have 'wanted' as 'yes'

My code:

    def containsYes(self):
        yes_ids = []
        qs = self.get_queryset()
        for q in qs:
            children = Child.objects.filter(parent_id = q.id)
            count = children .count()
            if children.filter(wanted = 'yes').count() == count
            yes_ids.append(q.id)
        return qs.filter(id__contains = yes_ids)

I know that this code is dead inefficient, and want a better solution using only querys

PS: Im new to django

Upvotes: 0

Views: 1646

Answers (2)

Yousef Alm
Yousef Alm

Reputation: 238

  1. Give a parent related name in child model:

parent = models.ForeignKey(Parent, ..., related_name=“children”)

  1. Change Wanted field to boolean and default it to True, than you can do the following:

Parent.objects.filter(children__wanted=True).exclude(children__wanted =False)

Or you can call Parent.objects.exclude(children__wanted=False) case this boolean is not nullable field.

Upvotes: 0

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477608

We can exclude Parents where there exists a child that is not wanted, so we can work with:

from django.db.models import F, Count, Q

Parent.objects.annotate(
    nchild=Count('child')
    nchild_wanted=Count('child', filter=Q(child__wanted=True))
).filter(
    nchild=F('nchild_wanted')
)

We thus first count the number of related Childs, and also the number of related Childs with wanted is set to True. Then we filter and only retain Parent objects where these two annotations are the same.

Since , one can make use of .alias(…) [Django-doc] to prevent counting both in the SELECT clause and the HAVING clause:

from django.db.models import F, Count, Q

Parent.objects.alias(
    nchild=Count('child')
    nchild_wanted=Count('child', filter=Q(child__wanted=True))
).filter(
    nchild=F('nchild_wanted')
)

Upvotes: 1

Related Questions