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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
