How To Convert Sql Scalar Subquery To Sqlalchemy Expression

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

Solution 1:

How to combine these queries together?

Use as_scalar(), or 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)

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.

but I completely stuck on order_by expression - I cannot prepare subquery to pass ordering member later.

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
        ORDERBY abs(datediff(property_valuations.as_of, sale.date_sold))
        LIMIT ? OFFSET ?) AS back_valuation 
FROM sale
JOINpropertyONproperty.id = sale.property_id