[sqlalchemy] subquery in select statement
- by webjunkie
Hi guys, I have two tables (albums,pictures) in a one to many relationship and I want to display each albums details with one picture so I have the following query
select albums.name,(select pictures.path from pictures where pictures.albumid=albums.id limit 1) as picture from albums where ...
Now I'm struggling creating this on Pylons with sqlalchemy I tried to do the following
picture = Session.query(model.Picture)
sub_q = picture.filter_by(albumid = model.Album.id).limit(1).subquery()
album_q = Session.query(model.Album, sub_q)
result = album_q.all()
but it creates the following statement displaying the incorrect picture beacuse the table albums is included in the subquery
select albums.name,(select pictures.path from pictures,albums where pictures.albumid=albums.id) from albums where ...
Am I doing it wrong?, is this even possible in sqlalchemy?.