title: Select columns and join from multiple tables using sqlalchemy
body: <p>I'm trying to do something that I thought should be simple in sqlalchemy using a <code>select</code> 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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>In sqlalchemy I can do something close, but not exactly right by doing this:</p>
  
  <pre><code>sqlalchemy.select(
    t1.id, 
    t1.name, 
    sqlalchemy.func.count(t2.id)
  ).where(
    t1.id==t2.parent_id
  ).group_by(
    t1.id, t1.name
  )
  </code></pre>
  
  <p>However, as soon as I start using <code>.join</code> or <code>.outerjoin</code> it seems to get confused and I can't seem to work out how to do it.</p>
  
  <p>Any guidance? It seems like I am missing something obvious.</p>