SqlAlchemy非WHERE子句位置子查询调用.subquery()生成SQL错误

需要实现关联标量子查询，将子查询作为SELECT字段返回每个协议组对应的最近审批日期，预期SQL结构如下：

SELECT  ets.agreement_t.id  AS ets_agreement_t_id,
        -- 其他字段...
        (
            select max(created_date) 
            from ets.agreement_history_t 
            where agreement_group_id = ets.agreement_t.agreement_group_id
        ) AS "LastApprovalDate",
        -- 其他字段...
FROM ets.agreement_t
-- 其他关联、过滤逻辑...

使用SQLAlchemy实现时，最初通过.subquery()方法构造子查询对象：

subqueryLastApprovalDate = db_session.query(func.max(AgreementHistoryT.created_date).filter(
                                (AgreementHistoryT.agreement_group_id == AgreementT.agreement_group_id))
                            ).label('lastApprovalDate')).subquery()

将该对象加入主查询的SELECT字段列表后，生成的SQL不符合预期：框架将构造的标量子查询错误放到了FROM子句中，还产生了隐式笛卡尔积，错误SQL结构如下：

SELECT          ets.agreement_t.id,
                -- 其他字段...
                anon_1."lastApprovalDate" AS "anon_1_lastApprovalDate",
                -- 其他字段...
FROM            (
                       SELECT max(ets.agreement_history_t.created_date) filter (WHERE ets.agreement_history_t.agreement_group_id = ets.agreement_t.agreement_group_id
                       ) AS "lastApprovalDate"
                       FROM   ets.agreement_history_t,
                              ets.agreement_t) AS anon_1,
                -- 其他表...

无法实现逐行关联查询各协议组最大创建日期作为返回字段的需求。

• SQLAlchemy中.subquery()方法的作用是生成可用于JOIN、放在FROM子句的派生表对象，不适用于构造SELECT列表中的关联标量子查询，因此框架会自动将这类对象放到FROM段处理。
• 原写法存在语法错误：误将query对象的filter()方法写到了func.max()聚合函数的参数括号内，会生成非标准的聚合filter语法，而非子查询内的行级关联过滤逻辑。

不需要调用.subquery()方法，直接构造带外部关联条件的聚合查询，调用label()指定别名后，直接放入主查询的SELECT字段列表即可。SQLAlchemy会自动识别关联外部表的这类查询对象，将其作为关联标量子查询嵌入SELECT列表，不会放入FROM子句。
正确代码示例：

from sqlalchemy import func

# 构造关联标量子查询，不要调用.subquery()
subquery_last_approval_date = (
    db_session.query(func.max(AgreementHistoryT.created_date))
    .filter(AgreementHistoryT.agreement_group_id == AgreementT.agreement_group_id)
    .label("lastApprovalDate")
)

# 主查询
agreements = (
    db_session.query(
        AgreementT.id,
        # 其余需要查询的字段...
        subquery_last_approval_date,
        # 其余需要查询的字段...
    )
    # 后续过滤、排序、分页等逻辑
)

按上述写法生成的SQL会和预期完全一致，逐行关联计算每个协议组对应的最近审批日期。

内容的提问来源于stack exchange，提问作者gene b.