代码之家 › 专栏 › 技术社区 › RobertW

Postgres SQL:寻找返回与媒体文件匹配的所有标签的方法

join postgresql

RobertW · 技术社区 · 11 月前

我之前问过一个问题,但有点搞砸了,所以我在这里再次重新表述。

我有三个相关的表格:

media
media_tags (桥牌桌)
tags

这是我到目前为止的代码:

SELECT m.media_id, m.name, array_agg(distinct t.tag) filter (WHERE t.date_deleted IS NULL) AS tags 
FROM media m 
LEFT JOIN media_tags mt USING (media_id) 
LEFT JOIN tags t USING (tag_id)
WHERE m.date_deleted IS NULL AND t.tag = ANY(array['dog','cat'])
GROUP BY m.media_id, m.name

这非常接近我想要的,但并不完全正确。想象一下,有这些媒体记录:

带有以下标签的媒体1:狗、鸟、羊、马
带有以下标签的媒体2:狗、猫、牛
带有以下标签的媒体3:猫、马、兔子
带有这些标签的媒体4:牛、马、羊

我上面的查询确实返回了媒体1、2和;3,但所示标签仅包括 dog & cat 我想要的是 标签 如果与数组中的任何值匹配,则始终包含其所有标签。因此,如果查询中有['dog','cat'],则媒体1,2,&3应该返回它们各自的标签,但Media 4不应该返回,因为没有匹配项。

我感觉我很接近一个解决方案,但就是想不出来。

1 回复 | 直到 11 月前

Tim Biegeleisen 11 月前

ANSI标准的方法(不使用Postgres数组)是首先找到所有匹配的标签,然后使用该结果来限制当前的查询。

WITH cte AS (
    SELECT m.media_id
    FROM media m
    INNER JOIN media_tags mt ON mt.media_id = m.media_id
    INNER JOIN tags t ON t.tag_id = mt.tag_id
    WHERE t.tag IN ('dog', 'cat')
    GROUP BY m.media_id
    HAVING COUNT(DISTINCT t.tag) = 2
)

SELECT m.media_id, m.name, ARRAY_AGG(DISTINCT t.tag) FILTER (WHERE t.date_deleted IS NULL) AS tags
FROM media m
INNER JOIN media_tags mt ON mt.media_id = m.media_id
INNER JOIN tags t ON t.tag_id = mt.tag_id
WHERE m.media IN (SELECT media_id FROM cte)
GROUP BY m.media_id, m.name;

推荐文章