samedi 18 juin 2016

SQLAlchemy - How to add dynamic left joins to a query?

I have six tables, modeled like the following:

+--< B >--C
| 
A
|
+--< D >--E

I would like to be able to dynamically query any of these options:

A
A, B, C
A, D, E
A, B, C, D E

For example, querying all four would look like

q = session.query(A, B, C, D, E) 
    .outerjoin(B, A.id == B.a_id).outerjoin(C, C.id == B.c_id)
    .outerjoin(D, A.id == D.a_id).outerjoin(E, E.id == D.e_id)

I can append the models to a list and dynamically use them in the select clause. However, I cannot figure out how to dynamically attach the joins. Here is what I have so far:

from sqlalchemy import outerjoin

models = [A]
joins = []

if foo:
    models.append(B)
    models.append(C)
    joins.append(outerjoin(A, B, A.id == B.a_id))
    joins.append(outerjoin(B, C, C.id == B.c_id))

if bar:
    models.append(D)
    models.append(E)
    joins.append(outerjoin(A, D, A.id == D.d_id))
    joins.append(outerjoin(D, E, E.id == D.e_id))

q = session.query(*models)
# How do I attach my joins list to this query?

I have tried the following, which did not work, and even if it did I would presume that the case when foo and bar are both False would leave an empty FROM clause.

q = q.select_from(*joins)

Of course I can get rid of the joins list and repeat the if conditions after executing q = session.query(*models), like the following, but I would rather perform the conditional logic one time.

if foo:
    q = q.outerjoin(B, A.id == B.a_id).outerjoin(C, C.id == B.c_id)

if bar:
    q = q.outerjoin(D, A.id == D.a_id).outerjoin(E, E.id == D.e_id)

Aucun commentaire:

Enregistrer un commentaire