title: Convert SQL query with JOIN ON to SQLAlchemy
body: <p>My query looks like so (the '3' and '4' of course will be different in real usage):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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 <a href="https://stackoverflow.com/questions/5913942/return-rows-even-if-record-does-not-exist-left-outer-join">this in plain SQL here already</a>. Now in the ORM, that's a different story, I can't even figure out how to make JOIN ON conditions with the documentation!</p>
  
  <p>Edit (new users are not allowed to answer their own question):</p>
  
  <p>Believe I solved it, I guess writing it down as a question helped! Maybe this will help some other newbie out there.</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>I'm keeping this open in case someone got a better solution or any insights.</p>