SQLAlchemy 联合括号问题 [英] SQLAlchemy Union Parenthesis Issue

问题描述

我需要生成类似于以下内容的查询:

I need to generate a query similar to the following:

(select * from ... where .. and .. order by .. limit ..)
union all
(select * from ... where .. and .. order by .. limit ..)
order by ..

使用 SQLAlchemy，我创建了两个查询对象，如下所示:

Using SQLAlchemy, I create two query objects as in:

q1 = Session.query(..).filter(..).filter(..).order_by(..).limit(..)
q2 = Session.query(..).filter(..).filter(..).order_by(..).limit(..)
q = q1.union_all(q2).order_by(..).all()

但是它不起作用，因为 SQLAlchemy 生成查询:q1 和 q2 不在括号内，并且会产生错误.

However it won't work because SQLAlchemy generates queries: q1 and q2 are not within parenthesis and it creates an error.

如何在 q1 q2 union 的括号内获取这些语句以生成上述查询?

How can I get these statements inside parenthesis for q1 q2 union to result in above expressed query?

推荐答案

您需要创建子查询，然后从这些子查询中进行选择:

You need to create subqueries, then select from those subqueries:

from sqlalchemy import union_all

q1 = Session.query(..).filter(..).filter(..).order_by(..).limit(..).subquery()
q2 = Session.query(..).filter(..).filter(..).order_by(..).limit(..).subquery()
q = Session.query(..).select_entity_from(union_all(q1.select(), q2.select()).order_by(..).all()

.subquery() 方法 返回一个别名对象，其中不直接支持 union_all 查询.因此，我们需要构建一个 select_entity_from() 构造，传入sqlalchemy.sql.expression.union_all() function 结果，因此您仍然可以将结果映射到正确的对象.

The .subquery() method returns an Alias object, which does not support union_all queries directly. So instead, we need to build a select_entity_from() construct, passing in the sqlalchemy.sql.expression.union_all() function result instead, so you still get the results mapped to the correct objects.

这篇关于SQLAlchemy 联合括号问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！