CODEWITHSUNDEEP
pythonpython-2.7sqlalchemy
Matthew Moisen
Matthew Moisen

Reputation: 18299

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)

Upvotes: 3

Views: 4102

Answers (2)

Ojus sangoi
Ojus sangoi

Reputation: 716

The verified answer by @jsbueno gave me a hint to solve my issue, but it didn't 100% worked for me. Might be a version issue, so providing an almost similar solution that worked for me.

models = [A]
joins = []

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

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

q = session.query(*models)
for join_args in joins:
     q = q.outerjoin(*join_args)

q.all() #This shall give you the required output.

Upvotes: 2

jsbueno
jsbueno

Reputation: 110476

Each outerjoin (and other SLQALchemy query methods) modifies a query object and returns a new query - which you can further modify by calling outerjoin (or filter, etc...) methods on. So, just use a for loop to repeatedly modify your query with an extra outerjoin for each set of outerjoin parameters you specify with your conditions. The parameters themselves can be just tuples which you preppend with an * just like you do for the models

models = [A]
joins = []

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

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

q = session.query(*models)
for join_args in joins:
     q = q.outerjoin(*join_args)

# q is now ready to go with all outerjoins specified.

Upvotes: 7

Related Questions