0

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.

Cory
  • 22,772
  • 19
  • 94
  • 91
  • How do you use `.join` and `.outerjoin`? – Ilja Everilä Jun 06 '18 at 10:50
  • I've tried a few different ways. But one problem is that `.join` seems to want a reference to a table which means that I can't include the columns from the joined table in the original select columns, and also that the join then includes all columns from the inner table. Other structures cause me to run into this problem: https://stackoverflow.com/questions/34803234/sqlalchemy-subquery-in-from-must-have-an-alias – Cory Jun 06 '18 at 10:59
  • Are `t1` and `t2` aliased mapped classes, or are they `Table` instances or aliases? Not using the `.c` namespace would hint at the former. Have you read [How to execute “left outer join” in SqlAlchemy](https://stackoverflow.com/questions/26142304/how-to-execute-left-outer-join-in-sqlalchemy)? – Ilja Everilä Jun 06 '18 at 11:05
  • They are `Table` instances. I have read that, but the solution exhibits some of the problems I am mentioning (for example, it includes all columns of `Table2` in the join and not just one of them) – Cory Jun 06 '18 at 11:12
  • What do you mean by "it includes all columns of `Table2` in the join"? The query in the linked Q/A selects 0 columns from `Table2`, uses 1 column in the ON clause and another in the WHERE. It'd still help if you'd include your actual attempt or attempts in the question itself, in addition to the pre ANSI join you've presented. Also note that `select()` expects a list of items as the 1st positional argument instead of items as arguments. – Ilja Everilä Jun 06 '18 at 11:15
  • Hmm, I see what you mean - I had tried something slightly different that was returning all columns. That is close. Is there a way to achieve the same result using `select` instead of `query`? Or a way to go from `select` to `query`? – Cory Jun 06 '18 at 11:25
  • They're somewhat interchangeable, since the ORM is built atop the Core. The latter example in the linked Q/A uses Core, though you could write it in a more concise way with `select([...]).select_from(t1.outerjoin(t2, t1.c.id == t2.c.parent_id))`. – Ilja Everilä Jun 06 '18 at 11:29
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172590/discussion-between-cory-and-ilja-everila). – Cory Jun 06 '18 at 11:44

0 Answers0