sqlalchemy FULL OUTER JOIN

python, sqlalchemy

Solution

Since 1.1. sqlalchemy now fully supports FULL OUTER JOINS. See here: https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.join.params.full

So for your code you would want to do:

q1 = (db.session.query(
        tb1.user_id.label('u_id'),
        func.count(tb1.id).label('tb1_c')
    )
    .group_by(tb1.user_id)
).cte('q1')

q2 = (db.session.query(
        tb2.user_id.label('u_id'),
        func.count(tb2.id).label('tb2_c')
    )
    .group_by(tb2.user_id)
).cte('q2')

result = db.session.query(
    func.coalesce(q1.u_id, q2.u_id).label('u_id'),
    q1.tb1_c,
    q2.tb2_c
).join(
    q2,
    q1.u_id == q2.u_id,
    full=True
)

Note that as with any FULL OUTER JOIN, `tb1_c` and `tb2_c` may be null so you might want to apply a coalesce on them.

Problem

Original source