Generate sql with subquery as a column in select statement using SQLAlchemy

Antigluk picture Antigluk · Apr 27, 2017 · Viewed 10.8k times · Source

Is there a way to make SQLAlchemy generate a query with a custom column that is a subquery that correlates with current row:

SELECT
 tab1.id,
 tab1.col1, 
 ...,
 (
     SELECT count(1) FROM tab2 
     WHERE tab2.tab1_id = tab1.id
     GROUP BY tab2.col1
 ) as cnt
FROM tab1
WHERE ...
LIMIT 100

using the ORM API?

session.query(Tab1, ?(subquery for additional column)?).filter(...).limit(100)

I'm using PostgreSQL 9.3 and old version of SQLAlchemy 0.9.8

Answer

Ilja Everilä picture Ilja Everilä · Apr 27, 2017

If you need this often, and/or the count is an integral part of your Tab1 model, you should use a hybrid property such as described in the other answer. If on the other hand you need this just for a single query, then you could just create the scalar subquery using Query.label(), or Query.as_scalar():

count_stmt = session.query(func.count(1)).\
    filter(Tab2.tab1_id == Tab1.id).\
    group_by(Tab2.col1).\
    label('cnt')

session.query(Tab1, count_stmt).filter(...).limit(100)

The subquery will automatically correlate what it can from the enclosing query.