Sqlalchemy - join the children's table with 2 conditions

Question

How to add 2 conditions to the ON clause when you join 2 tables. I have three tables in the hierarchy, each of which has a remote flag. I have to combine all these tables into one query and a filter based on the remote flag. Currently, conditions are added to the where clause of the request, which does not filter deleted records. It should be added to the ON clause. Please suggest.

My current request 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 joinload. Most likely, what happens is that you have 4 connections with two that you defined using the join, and then two of the parameters (joinload (...))

By changing your code, you should give the following:

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

Answer 2

You can explicitly specify the ON clause in a Query.join call using the onclause parameter. Then the request should look like this (not verified):

 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(joinedload('switches')). options(joinedload('ports')). all() ) 

Answer 3

The and_ () console is also available using Python and the operator (although note that compound expressions must be enclosed in brackets in order to function with the Python operator's priority behavior): There is also | for or_ () and ~ for not_ ()

Thus, the use and operator of your code will look like this:

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