Writing a correlated subquery with SQLAlchemy

Charles-Axel Dein

We have two tables:

  • Folders (id, company_id)
  • Milestones (id, is_done, value: i.e. when the milestone will happen)

A folder has multiple milestones. We would like to get each folder as well as its most recent milestone.

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 (see 4 Ways to Join Only The First Row in SQL):

select *
from folders f

join milestones m on = (
select id from milestones m
where m.folder_id = and f.company_id = ... and m.is_done = false
order by `value` asc
limit 1

And here's how to write it with SQLAlchemy:

subq = (session.query(
    TFolder.company_id == company_id).order_by(

query = session.query(TFolder, TMilestone).join(
    TMilestone, == subq)

res = query.all()