user3763741 user3763741 - 3 months ago 23
Python Question

SqlAlchemy Join Query

I have tables like this:

Box

|- issues[]

Issue

|- status_id

|- status (related through status_id)

Status

|- id

I want to get all the boxes where the “issues” field for each box will only contain issues that don’t have a status_id = 5. The following isn’t working

db.session.query(Box).join(Issue).filter(Issue.status_id != 5).all()

What is wrong with the above code?

Answer

If I've understood your situation correctly, I think the following is what you're looking for:

db.session.query(Box).outerjoin(Box.issues).filter(or_(Issue.status_id.is_(None), Issue.status_id != 5)).options(contains_eager(Box.issues)).all()
Comments