CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
user3763741
user3763741

Reputation: 95

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?

Upvotes: 1

Views: 219

Answers (1)

mickzer
mickzer

Reputation: 6338

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

Upvotes: 1

Related Questions