我们可以使用 SQLAlchemy 进行相关查询吗 [英] Can we make correlated queries with SQLAlchemy

问题描述

我正在尝试将此 SQL 查询转换为 Flask-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
                     WHERE d."STATUS" = 0
                                    AND d."DATE" = (SELECT max("DECLANCHEMENT"."DATE")
                                    FROM "DECLANCHEMENT"
                                    WHERE "DECLANCHEMENT"."ID_ENVOI" = d."ID_ENVOI"))

如您所见，它使用子查询，最重要的是，其中一个子查询是相关查询(它使用在外部查询中定义的 d 表).

As you can see, it uses subqueries and, most important part, one of the subqueries is a correlated query (it use d table defined in an outer query).

我知道如何使用带有 subquery() 函数的子查询，但是我找不到有关使用 SQLAlchemy 进行相关查询的文档.你知道怎么做吗?

I know how to use subqueries with subquery() function, but I can't find documentation about correlated queries with SQLAlchemy. Do you know a way to do it ?

推荐答案

基于 univerio 评论中的链接，我已经为我的请求完成了这段代码:

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()

这篇关于我们可以使用 SQLAlchemy 进行相关查询吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！