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 -~----------~----~----~----~------~----~------~--~---
