Can layered subqueries spanning multiple tables reference a parent query?

Question

I have three tables setup: POSTS, DISCUSSIONS, and COMMENTS. The layout is as follows:

POSTS
id | user_id | title | body | created

DISCUSSIONS
id | post_id | user_id | title | body | created

COMMENTS
id | post_id | discussion_id | user_id | title | body | created

A post hasMany DISCUSSIONS, which then hasMany COMMENTS.

What I need to get working is a query which will give me all of the POSTS, but with a count of the number of users involved in each post. Now, by "involved" I mean I need a count of all users who have either posted a discussion to the post, or a comment to one of the post's discussions. Obviously though, my query needs to make sure that it doesn't count duplicates in the COMMENTS table that are also in the DISCUSSIONS table of the same post_id.

I can get this working "manually" by specifying the post_id in the subqueries like this:

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, this obviously doesn't do me any good because I need to be able to have the subqueries reference the parent query's post.id so that each row returns the appropriate count for each post.

Is this even possible? What does it take?

Thanks, Seth

Answer 1

I'd try this.

SELECT post_id, count(DISTINCT user_id)
FROM 
(
    SELECT id as post_id, user_id FROM posts
    UNION
    SELECT post_id, user_id FROM discussions
    UNION 
    SELECT post_id, user_id FROM comments
) a
GROUP BY post_id