timbo timbo - 5 months ago 10
Python Question

Find latest two element revisions using 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.

I can find the latest revision of all tasks using:


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


How can the latest 2 revisions be found, presumably using a revised subquery?

Answer

The working SQL is pretty much:

select task_id, id from TaskRevisions where (
   select count(*) from TaskRevisions as t
   where t.task_id = TaskRevisions.task_id and t.id <= TaskRevisions.id
) <= 2

After much work, this is the answer in SQLAlchemy:

tr = aliased(TaskRevision)
q = s.query(TaskRevision.task_id, TaskRevision.id, TaskRevision.title).filter(
        s.query(func.count(tr.id)) \
            .filter(tr.id >= TaskRevision.id) \
            .filter(tr.task_id == TaskRevision.task_id) \
            .as_scalar() <= 2) \
    .order_by(TaskRevision.task_id).all()
Comments