PostgreSQL查询关联指定标签ID列表的多对多关系文章记录

场景说明 #

现有posts文章表与tags标签表为多对多关系，二者通过中间pivot表posts_tags关联，需根据传入的标签ID列表检索符合规则的文章记录。

表结构与测试数据 #

posts表（文章表） #

id | text
--------
1  | "foo"
2  | "bar"
3  | "baz"

posts_tags表（关联中间表） #

post_id | tag_id
-----------------
1       | 1
1       | 2
1       | 3
2       | 1
3       | 1

tags表（标签表） #

id | name
--------
1  | "foo"
2  | "bar"
3  | "baz"

返回规则 #

• 传入标签ID列表为[1,2,3]时，返回结果为[{id: 1, text: "foo"}]
• 传入标签ID列表为[1]时，返回结果为[{id: 1, text: "foo"}, {id: 2, text: "bar"}, {id: 3, text: "baz"}]

规则核心：返回同时关联了传入列表中所有标签的不重复文章记录，传入标签集合是返回文章关联标签集合的子集。

实现SQL #

SELECT p.*
FROM posts p
JOIN posts_tags pt 
  ON p.id = pt.post_id
WHERE pt.tag_id IN (/* 替换为实际传入的标签ID列表，比如1,2,3 */)
GROUP BY p.id
HAVING COUNT(DISTINCT pt.tag_id) = /* 替换为传入标签的总数量，比如传入3个标签就填3 */;

逻辑说明 #

• 用JOIN关联文章表和中间表，过滤掉没有任何标签关联的文章
• 用IN条件先筛选出关联了任意一个传入标签的记录，缩小查询范围
• 按文章ID分组，通过HAVING子句统计每个文章实际匹配到的传入标签数量，只有匹配数量等于传入标签总数量的文章，才满足“关联了所有传入标签”的要求
• COUNT(DISTINCT pt.tag_id)是为了避免中间表存在脏数据（同一post_id和tag_id重复存储）导致统计错误

测试验证 #

1. 传入标签列表[1,2,3]，标签总数量为3：只有id=1的文章匹配到3个传入标签，符合返回要求
2. 传入标签列表[1]，标签总数量为1：id=1、2、3的文章都匹配到1个传入标签，全部符合返回要求

补充：如果需求是只要关联列表中任意一个标签就返回（即OR逻辑），可以使用更简单的SQL，注意加去重避免同一文章关联多个标签时返回重复记录：

SELECT DISTINCT p.*
FROM posts p
JOIN posts_tags pt ON p.id = pt.post_id
WHERE pt.tag_id IN (/* 传入标签ID列表 */);

该SQL在传入[1,2,3]时会返回id=1、2、3三条记录，和本次需求的预期不符，仅作为场景扩展参考。

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