return join table but group by only main table id

Question

I'm trying to return a join of 4 tables, but group them by "commentcount". however i keep gettng an error telling me to add the 'id' of each table to the GROUP BY clause. But doing so gives '1' for each "commentcount" tables:

• Users
• Profiles (joined to Users (has user_id column))
• Posts(joined to Users (has user_id column))
• Comments(joined to Posts and Users (has user_id and post_id column))

here is my query:

SELECT 
    "Post".*, 
    COUNT(*) AS "CommentCount", 
    "PostComments"."id" AS "PostComments.id", 
    "User"."id" AS "User.id", 
    "User"."email" AS "User.email", 
    "User"."password" AS "User.password",  
    "User.Profile"."id" AS "User.Profile.id", 
    "User.Profile"."user_id" AS "User.Profile.user_id", 
    "User.Profile"."username" AS "User.Profile.username", 
    "User.Profile"."gender" AS "User.Profile.gender"
FROM
    "posts" AS "Post" 
LEFT OUTER JOIN 
    "post_comments" AS "PostComments" 
       ON     "Post"."id" = "PostComments"."post_id" 
          AND "PostComments"."deleted_at" IS NULL 
LEFT OUTER JOIN 
    "users" AS "User" 
       ON     "Post"."user_id" = "User"."id" 
          AND "User"."deleted_at" IS NULL 
LEFT OUTER JOIN 
    "profiles" AS "User.Profile" 
       ON  "User"."id" = "User.Profile"."user_id" 
WHERE "Post"."deleted_at" IS NULL
GROUP BY 
    "Post"."id";

I am getting the error column "User.id" must appear in the GROUP BY clause or be used in an aggregate function but when i add that, columncount is wrong.

*How Do I group by the Post.id column and still get the corresponding data from the join tables? *

N.B. I would like to convert the correct query to sequelize.js once I've got it figured out, so if anyone happens to know how to do it with sequelize, I'd be more more than welcome :)

Answer 1

I'd expect the number of comments per post to be given by this query.

select "post_id", count(*) as num_comments
from "PostComments"
group by "post_id";

Now, no matter what the rest of your query does, you can join on "post_id" from the query above. Something along these lines should work.

select ..., comment_count.num_comments
from ...
-- other joins go here . . .
left outer join (select "post_id", count(*) as num_comments
                 from "PostComments"
                 group by "post_id") comment_count
    on "Post"."id" = comment_count.post_id
where "Post"."deleted_at" is null;