count subquery in sqlalchemy-CodePudding

I'm having some trouble translating a subquery into sqlalchemy. I have two tables that both have a store_id column that is a foreign key (but it isn't a direct many-to-many relationship) and I need to return the id, store_id and name from table 1 along with the number of records from table 2 that also have the same store_id. I know the SQL that I would use to return those records I'm just now sure how to do it using sqlalchemy.

SELECT
table_1.id
table_1.store_id,
table_1.name,
(
    SELECT
        count(table_2.id)
    FROM
        table_2 
    WHERE
        table_1.store_id = table_2.store_id
) AS store_count FROM table_1;

CodePudding user response：

This post actually answered my question. I must have missed it when I was searching initially. My solution below.

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

store_count = session.query(func.count(Table2.id)).filter(Table2.store_id == Table1.store_id)

session.query.add_columns(Table1.id, Table1.name, Table1.store_id, store_count.label("store_count"))