PostgreSQL 多表关联查询:如何返回含无分类的所有用户活动
问题根因
你当前查不到无分类活动的核心原因和CASE语句没有关系,是连接类型用错了:
- 你写的
JOIN本质是*INNER JOIN(内连接)*,规则是只有两侧关联表都能匹配到对应行,整条记录才会保留到结果里 - 对于没有分配任何分类的活动,它在
categories_to_activities关联表中没有对应条目,在内连接的匹配环节就会被直接过滤,自然不会出现在查询结果中
正确实现方案
只需要把内连接替换为左连接(LEFT JOIN,左外连接),再配合聚合逻辑的空值处理即可,不需要额外写复杂的CASE判断。左连接的逻辑是:无论右侧关联表有没有匹配数据,都会完整保留左侧主表(也就是你的activities表)中符合条件的全部行。
调整后的可直接使用的SQL语句:
SELECT a.*, COALESCE( ARRAY_AGG(ROW_TO_JSON(c)) FILTER (WHERE c.category_id IS NOT NULL), ARRAY[]::json[] ) AS categories FROM activities a -- 替换为左连接,无分类的活动也会被保留 LEFT JOIN categories_to_activities ca ON a.activity_id = ca.activity_id LEFT JOIN categories c ON ca.category_id = c.category_id WHERE a.user_id = ${userId} GROUP BY a.activity_id;
逻辑细节说明
- 两处
LEFT JOIN替换原有的内连接,从根源上避免无分类活动被连接逻辑过滤 FILTER (WHERE c.category_id IS NOT NULL):左连接匹配失败时,关联的分类表字段会全部返回null,这部分记录不会被聚合到分类数组中,避免出现[null]这种包含无效值的数组- 外层
COALESCE:如果某条活动完全没有关联分类,ARRAY_AGG会返回null,这里统一将其替换为空json数组,保证返回的categories字段永远是数组格式,下游业务不需要额外做判空兼容
内容的提问来源于stack exchange,提问作者Vidlička LujVitlóón
相关产品推荐
相关产品推荐

