count subquery in sqlalchemy

Question

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;

This probably can help: stackoverflow.com/questions/38878897/… or stackoverflow.com/questions/18227100/… — jorzel
– jorzel, Commented Dec 22, 2021 at 22:29
Hi thanks for your response, I'm not confused about How to make a subquery in sqlalchemy just confused about how to make this specific subquery. — user3403785
– user3403785, Commented Dec 23, 2021 at 3:54

user3403785 · Accepted Answer · 2021-12-23 06:38:37Z

0

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"))

edited Dec 23, 2021 at 6:38

answered Dec 23, 2021 at 6:33

user3403785

234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

count subquery in sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related