postgresql - 返回连接表但仅按主表 ID 分组

Question

我正在尝试返回 4 个表的连接，但按“评论计数”对它们进行分组。但是我一直收到一个错误，告诉我将每个表的“id”添加到 GROUP BY 子句中。但是这样做会为每个“评论计数”提供 '1'
表:

用户

配置文件(加入用户(有 user_id 列))

帖子(加入用户(有 user_id 列))

评论(加入帖子和用户(有 user_id 和 post_id 列))

这是我的查询:

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";

我收到错误 column "User.id" must appear in the GROUP BY clause or be used in an aggregate function 但是当我添加它时，columncount 是错误的。

*如何按 Post.id 列分组并仍然从连接表中获取相应数据？ *

注意一旦我弄清楚了，我想将正确的查询转换为 sequelize.js，所以如果有人碰巧知道如何使用 sequelize 做到这一点，我会非常欢迎:)

Answer 1

我希望此查询给出每个帖子的评论数。

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

现在，无论您的查询的其余部分做什么，您都可以从上面的查询中加入“post_id”。沿着这些路线的东西应该工作。

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;