CODEWITHSUNDEEP
ormjoinsqlalchemyouter-join
Oddthinking
Oddthinking

Reputation: 25342

Outer Join with ORM mapping in SQLAlchemy

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.

Upvotes: 14

Views: 11310

Answers (2)

sayap
sayap

Reputation: 6275

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))

Upvotes: 18

Oddthinking
Oddthinking

Reputation: 25342

Tentative answer to my own question:

This code appears to be generating the SQL I want: 

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

Note the addition of the C parameter to the original session.query call.

This does not appear to do what I thought it would do: A joined with B joined with C left joined with C again.

I am still testing.

Upvotes: 1

Related Questions