Thank you Michael,
What about using this type of thing inside another query?
I keep getting:
AttributeError: 'Select' object has no attribute '_nested_statement'
Seth
On Sep 8, 7:16 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> an example of NOT IN (subquery):
>
> >>> from sqlalchemy import *
> >>> from sqlalchemy.sql import column, table
> >>> s =
>
> select([column("foo")]).select_from(table("bar")).where(~column("foo").in_(select([column("bar")]).select_from(table("bat")).where(column("bar")==5)))>>>
> print s
>
> SELECT foo
> FROM bar
> WHERE foo NOT IN (SELECT bar
> FROM bat
> WHERE bar = :bar_1)
>
> Seth wrote:
>
> > I'm experiencing some problems when trying to do layered subqueries,
> > especially relating to doing a NOT IN (SUBQUERY). I can't get
> > SQLAlchemy to put the sub-query SELECT inside parenthesis, so my query
> > is failing with an error.
>
> > I'm trying to do a query across three tables, with some rather
> > complicated logic.
>
> > I have 3 tables:
>
> > POSTS with columns: table with id, user_id, title, body, created
> > DISCUSSIONS with columns: id, post_id, user_id, title, body, created
> > COMMENTS with columns: id, post_id, discussion_id, user_id, title,
> > body, created
>
> > A COMMENT belongs to a DISCUSSION, which then belongs to a POST. What
> > I need to do is make a query that pulls all the POST columns, and then
> > does a count of all the users "involved" in that post. By "involved",
> > I mean that I need to get a count of the user_id's in DISCUSSIONS and
> > COMMENTS which have an entry with that post_id.
>
> > The "by hand" SQL would look something like this (brace yourself):
>
> > SELECT posts.id AS posts_id, posts.user_id AS posts_user_id,
> > posts.title AS posts_title, posts.created AS posts_created,
> > users_profile.name AS users_profile_name,
> > (anon_1.discussions_users_count + anon_1.comments_users_count) AS
> > anon_1_users_count FROM users_profile, posts
> > LEFT OUTER JOIN (SELECT discussions.post_id AS post_id, count(*) AS
> > discussions_users_count, (SELECT count(discussion_comments.user_id)
> > FROM discussion_comments WHERE discussion_comments.user_id NOT IN
> > (SELECT discussions.user_id FROM discussions WHERE discussions.post_id
> > = 1 GROUP BY discussions.user_id) AND discussion_comments.post_id = 1
> > GROUP BY discussion_comments.user_id) AS comments_users_count FROM
> > discussions GROUP BY discussions.post_id) AS anon_1 ON posts.id =
> > anon_1.post_id
> > WHERE posts.user_id = users_profile.user_id ORDER BY posts.created
> > DESC
> > LIMIT 10 OFFSET 0
>
> > However, I can't get SQLAlchemy to replicate the "NOT IN (SELECT ...)"
> > subquery setup. It strips the parenthesis after the NOT IN and so I
> > get a syntax error.
>
> > Advice would be appreciated!
>
> > Seth
>
>
--~--~---------~--~----~------------~-------~--~----~
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
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
