基于标签关联的相关文章SQL查询函数实现需求

需求 #

输入UUID类型的post_id，返回posts表记录集，按与目标文章的标签匹配度降序排序：优先返回匹配所有标签的文章，其次依次是4个、3个、2个标签匹配的，最后是单个标签匹配的。前端通过Supabase自行处理分页和结果限制。

依赖数据表结构 #

（常规标签多对多设计，若你的表结构不同，可调整关联逻辑）

• posts：id uuid PRIMARY KEY, title text, content text, created_at timestamptz
• post_tags：post_id uuid REFERENCES posts(id), tag_id uuid, PRIMARY KEY(post_id, tag_id)

完整函数代码 #

CREATE OR REPLACE FUNCTION get_related_posts(target_post_id uuid)
RETURNS SETOF posts
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT p.*
  FROM posts p
  JOIN (
    -- 计算每篇文章与目标文章的标签匹配数
    SELECT 
      pt.post_id,
      COUNT(pt.tag_id) AS match_count
    FROM post_tags pt
    JOIN post_tags target_pt ON target_pt.tag_id = pt.tag_id
    WHERE target_pt.post_id = target_post_id
      AND pt.post_id != target_post_id
    GROUP BY pt.post_id
    -- 补充匹配所有标签的文章（确保这类排在最前）
    UNION ALL
    SELECT 
      p.id,
      (SELECT COUNT(*) FROM post_tags WHERE post_id = target_post_id) AS match_count
    FROM posts p
    JOIN post_tags pt ON p.id = pt.post_id
    WHERE p.id != target_post_id
    GROUP BY p.id
    HAVING COUNT(pt.tag_id) = (SELECT COUNT(*) FROM post_tags WHERE post_id = target_post_id)
  ) AS tag_matches ON p.id = tag_matches.post_id
  -- 按匹配数降序，保证优先级
  ORDER BY tag_matches.match_count DESC;
END;
$$;

关键逻辑说明 #

• 先通过post_tags关联获取目标文章的所有标签，统计其他文章与这些标签的重叠数量。
• 单独处理匹配所有标签的文章，确保其排序优先级最高。
• 全程排除目标文章自身，避免返回重复内容。
• 最终按匹配标签数量降序排序，完全符合需求的优先级规则。

调用示例 #

在Supabase中直接调用：

SELECT * FROM get_related_posts('a1b2c3d4-5678-90ef-ghij-klmnopqrstuv');

前端实现分页只需添加LIMIT和OFFSET：

SELECT * FROM get_related_posts('a1b2c3d4-5678-90ef-ghij-klmnopqrstuv') LIMIT 10 OFFSET 20;

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