sqlalchemy – join child table with 2 conditions

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