1

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;
user3403785
  • 23
  • 3
  • This probably can help: https://stackoverflow.com/questions/38878897/how-to-make-a-subquery-in-sqlalchemy or https://stackoverflow.com/questions/18227100/sqlalchemy-subquery-in-from-clause-without-join – jorzel Dec 22 '21 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 Dec 23 '21 at 03:54

1 Answers1

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"))
user3403785
  • 23
  • 3