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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
