mengu on web programming.

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.
Did you enjoy this post? You should follow me on twitter here.

Comments

Pedro said on 23/12/2011 01: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 14: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.
Name*:
E-Mail* (not published):
Web site:
Response: