Can We Make Correlated Queries With Sqlalchemy

I'm trying to translate this SQL query into a Flask-SQLAlchemy call: SELECT * FROM 'ENVOI' WHERE 'ID_ENVOI' IN (SELECT d.'ID_ENVOI' FROM 'DECLANCHEMENT' d

Solution 1:

Yes, we can.

Have a look at the following example (especially the correlate method call):

from sqlalchemy import select, func, table, Column, Integer

table1 =table('table1', Column('col', Integer))
table2 =table('table2', Column('col', Integer))


subquery =select(
    [func.if_(table1.c.col ==1, table2.c.col, None)]
).correlate(table1)

query = (
    select([table1.c.col,
            subquery.label('subquery')])
    .select_from(table1)
)

if __name__ =='__main__':
    print(query)

will result in the following query

SELECT table1.col, (SELECTif(table1.col = :col_1, table2.col, NULL) AS if_1 
FROM table2) AS subquery 
FROM table1

As you can see, if you call correlate on a select, the given Table will not be added to it's FROM-clause. You have to do this even when you specify select_from directly, as SQLAlchemy will happily add any table it finds in the columns.

Solution 2:

Based on the link from univerio's comment, I've done this code for my request:

Declch = db.aliased(Declanchement)

maxdate_sub = db.select([db.func.max(Declanchement.date)])\
                .where(Declanchement.id_envoi == Declch.id_envoi)

decs_sub = db.session.query(Declch.id_envoi)\
            .filter(Declch.status == SMS_EN_ATTENTE)\
            .filter(Declch.date < since)\
            .filter(Declch.date == maxdate_sub).subquery()

envs = Envoi.query.filter(Envoi.id_envoi.in_(decs_sub)).all()