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

Question

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)

Answer 1

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.