Filter for elements using exists through a reverse foreign key relationship

Question

A relevant image of my model is here: https://i.sstatic.net/xzsVU.png

I need to make a queryset that contains all cats who have an associated person with a role of "owner" and a name of "bob".

The sql for this would be shown below.

select * from cat where exists 
(select 1 from person inner join role where 
person.name="bob" and role.name="owner");

This problem can be solved in two sql queries with the following django filters.

people = Person.objects.filter(name="bob", role__name="owner")
ids = [p.id for p in people]
cats = Cat.objects.filter(id__in=ids)

My actual setup is more complex than this and is dealing with a large dataset. Is there a way to do this with one query? If it is impossible, what is the efficient alternative?

Answer 1

I'm pretty sure this is your query:

cats = Cat.objects.filter(person__name='bob', person__role__name='owner')

read here about look ups spanning relationships