Comment convertir une sous-requête scalaire SQL en expression SQLAlchemy ?

Question

J'ai besoin d'un peu d'aide pour exprimer en langage SQLAlchemy mon code comme suit :

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

Sous-requête interne visant à obtenir la valeur d'une propriété à partir d'une table propert_valuations avec des colonnes ( zip_code INT, valuation DECIMAL, as_if DATE ) la plus proche de la date de vente du tableau sales . Je sais comment le réécrire, mais je suis complètement bloqué. order_by Je ne peux pas préparer une sous-requête pour passer le membre ordonnateur plus tard.

J'ai actuellement les questions suivantes :

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_)

Comment combiner ces requêtes ?

Answer 1

Comment combiner ces requêtes ?

Utilisation as_scalar() ou 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)

Utilisation as_scalar() limite les colonnes et les lignes renvoyées à 1. Il n'est donc pas possible d'obtenir l'ensemble de l'objet du modèle en l'utilisant (comme le fait query(PropertyValuation) est une sélection de tous les attributs de PropertyValuation ), mais en obtenant uniquement le évaluation fonctionne.

mais je suis complètement bloqué sur l'expression order_by - je ne peux pas préparer une sous-requête pour passer le membre ordonnateur plus tard.

Il n'est pas nécessaire de l'adopter plus tard. Votre façon actuelle de déclarer la sous-requête est très bien telle quelle, puisque SQLAlchemy peut établir une corrélation automatique entre les objets FROM et ceux d'une requête englobante . J'ai essayé de créer des modèles qui représentent quelque peu ce que vous avez, et voici comment la requête ci-dessus fonctionne (avec des sauts de ligne et une indentation ajoutés pour plus de lisibilité) :

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