sqlalchemy - Select columns and join from multiple tables using sqlalchemy

Question

I'm trying to do something that I thought should be simple in sqlalchemy using a select statement. Basically I have two tables and I want to select a few columns from them and do a left outer join with a group by. In SQL this would look something like this:

SELECT
  t1.id,
  t1.name,
  count(t2.id) as child_count
FROM parent t1
LEFT OUTER JOIN child t2
  ON t1.id = t2.parent_id
GROUP BY t1.id, t1.name;

In sqlalchemy I can do something close, but not exactly right by doing this:

sqlalchemy.select(
  t1.id, 
  t1.name, 
  sqlalchemy.func.count(t2.id)
).where(
  t1.id==t2.parent_id
).group_by(
  t1.id, t1.name
)

However, as soon as I start using .join or .outerjoin it seems to get confused and I can't seem to work out how to do it.

Any guidance? It seems like I am missing something obvious.

标签： sqlalchemy