python - Add calculated column in lateral join SQLAlchemy

Question

I am trying to write the following PosgreSQL query in SQLAlchemy:

SELECT DISTINCT user_id
FROM
(SELECT *, (amount * usd_rate) as usd_amount
FROM transactions AS t1
LEFT JOIN   LATERAL (
                SELECT rate as usd_rate
                FROM fx_rates fx 
                WHERE (fx.ccy = t1.currency) AND (t1.created_date > fx.ts) 
                ORDER BY fx.ts DESC
                LIMIT 1 
            ) t2 On true) AS complete_table
WHERE type = 'CARD_PAYMENT' AND usd_amount > 10

So far, I have the lateral join by using subquery in the following way:

lateral_query = session.query(fx_rates.rate.label('usd_rate')).filter(fx_rates.ccy == transactions.currency,
                             transactions.created_date > fx_rates.ts).order_by(desc(fx_rates.ts)).limit(1).subquery('rates_lateral').lateral('rates')



task2_query = session.query(transactions).outerjoin(lateral_query,true()).filter(transactions.type == 'CARD_PAYMENT')
print(task2_query)

This produces:

SELECT transactions.currency AS transactions_currency, transactions.amount AS transactions_amount, transactions.state AS transactions_state, transactions.created_date AS transactions_created_date, transactions.merchant_category AS transactions_merchant_category, transactions.merchant_country AS transactions_merchant_country, transactions.entry_method AS transactions_entry_method, transactions.user_id AS transactions_user_id, transactions.type AS transactions_type, transactions.source AS transactions_source, transactions.id AS transactions_id 
FROM transactions LEFT OUTER JOIN LATERAL (SELECT fx_rates.rate AS usd_rate 
FROM fx_rates 
WHERE fx_rates.ccy = transactions.currency AND transactions.created_date > fx_rates.ts ORDER BY fx_rates.ts DESC 
 LIMIT %(param_1)s) AS rates ON true 
WHERE transactions.type = %(type_1)s

Which print the correct lateral query,but so far I don't know how to add the calculated field (amount*usd_rate), so I can apply the distinct and where statements.

标签： pythonpostgresqlsqlalchemy

Answer 1

Add the required entity in the Query, give it a label, and use the result as a subquery as you've done in SQL:

task2_query = session.query(
        transactions,
        (transactions.amount * lateral_query.c.usd_rate).label('usd_amount')).\
    outerjoin(lateral_query, true()).\
    subquery()

task3_query = session.query(task2_query.c.user_id).\
    filter(task2_query.c.type == 'CARD_PAYMENT',
           task2_query.c.usd_amount > 10).\
    distinct()

On the other hand wrapping it in a subquery should be unnecessary, since you can use the calculated USD amount in a WHERE predicate in the inner query just as well:

task2_query = session.query(transactions.user_id).\
    outerjoin(lateral_query, true()).\
    filter(transactions.type == 'CARD_PAYMENT',
           transactions.amount * lateral_query.c.usd_rate > 10).\
    distinct()