CODEWITHSUNDEEP
pythonsqlalchemy
koszikot
koszikot

Reputation: 2323

How to filter by joinloaded table in SqlAlchemy?

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 ?

Upvotes: 16

Views: 11717

Answers (1)

van
van

Reputation: 76992

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)
        )

Upvotes: 24

Related Questions