Writing a subquery with SQLAlchemy core

Charles-Axel Dein

We have three tables:

  • Folders (id, company_id)
  • Milestones (id, is_done, value: i.e. when the milestone will happen)
  • Members (folder_id, user_id): a member of this folder

A folder has multiple milestones and multiple members. We would like to get a list of (, max(milestone.value), member.user_id) for each folder ID and member user ID.

As usual with SQLAlchemy, it is a good idea to start by writing the query in plain SQL. Here's one way to do it:

select f1.*, m.user_id from (
    select, max(m.value)
    from folders f
    join milestones m on m.folder_id =
    where f.company_id = :id group by
as f1

left join folders_members m on m.folder_id =
order by

And here's how to write it with SQLAlchemy core:

f = t_folders.c
m = t_milestones.c
members_t = t_folders_members

f1 = (
    select([, func.max(m.value).label("value")])
    .select_from(t_folders.join(t_milestones, == m.folder_id))
            f.company_id == company_id,
f2 = select([, f1.c.value,]).select_from(
    f1.join(members_t, members_t.c.folder_id ==

res = self.session.execute(f2)