SqlAlchemy Join Query
<p>I have tables like this:</p>

<p>Box</p>

<p>|- issues[]</p>

<p>Issue</p>

<p>|- status_id</p>

<p>|- status (related through status_id)</p>

<p>Status</p>

<p>|- id</p>

<p>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</p>

<p>db.session.query(Box).join(Issue).filter(Issue.status_id != 5).all()</p>

<p>What is wrong with the above code?</p>

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

<pre><code>db.session.query(Box).outerjoin(Box.issues).filter(or_(Issue.status_id.is_(None), Issue.status_id != 5)).options(contains_eager(Box.issues)).all()
</code></pre>

python sqlalchemy flask-sqlalchemy