SELECT user_posts.id, 
   COUNT(user_post_comments.post_id) as number_of_comments,
   COUNT(user_post_reactions.post_id) as number_of_reactions

FROM user_posts

   LEFT JOIN user_post_comments
   ON (user_posts.id = user_post_comments.post_id)

   LEFT JOIN user_post_reactions
   ON (user_posts.id = user_post_reactions.post_id)
WHERE user_posts.user_id = '850e6511-2f30-472d-95a1-59a02308b46a'
group by user_posts.id

I have this query for getting the number of comments and reactions from another table by post_id

current output screenshot

CodePudding user response：

To caluclate number of comments and reactions just use subqueries. No need to join and group by.

SELECT user_posts.id, 
   ( select COUNT(*) from user_post_comments
        where user_posts.id = user_post_comments.post_id
   ) as number_of_comments,
   ( select COUNT(*) from user_post_reactions
        where user_posts.id = user_post_reactions.post_id
   ) as number_of_reactions
FROM user_posts
WHERE user_posts.user_id = '850e6511-2f30-472d-95a1-59a02308b46a'

CodePudding user response：

If both joins return non-null rows, what you get for each count is the product of the number of rows from each for a given user_posts.id. One way you could fix that is by counting distinct identifiers for each table, e.g.

COUNT(DISTINCT user_post_comments.id) as number_of_comments

(Assuming "id" exists as a primary key on that table). This may not be spectacularly efficient, but is relatively simple.