How to filter by joined table in SqlAlchemy?

Question

Let's say I have 2 models, Document and Person . Document got its relation to Person through the owner property. Now:

 session.query(Document)\ .options(joinedload('owner'))\ .filter(Person.is_deleted!=True) 

There will be a double Person join table. A one-person table will be selected and the doubled part will be filtered out, which is not exactly what I want, because in this way the lines of the document will not be filtered out.

What can I do to apply a filter to a table / model with a connection?

Answer 1

You are right, the Person table will be used twice in the resulting SQL , but each of them has a different purpose:

• need to filter the condition: filter(Person.is_deleted != True)
• the other should look forward to loading the relationship: options(joinedload('owner'))

But the reason your query returns incorrect results is because your filter condition is not completed. To make it a result, you must also JOIN two models:

 qry = (session.query(Document). join(Document.owner). # THIS IS IMPORTANT options(joinedload(Document.owner)). filter(Person.is_deleted != True) ) 

This will return the correct rows, although it will still have 2 references (JOINs) in the Person table. The real solution to your request is to use contains_eager instead of joinedload :

 qry = (session.query(Document). join(Document.owner). # THIS IS STILL IMPORTANT options(contains_eager(Document.owner)). filter(Person.is_deleted != True) )