Convert SQL query with JOIN ON to SQLAlchemy

Question

My query looks like so (the '3' and '4' of course will be different in real usage):

SELECT op_entries.*, op_entries_status.*
FROM op_entries
LEFT OUTER JOIN op_entries_status ON op_entries.id = op_entries_status.op_id AND op_entries_status.order_id = 3
WHERE op_entries.op_artikel_id = 4 AND op_entries.active
ORDER BY op_entries.id

This is to get all stages (operations) in the production of an article/order-combination as well as the current status (progress) for each stage, if a status entry exists. If not the stage must still be returned, but the status rows be null.

I'm having immerse problems getting this to play in SQLAlchemy. This would have been a 2 part question, but I found the way to do this in plain SQL here already. Now in the ORM, that's a different story, I can't even figure out how to make JOIN ON conditions with the documentation!

Edit (new users are not allowed to answer their own question):

Believe I solved it, I guess writing it down as a question helped! Maybe this will help some other newbie out there.

query = db.session.query(OpEntries, OpEntriesStatus).\
    outerjoin(OpEntriesStatus, db.and_(
        OpEntries.id == OpEntriesStatus.op_id,
        OpEntriesStatus.order_id == arg_order_id)).\
    filter(db.and_(
        OpEntries.op_artikel_id == artidQuery,
        OpEntries.active)).\
    order_by(OpEntries.id).\
    all()

I'm keeping this open in case someone got a better solution or any insights.

Answer 1

Assuming some naming convention, the below should do it:

qry = (session.query(OpEntry, OpEntryStatus)
        .join(OpEntryStatus, and_(OpEntry.id == OpEntryStatus.op_id, OpEntryStatus.order_id == 3))
        .filter(OpEntry.op_artikel_id == 4)
        .filter(OpEntry.active == 1)
        .order_by(OpEntry.id)
        )

Read join, outerjoin for more information on joins, where second parameter is an onclause. If you need more than 1, just use and_ or or_ to create any expression you need.