SqlAlchemy prev/next accessors?

Question

Is there any way to get the prev/next records from a SqlAlchemy query? Like so:

record.id
record.next.id
record.prev.id

I could add the prev/next methods to each model myself, but I'm wondering if there is already some automagical way that I overlooked. The web application in question is written in Pylons.

Edit: This is probably a dirty hack, but it works. I imported the sqlalchemy Session object into my model and did this:

def next(self):
    return Session.query(Blog).filter(Blog.id > self.id).order_by(Blog.id).first()

def prev(self):
    return Session.query(Blog).filter(Blog.id < self.id).order_by(desc(Blog.id)).first()

Answer 1

Plus 1 and Minus 1 on last database record will work sometimes. What if you delete couple of last records. Next id will not be plus one or minus one on last id. That is not session thing it is mysql thing. To get next id you will have to do some queries on information schema like this.

q = "SELECT auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE table_name='table_name'"
a = s.execute(q) #where s is sqlalchemy session

for b in a:

   print b
   print b[0]

that will give you something like: (163459L,) 163459

In my case that is true.

And for the prev you can always do .first() on you last record. That is prev record.

Answer 2

Here is what I use to retrieve next/prev from an object using sqlalchemy. The idea is to get the row_number of the object from the initial list and do another request with an offset +/- 1.

I'm using a subquery with ROW_NUMBER (How do I use ROW_NUMBER()?) to retrieve the row_number from initial query:

row_number = func.row_number().over(order_by=orders).label("row_number")
row_number_subquery = self.getlistquery(columns=chain([row_number], pk_columns)).subquery()

Then I join with the model on primary keys:

join_clause = [getattr(row_number_query.c, pk.key) == getattr(model, pk.key) for pk in pk_columns]

I filter by the primary keys of the object:

filter = [getattr(model, pk.key) == pk_values[index] for index, pk in enumerate(pk_columns)]

I create the final query to get the offset of the object:

offset = db.session.query(row_number_subquery).join(model, *join_clause).filter(*filter).first()

Then the element contains the offset needed for next or offset-2 for previous (as starting at 1 for row_number and 0 for offset)

self.getlistquery().offset(offset)

Answer 3

What I would suggest doing is in your original query requesting -1 and +1 so that you can easily get the ID's without having to run another query saving time on processing on the SQL server.

Your solution definitely works, but requires an extra two queries. You could even make it a subquery so that if you just want to retrieve a single row and throw previous/next on it that data is stored in a column created by a sub-query.