Subqueries With SQLAlchemy

I have been developing the new version of www.osesturkiye.com for the Turkish version of the show called "The Voice". It's already built with TurboGears, mako and SQLAlchemy. In the new version we have a gallery and many photos in it. My SQLAlchemy model is like this: class GalleryPhoto(DeclarativeBase): __tablename__ = 'gallery_photo' id = Column(Integer, primary_key=True) photo_image = Column(UnicodeText) photo_description = Column(UnicodeText) dateline = Column(DateTime, default=datetime.now) The problem I have is, in the photo detail page I will display "Previous" and "Next" links. I can implement this with two ways: 3 queries for image itself, previous image id and the next image id. """ photo itself: SELECT * FROM gallery_photo WHERE id = :photo_id previous photo: SELECT id from gallery_photo WHERE id < :photo_id ORDER BY id DESC LIMIT 1 next photo: # SELECT id FROM gallery_photo WHERE id > :photo_id ORDER BY id ASC LIMIT 1 """ # with SQLAlchemy photo = DBSession.query(GalleryPhoto.id, GalleryPhoto.photo_image).filter_by(id=photo_id).one() prev_photo = DBSession.query(GalleryPhoto.id).filter(GalleryPhoto.id < photo_id).order_by(GalleryPhoto.id.desc()).one() next_photo = DBSession.query(GalleryPhoto.id).filter(GalleryPhoto.id > photo_id).order_by(GalleryPhoto.id.asc()).one() 1 query with subqueries to rule them all. """ SELECT p1.id, p1.photo_image, (SELECT p2.id FROM gallery_photo p2 WHERE p2.id < p1.id ORDER BY p2.id DESC LIMIT 1) AS prev_photo, (SELECT p3.id FROM gallery_photo p3 WHERE p3.id > p1.id ORDER BY p3.id ASC LIMIT 1) AS next_photo FROM gallery_photo p1 WHERE p1.id = :photo_id """ # with SQLAlchemy from sqlalchemy import select p1 = GalleryPhoto.__table__.alias("p1") p2 = GalleryPhoto.__table__.alias("p2") p3 = GalleryPhoto.__table__.alias("p3") prev_query = (select([p2.c.id]).where(p1.c.id > p2.c.id)\ .order_by(p2.c.id.desc()).limit(1)).label("prev_image") next_query = (select([p3.c.id]).where(p1.c.id < p3.c.id)\ .order_by(p3.c.id.asc()).limit(1)).label("next_image") photo = DBSession.query(p1.c.id, p1.c.photo_image, prev_query, next_query)\ .filter(p1.c.id == photo_id).one() Beware that the one() method throws an sqlalchemy.orm.exc.NoResultFound exception if there are no results so you may need to take care of the exception.