timbo timbo - 4 months ago 6x
Python Question

How to get latest objects from SQLAlchemy

I have a single table that contains a number of revisions for an entity in another table (Tasks). So each TaskRevision row has a unique autoincremented id and a reference to the Task id.

How do I find the latest revision of all tasks? I have tried:


session.query(TaskRevision.id).group_by(TaskRevision.task_id, TaskRevision.id).having(TaskRevision.id==func.max(TaskRevision.id))


This is slightly annoying to do, but taken from this question:

subq = session.query(TaskRevision.task_id, func.max(TaskRevision.id).label("max_id")) \
session.query(Task) \
       .join(subq, Task.id == subq.c.task_id) \
       .join(TaskRevision, TaskRevision.task_id == subq.c.max_id) \
       .with_entities(Task, TaskRevision)

You can skip the second step if all you want are IDs.

Because it's a little annoying, I usually prefer to denormalize and keep a latest_task_revision_id column on Task.