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

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

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.

Solution

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.