按SqlAlchemy SQLite中的联合查询中的列排序

As explained in this question, you can use string literals to do order by in unions.

如本问题中所述,您可以使用字符串文字在联合中进行排序。

For example, this works with Oracle:

例如,这适用于Oracle:

querypart1 = select([t1.c.col1.label("a")]).order_by(t1.c.col1).limit(limit)
querypart2 = select([t2.c.col2.label("a")]).order_by(t2.c.col2).limit(limit)
query = querypart1.union_all(querypart2).order_by("a").limit(limit)

The order-by can take a string literal, which is the name of the column in the union result.

order-by可以采用字符串文字,这是联合结果中列的名称。

(There are gazillions of rows in partitioned tables and I'm trying to paginate the damn things)

(分区表中有很多行,而我正在尝试对这些该死的东西进行分页)

When running against SQLite3, however, this generates an exception:

但是,在针对SQLite3运行时,会生成异常:

sqlalchemy.exc.OperationalError: (OperationalError) near "ORDER": syntax error

How can you order by the results of a union?

你如何通过工会的结果订购?

1 个解决方案

#1

The queries that are part of a union query must not be sorted.

必须不对作为联合查询一部分的查询进行排序。

To be able to use limits inside a compound query, you must wrap the individual queries inside a separate subquery:

为了能够在复合查询中使用限制,必须将单个查询包装在单独的子查询中:

SELECT * FROM (SELECT ... LIMIT ...)
UNION ALL
SELECT * FROM (SELECT ... LIMIT ...)

q1 = select(...).limit(...).subquery()
q2 = select(...).limit(...).subquery()
query = q1.union_all(q2)...

#1