Hi, Thanks for the reply (and sorry for my late response!).
I am indeed on a 0.7 build. Could you go into a little more detail about how correlate is used? The documentation for that method seems a little vague to me. Thanks! On Friday, January 25, 2013 1:03:00 AM UTC-5, Michael Bayer wrote: > > > On Jan 24, 2013, at 11:03 PM, Dave Pedu wrote: > > Hi all, > > I am trying to run a query like this one, using sqlalchemy: > > SELECT > t.`ForumID`, > ( SELECT `ID` FROM `posts` p WHERE `ThreadID` = t.`ID` ORDER BY p.`Date` > DESC LIMIT 1 ) as `LastPost` > FROM `threads` t > WHERE t.`Deleted` = 0 > > > I am unsure how to achieve the subquery that comes out as 'LastPost'. > > Closest I have gotten is this: > > lastPostQ = s.query(db.Post.ID).filter(db.Post.ThreadID==db.Thread.ID > ).order_by(desc(db.Post.Date)).limit(1).label("LastPost") > q = s.query( > db.Thread.ForumID, > lastPostQ > ).\ > join(db.Forum, db.Forum.ID==db.Thread.ForumID).\ > join(db.ForumPermission, db.ForumPermission.ForumID==db.Thread.ForumID).\ > filter(db.Thread.Deleted==0) > > > Which generates the following SQL: > > SELECT > threads."ForumID" AS "threads_ForumID", > (SELECT posts."ID" FROM posts, threads WHERE posts."ThreadID" = > threads."ID" ORDER BY posts."Date" DESC LIMIT :param_1) AS "LastPost" > FROM > threads > WHERE > threads."Deleted" = :Deleted_1 > > > Which is close, but the subqueries select statement is selecting "FROM > posts, threads" where it should only be "FROM posts". > > I don't know how to procede from here. A member on IRC suggested > using correlate() but I am unsure as how or where to use it, as the > documentation was unclear. > > > if you're on 0.7 then the correlation used by query() is not automatic, > you need to call query = query.correlate(Thread) on that subquery so it > knows that "Thread" as a FROM object will be supplied from an enclosing > query. In 0.8 the correlation is automatic by default with Query, the same > way as it works with a select() construct. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.