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.

van · Accepted Answer · 2016-05-19 06:40:40Z

15

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.

edited May 19, 2016 at 6:40

answered Mar 6, 2013 at 12:33

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

not amused Over a year ago

Thanks! In this case outerjoin was necessary but otherwise it looks good to me. I didn't test it though. Sorry if I stole the thunder.

Jesse Aldridge Over a year ago

Link is dead. Maybe it should be changed to docs.sqlalchemy.org/en/latest/orm/join_conditions.html ?

Collectives™ on Stack Overflow

Convert SQL query with JOIN ON to SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related