Outer Join with ORM mapping in SQLAlchemy

Question

I am using the ORM Mapping in SQLAlchemy 0.6.8.

I have three tables (A, B and C), with no foreign keys between them.

I am trying to join table A and B, and then left outer join that with C. I am expecting a named tuple, with fields A, B and C - with the C field sometimes set to None.)

I can do the first join easily enough by just selecting both table.

(session.query(A, B)
    .filter(A.some_field = B.some_other_field))

That gets me a NamedTuple with fields A and B.

I then add the outer join, to make it:

(session.query(A, B)
    .filter(A.some_field==B.some_other_field))
    .outerjoin((C, A.some_field==C.some_different_field))

The result still only has two tables. I can't access the other fields of C (even in the cases where they are present).

What is the correct way to do an left outer join, to get access to the fields of the right-most table??

I'd rather not fallback to the basic SQL if I could avoid it - I am trying to learn to take advantage of the ORM.

Answer 1

This should work:

(session.query(A)
    .join(B, A.some_field == B.some_other_field)
    .outerjoin(C, A.some_field == C.some_different_field)
    .add_entity(B)
    .add_entity(C))