如何将id = ANY(ARRAY(select ...))转换为SQLAlchemy语法？

问题描述 #

我有如下简化后的SQL查询：

WITH users as (
   SELECT *
   FROM users
   WHERE created_at >= '2024-01-01'
)
SELECT *
FROM emails
WHERE user_id = ANY(ARRAY(SELECT id FROM users))

由于ANY(ARRAY())能迫使优化器选择更优执行计划，带来显著性能提升，因此我需要使用它而非简单的IN语句。

我尝试了以下SQLAlchemy代码但始终报错：

users_cte = select(User.id, User.name).filter(User.created_at >= date(2024, 01, 01)).cte("user_cte")

select(
   emails.address
)
.filter(Email.user_id.any_(func.array(select(users.cte.id))))

错误信息：

TypeError: ColumnOperators.any_() takes 1 positional argument but 2 were given

请问这种转换在SQLAlchemy中是否可行？

解决方案 #

完全可行，你的错误源于any_()的使用方式不正确。以下是两种正确的实现方式：

方法一：显式构造ANY(ARRAY(...)) #

直接用func.any()包裹数组子查询，通过相等判断关联列：

from sqlalchemy import select, func, date
# 假设你的模型类是User和Email
users_cte = select(User.id).filter(User.created_at >= date(2024, 1, 1)).cte("users")

# 生成数组形式的子查询
array_subquery = func.array(select(users_cte.c.id))

# 构造最终查询
query = select(Email.address).filter(Email.user_id == func.any(array_subquery))

方法二：利用SQLAlchemy的any()自动转换 #

SQLAlchemy的any()方法支持直接传入子查询，底层会根据数据库方言自动生成ANY(ARRAY(...))格式的SQL（以PostgreSQL为例）：

from sqlalchemy import select, date
users_cte = select(User.id).filter(User.created_at >= date(2024, 1, 1)).cte("users")

query = select(Email.address).filter(Email.user_id.any(select(users_cte.c.id)))

生成的SQL验证 #

上述代码执行后，会生成与你需求一致的SQL：

WITH users AS (
    SELECT user.id 
    FROM user 
    WHERE user.created_at >= %(created_at_1)s
)
SELECT email.address 
FROM email 
WHERE email.user_id = ANY(ARRAY(SELECT users.id FROM users))

错误原因解析 #

你之前的写法Email.user_id.any_(func.array(...))错误，因为any_()是无参数方法，用于标记列需匹配数组元素，正确逻辑是将数组表达式放在==右侧，并用func.any()包裹，或直接给any()传入子查询。

内容的提问来源于stack exchange，提问作者TreeWater