mengu on web programming.

Subqueries With SQLAlchemy

I have been developing the new version of 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, 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.photo_image).filter_by(id=photo_id).one() prev_photo = DBSession.query( < photo_id).order_by( next_photo = DBSession.query( > photo_id).order_by( 1 query with subqueries to rule them all. """ SELECT, p1.photo_image, (SELECT FROM gallery_photo p2 WHERE < ORDER BY DESC LIMIT 1) AS prev_photo, (SELECT FROM gallery_photo p3 WHERE > ORDER BY ASC LIMIT 1) AS next_photo FROM gallery_photo p1 WHERE = :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([]).where( >\ .order_by("prev_image") next_query = (select([]).where( <\ .order_by("next_image") photo = DBSession.query(, p1.c.photo_image, prev_query, next_query)\ .filter( == 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.
Did you enjoy this post? You should follow me on twitter here.


Pedro said on 23/12/2011 03:25 AM
As on olde hand at sql, the sqlalchemy looks a mess!! I like sa, and its very useful orm, but indeed it seems awfully complicated to "construct" a query, and very often just fallback onto raw sql.. and its problems..

Tom said on 25/12/2011 16:48 PM
@Pedro Its really not, I am not sure why the author used such messy code, you don't need the table alias calls at all.

Leave a Response

No HTML allowed. You can use markdown.
E-Mail* (not published):
Web site: