如何将 SQL 标量子查询转换为 SQLAlchemy 表达式 [英] How to convert SQL scalar subquery to SQLAlchemy expression
问题描述
我需要一点帮助来用 SQLAlchemy 语言表达我的代码,如下所示:
I need a litle help with expressing in SQLAlchemy language my code like this:
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
中获取属性值,其中列(zip_code INT、估价 DECIMAL、as_if DATE
)最接近表 的销售日期销售
.我知道如何重写它,但我完全坚持 order_by
表达式 - 我无法准备子查询以稍后传递排序成员.
Inner subquery aimed to get property value from table propert_valuations
with columns (zip_code INT, valuation DECIMAL, as_if DATE
) closest to the date of sale from table sales
. I know how to rewrite it but I completely stuck on order_by
expression - I cannot prepare subquery to pass ordering member later.
目前我有以下疑问:
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_)
如何将这些查询组合在一起?
How to combine these queries together?
推荐答案
如何将这些查询组合在一起?
How to combine these queries together?
使用 as_scalar()
或 标签()
:
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 属性有效.
Using as_scalar()
limits returned columns and rows to 1, so you cannot get the whole model object using it (as query(PropertyValuation)
is a select of all the attributes of PropertyValuation
), but getting just the valuation attribute works.
但我完全坚持使用 order_by 表达式 - 我无法准备子查询以稍后传递排序成员.
but I completely stuck on order_by expression - I cannot prepare subquery to pass ordering member later.
以后就不用传了.您当前声明子查询的方式很好,因为 SQLAlchemy 可以 自动将 FROM 对象与封闭查询的对象相关联.我尝试创建在某种程度上代表您所拥有内容的模型,以下是上述查询的工作方式(添加换行符和缩进以提高可读性):
There's no need to pass it later. Your current way of declaring the subquery is fine as it is, since SQLAlchemy can automatically correlate FROM objects to those of an enclosing query. I tried creating models that somewhat represent what you have, and here's how the query above works out (with added line-breaks and indentation for readability):
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
这篇关于如何将 SQL 标量子查询转换为 SQLAlchemy 表达式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!