How to filter by joinloaded table in SqlAlchemy?

Question

Lets say I got 2 models, Document and Person. Document got relationship to Person via "owner" property. Now:

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

Will double join table Person. One person table will be selected, and the doubled one will be filtered which is not exactly what I want cuz this way document rows will not be filtered.

What can I do to apply filter on joinloaded table/model ?

Answer 1

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

• one is to filter the the condition: filter(Person.is_deleted != True)
• the other is to eager load the relationship: options(joinedload('owner'))

But the reason your query returns wrong results is because your filter condition is not complete. In order to make it produce the right results, you also need to JOIN the two models:

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

This will return correct rows, even though it will still have 2 references (JOINs) to Person table. The real solution to your query is that using 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)
        )