我们可以使用SQLAlchemy进行相关查询吗 [英] Can we make correlated queries with SQLAlchemy
本文介绍了我们可以使用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).
我知道如何在<$ c $中使用子查询c> 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屋!
查看全文