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. Any help would be appreciated! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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.