CODEWITHSUNDEEP
pythonsqlalchemy
Prasadnsr
Prasadnsr

Reputation: 481

sqlalchemy - join child table with 2 conditions

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

Upvotes: 40

Views: 69978

Answers (3)

Vlad Bezden
Vlad Bezden

Reputation: 89527

The and_() conjunction is also available using the Python & operator (though note that compound expressions need to be parenthesized in order to function with Python operator precedence behavior): There are also | for or_() and ~ for not_()

So using & operator 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()
)

Upvotes: 28

Tony Gibbs
Tony Gibbs

Reputation: 2489

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

Upvotes: 66

van
van

Reputation: 76962

You can specify the ON clause explicitely in the Query.join call using onclause parameter. Then you query should look like below (not tested):

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

Upvotes: 9

Related Questions