dein.fr

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 (folder.id, 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 f.id, max(m.value)
    from folders f
    join milestones m on m.folder_id = f.id
    where f.company_id = :id group by f.id)
as f1

left join folders_members m on m.folder_id = f1.id
order by f1.id

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([f.id, func.max(m.value).label("value")])
    .select_from(t_folders.join(t_milestones, f.id == m.folder_id))
    .where(
        and_(
            f.company_id == company_id,
            m.is_done.is_(False),
        )
    )
    .group_by(f.id)
).alias("f1")
f2 = select([f1.c.id, f1.c.value, members_t.c.id]).select_from(
    f1.join(members_t, members_t.c.folder_id == f1.c.id)
)

res = self.session.execute(f2)