如何将SQL标量子查询转换为SQLAlchemy表达式

Question

我需要一点帮助，将我的代码用SQLAlchemy语言表达出来，就像这样：

SELECT
    s.agent_id,
    s.property_id,
    p.address_zip,
    (
        SELECT v.valuation
        FROM property_valuations v WHERE v.zip_code = p.address_zip
        ORDER BY ABS(DATEDIFF(v.as_of, s.date_sold))
        LIMIT 1
    ) AS back_valuation,
FROM sales s
JOIN properties p ON s.property_id = p.id

内部子查询旨在从表propert_valuations中获取最接近表sales的销售日期的列(zip_code INT, valuation DECIMAL, as_if DATE)的属性值。 我知道如何重写它，但完全卡在order_by表达式上 - 我无法准备子查询以稍后传递排序成员。

目前我有以下查询：

subquery = (
    session.query(PropertyValuation)
    .filter(PropertyValuation.zip_code == Property.address_zip)
    .order_by(func.abs(func.datediff(PropertyValuation.as_of, Sale.date_sold)))
    .limit(1)
)

query = session.query(Sale).join(Sale.property_)

如何将这些查询组合在一起？

- Alex G.P.

Answer 1

如何将这些查询组合在一起？

使用as_scalar()或label()。

subquery = (
    session.query(PropertyValuation.valuation)
    .filter(PropertyValuation.zip_code == Property.address_zip)
    .order_by(func.abs(func.datediff(PropertyValuation.as_of, Sale.date_sold)))
    .limit(1)
)

query = session.query(Sale.agent_id,
                      Sale.property_id,
                      Property.address_zip,
                      # `subquery.as_scalar()` or
                      subquery.label('back_valuation'))\
        .join(Property)

使用as_scalar()会将返回的列和行限制为1，因此您无法使用它获取整个模型对象（因为query(PropertyValuation)是选择PropertyValuation的所有属性），但是仅获取valuation属性是可以的。

但我在order_by表达式上完全卡住了 - 我无法准备子查询以后传递排序成员。

没有必要以后再传递。您当前声明子查询的方式是可以的，因为SQLAlchemy可以自动将FROM对象与封闭查询的对象相关联。我尝试创建了一些模型，以某种程度上代表您的模型，以下是上述查询的工作方式（为了可读性添加了换行和缩进）：

In [10]: print(query)
SELECT sale.agent_id AS sale_agent_id,
       sale.property_id AS sale_property_id,
       property.address_zip AS property_address_zip,
       (SELECT property_valuations.valuation
        FROM property_valuations 
        WHERE property_valuations.zip_code = property.address_zip
        ORDER BY abs(datediff(property_valuations.as_of, sale.date_sold))
        LIMIT ? OFFSET ?) AS back_valuation 
FROM sale
JOIN property ON property.id = sale.property_id

- Ilja Everilä

---

自SQLAlchemy 1.4版本开始，as_scalar()被替换为scalar_subquery()。 - Mikko Ohtamaa

如果能够更新答案使用scalar_subquery()，我会非常感激。 - undefined