How can I reverse the join order to get a right join with sqlalchemy using a subquery?

Question

I'm trying to create the following (very much simplified) SQL statement with Flask-SQLAlchemy ORM

SELECT TableA.attr
FROM (SELECT DISTINCT TableB.a_id FROM TableB) AS TableB
LEFT JOIN TableA
ON TableA.id = TableB.a_id

To achieve that I used the following SQLAlchemy statements

sq = db.session.query(distinct(TableB.a_id).label('a_id')).subquery()
results = db.session.query(TableA.attr).join(sq, sq.c.a_id==TableA.id, isouter=True).all()

This works however rather than joining my subquery TableB (left) with TableA (right) it does the reverse and joins TableA with TableB.

SELECT TableA.attr
FROM TableA
LEFT JOIN (SELECT DISTINCT TableB.a_id FROM TableB) AS TableB
ON TableB.a_id = TableA.id

Since I understand that SQLAlchemy doesn't have a right join, I'll have to somehow reverse the order while still getting TableA.attr as the result and I can't figure out how to do that with a subquery.

x right join y on c = y left join x on c (ignoring column order) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jul 02 '20 at 17:15
https://stackoverflow.com/questions/11400307/right-outer-join-in-sqlalchemy — Ilja Everilä, Jul 02 '20 at 18:28

score 3 · Accepted Answer · answered Jul 03 '20 at 09:19

The answer to the question was indeed the select_from() method. I.e. the actual solution to my problem were the following statements:

sq = db.session.query(distinct(TableB.a_id).label('a_id')).subquery()
results = db.session.query(TableA.attr) \
                    .select_from(sq) \
                    .join(TableA, TableA.id==sq.c.a_id, isouter=True).all()

In general terms: The select_from() method gets the left part of the join. The join() gets the right part as its first argument.

How can I reverse the join order to get a right join with sqlalchemy using a subquery?

1 Answers1