CODEWITHSUNDEEP
pythondjangopython-3.x
user972014
user972014

Reputation: 3856

Django query OneToMany by multiple fields

I have schema similar to this one (obviously this one is simplified)

class Pet(models.Model):
    name = TextField()
    date_vaccinated = DateTimeField(null=True)  # null indicates no vaccination
    owner = ForeignKey(Person, related_key="pet")

class Person(models.Model):
    name = TextField()


people_with_a_vaccinated_pet_named_rex = 
Person.objects.filter(pet__date_vaccinated__isnull=False, pet__name="Rex")

As indicated in the last line I'm trying to find all people who have a pet called Rex that is also vaccinated. The query I wrote will find all people with a pet named rex, and a vaccinated pet (not necessarily the same pet..)

Is there a way to query with multiple conditions on the same OneToMany relation?

P.S the real query I'm trying to write is more similar to the following:

Person.objecs.filter(pet__class1__date__isnull=False, pet__class1__class2__class3__name="blabla")

where I want to reach class 3 only through class1 instances that their date is not null

Upvotes: 1

Views: 764

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476594

This is already the case. In this filter, you make queries on the same one-to-many relation.

Indeed, your query:

Person.objects.filter(
    pet__date_vaccinated__isnull=False,
    pet__name='Rex'
)

Is translated to a database query that looks like:

SELECT person.*
FROM person
INNER JOIN pet ON pet.owner = person.id
WHERE pet.date_vaccinated IS NOT NULL AND pet.name = 'Rex'

We thus make one JOIN on the Pet model, and we thus filter on the same Pet.

This is the same for your second case, again it will join once on the pet, class1, class2, and class3, and thus the filter conditions are performed on the same related object.

If you want to query on (possibly) different object, you can filter with:

# get persons that have a Pet that has a name 'Rex',
# and a (possibly) different Pet that is vaccinated

Person.objects.filter(
    pet__date_vaccinated__isnull=False
).filter(
    pet__name='Rex'
)

Upvotes: 2

Related Questions