to use your own example:
lastPostQ =
s.query(db.Post.ID).filter(db.Post.ThreadID==db.Thread.ID).correlate(Thread).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)
see correlate() above. It means, "don't put Thread in the FROM clause of this
subquery".
On Jan 27, 2013, at 6:52 PM, Dave Pedu wrote:
> 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 [email protected].
> To unsubscribe from 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.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from 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.
