Generate sql with subquery as a column in select statement using SQLAlchemy
correlated-subquery, postgresql, python, sqlalchemy
Solution
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.
Problem
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