Postgres 12多表JOIN查询无结果及post_comment_response_approval数据过滤问题求助

嘿，我帮你找到了当前查询的两个核心问题，还有对应的修正方案：

首先，你的JOIN team_member_manager条件搞反了关联关系——你把帖子作者的ID（post.team_member_id，也就是team_member.id）当成了管理员的ID（managing_team_member_id），但实际上在team_member_manager表里，managing_team_member_id是管理员的ID（你的示例里是68893），managed_team_member_id才是被管理的用户（也就是帖子作者60735）。这就导致你的join条件永远匹配不上，自然返回空结果。

另外，你用来聚合审批记录的子查询没有做任何过滤，所以会把所有post_comment_response_approval的记录都塞进去，包括不在team_member_manager里的666那条，这也不符合你的需求。

修正后的查询代码 #

SELECT 
    pcr.*,
    (
        SELECT JSON_AGG(pcra_inner)
        FROM post_comment_response_approval pcra_inner
        WHERE pcra_inner.post_comment_response_id = pcr.id
          -- 只保留有管理员（属于team_id=91）管理的审批人记录
          AND EXISTS (
              SELECT 1 
              FROM team_member_manager tmm_inner
              JOIN team_member tm_inner ON tm_inner.id = tmm_inner.managing_team_member_id
              WHERE tmm_inner.managed_team_member_id = pcra_inner.team_member_id
                AND tm_inner.team_id = 91
          )
    ) AS approvals,
    COUNT(*) OVER() AS total_count
FROM post_comment_response pcr
LEFT JOIN post_comment pc ON pc.id = pcr.post_comment_id
LEFT JOIN post p ON p.id = pc.post_id
-- 核心过滤：仅保留帖子作者被team_id=91的管理员管理的记录
WHERE EXISTS (
    SELECT 1 
    FROM team_member_manager tmm
    JOIN team_member tm ON tm.id = tmm.managing_team_member_id
    WHERE tmm.managed_team_member_id = p.team_member_id
      AND tm.team_id = 91
);

关键修改点解释 #

1. 修复主查询的过滤逻辑：
   我用EXISTS子查询替代了原来容易混淆的join链，明确筛选出：帖子的作者（p.team_member_id）必须是被team_id=91的管理员管理的记录。这样就避免了之前join顺序导致的关联错误。

2. 过滤无效的审批记录：
   在聚合approvals的子查询里，我加了EXISTS条件，只保留那些审批人（pcra_inner.team_member_id）存在于team_member_manager中，且对应的管理员属于team_id=91的记录——这样就能自动排除示例里666那条不符合要求的审批。

3. 调整join类型（可选）：
   把原来的JOIN改成LEFT JOIN是为了保留post_comment_response中所有符合过滤条件的记录（哪怕没有对应的post_comment或post）。如果你的业务要求必须有对应的帖子和评论，可以改回JOIN。

测试数据验证 #

用你提供的测试数据运行这个查询，会得到你想要的结果：

• 返回post_comment_response中id=1的那条记录
• approvals字段只包含team_member_id=60735的审批（排除了666的）
• total_count的值为1

完全满足你的需求：仅返回存在于team_member_manager中的相关记录，同时过滤掉无效的审批项。

内容的提问来源于stack exchange，提问作者jabepa