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

Question

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 1

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.