sqlalchemy - join child table with 2 conditions

Question

How to add 2 conditions to the ON clause when you join 2 tables. I have 3 three tables in hierarchy each with the deleted flag. I have to join all these table in a single query and filter based on deleted flag also. Currently the conditions gets added to the where clause of the query, which does not filter the deleted records. It needs to be added to the ON clause. Please suggest.

My current query is as follows:

result = session.query(Host).filter(and_(Host.id.in_(ids), Host.deleted == False)).\     join(Switch).filter(Switch.deleted == False).\     join(Port).filter(Port.deleted == False).\     options(joinedload('switches')).\     options(joinedload('ports')).\     all() 

Thankyou

Answer 1

Try contains_eager instead of joinedload. What is probably happening is that you have 4 joins the two you defined with join and then then the two from the options(joinedload(...))

Modifying your code, should give this:

from sqlalchemy import and_  result = (session.query(Host).filter(and_(Host.id.in_(ids), Host.deleted == False)).     join(Switch, and_(Switch.host_id==Host.id, Switch.deleted == False)).     join(Port, and_(Port.switch_id==Switch.id, Port.deleted == False)).     options(contains_eager('switches')).     options(contains_eager('ports')).     all() )