Sqlalchemy 将 WHERE 子句复制到 FROM [英] Sqlalchemy duplicated WHERE clause to FROM
问题描述
我向 psql 写了原始查询,它工作正常,但是当我在 sqlalchemy 中写这个时,我的 WHERE 子句复制到了 FROM 子句.
I wrote raw query to psql and it's work fine but when i wrote this in sqlalchemy my WHERE clause duplicated to FROM clause.
select id from T1 where arr && array(select l.id from T1 as l where l.box && box '((0,0),(50,50))');
在此查询中,我从 T1 中获取所有 id,其中整数数组与子查询的结果相交.
In this query i fetch all id from T1 where array with ints intersects with results from subquery.
class T1():
arr = Column(ARRAY(Integer))
...
class T2():
box = Column(Box) # my geometry type
...
1 个版本:
layers_q = select([T2.id]).where(T2.box.op('&&')(box)) # try find all T2 intersects with box
chunks = select([T1.id]).where(T1.arr.overlap(layers_q)) # try find all T1.id where T1.arr overlap with result from first query
SELECT T1.id
FROM T1
WHERE T1.arr && (SELECT T2.id
FROM T2
WHERE T2.box && %(box_1)s)
我有一个关于类型转换的 PG 错误.我明白了.
This i have a PG error about type cast. I understand it.
2 版本:
layers_q = select([T2.id]).where(T2.box.op('&&')(box))
chunks = select([T1.id]).where(T1.arr.overlap(func.array(layers_q)))
我添加了 func.array() 用于强制转换为数组但结果不正确:
I added func.array() for cast to array but result is not correct:
SELECT T1.id
FROM T1, (SELECT T2.id AS id
FROM T2
WHERE T2.box && %(box_1)s)
WHERE T1.arr && array((SELECT T2.id
FROM T2
WHERE T2.box && %(box_1)s))
在那里你可以看到我在 FROM 子句中重复的内容.它是如何正确的?
There you can see what i have duplicate in FROM clause. How did it correctly?
推荐答案
我找到了解决方案!
func.array(select([T2.id]).where(T2.box.op('&&')(box)).as_scalar())
添加 as_scalar() 后一切正常,因为在我的选择中,所有 id 都需要在一个数组中.
After added as_scalar() all be good, beacause in my select all ids need have in one array.
这篇关于Sqlalchemy 将 WHERE 子句复制到 FROM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!