CODEWITHSUNDEEP
djangopostgresqldjango-orm
Scott Stafford
Scott Stafford

Reputation: 44798

Using Django ORM, how do I filter against two fields of the same record in a Many relationship?

I have a DB schema like so:

class Family(models.Model):
    pass

class Child(models.Model):
    family = models.ForeignKey(Family)

    name   = models.TextField()
    age    = models.IntegerField()
    gender = models.CharField(max_length=1)

I want a query that returns all children in families that have boys under 5.

How do I express this? Closest I got is:

# WRONG: this is no good, it will only return boys under 5, but I want all
# children in families with boys under 5.
Child.objects.filter(gender='M', age__lt=5)

# WRONG: this is no good, it is closer but will also return children in 
# families with a 6yo boy and a 3yo girl.
Child.objects.filter(family__child__gender='M', family__child__age__lt=5)

Upvotes: 0

Views: 93

Answers (4)

Scott Stafford
Scott Stafford

Reputation: 44798

As @knbk pointed out in a comment, I was actually wrong when I wrote the question. I assumed a default behavior that wasn't there. The best answer, then, is the one I gave:

Child.objects.filter(family__child__gender='M', family__child__age__lt=5)

If you wanted to retrieve all children in families with a male child and one (possibly a different one) who is under 5, you'd have to do this:

Child.objects.filter(family__child__gender='M').filter(family__child__age__lt=5)

And here's the documentation link @knbk provided which details the feature: https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships

If he answers, I'll move the points to him.

Upvotes: 0

sk1p
sk1p

Reputation: 6735

You can try an __in-query like this: 

boys_under_5 = Child.objects.filter(gender='M', age__lg=5)
Child.objects.filter(family__child__in=boys_unter_5)

As to why your original query doesn't work, I suggest reading the generared SQL:

print Child.objects.filter(family__child__gender='M', family__child__age__lt=5).query

Upvotes: 1

pcoronel
pcoronel

Reputation: 3981

Get the ids of the Familys that have boys under 5, then filter Child on those ids. The following query only hits the database once: 

Child.objects.filter(
    family__in=Family.objects.filter(child__gender='M', child_age__lt=5).values('id')
)

Upvotes: 1

Regneel
Regneel

Reputation: 114

You stated that you wanted all boys in families with boys under 5 It's a big ambiguous but I'm going to assume that you want families that contain at least one boy that is under 5

This would work:

families = Family.objects.filter(child__gender='M', child__age__lt=5).prefetch_related('child')

Then you could iterate over the family objects to retrieve their respective children

Upvotes: 1

Related Questions