CODEWITHSUNDEEP
pythonsqlalchemy
mishik
mishik

Reputation: 10003

Specify "base" from-table in SQLAlchemy

Suppose I have 3 tables: A, B, C.

A: id, fieldA
B: id, id_a, fieldB; id_a - foreign key for A.id
C: id, id_b, fieldC; id_b - foreign key for B.id

Now I want to run this sql query:

SELECT A.fieldA,
       B.fieldB
FROM   C
       LEFT JOIN B
            LEFT JOIN A
              ON A.id = B.id_a
         ON B.id = C.id_b
WHERE  C.fieldC = :myvalue

The SQLAlchemy query would be:

session.query(A.fieldA, B.fieldB).join(...).join(...).filter(C.fieldC == myvalue)
                                 ^------------------^ issue here

The problem is that all the joins will be applied to the table that was selected in the query first, i.e. in this case to A.

I can use some dummy hack like:

session.query(C.fieldC, A.fieldA, B.fieldB).join(B).join(A).filter(C.fieldC == myvalue)
              ^------^ I don't need this column

But, of course, this is not desirable.

Is there a way to specify base table that will have all the joins attached to?

Upvotes: 0

Views: 29

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

Given your example, you need:

  • to specify the left part of the join using select_from
  • and use an outerjoin (and not an inner join)

I don't have SQLAlchemy at hand right now, so I cannot guarantee this will work "out of the box", but something like that would be a good starting point:

session.query(A.fieldA, B.fieldB)        \
          .select_from(C)                \
          .outerjoin(B, ....)            \
          .outerjoin(A, ....)            \
          .filter(C.fieldC == myvalue)

Upvotes: 2

Related Questions