基于逻辑 AND 而不是逻辑 OR 查找关联

Question

在我的数据模型中，一张照片可以有许多与之关联的标签。

所以我的photos桌上有两张照片。

 id | file_name
----+----------------------
  1 | DSCN0317.jpg
  2 | DSCN0291.jpg

我有两个标签的tags表

 id |      name
----+----------------
  5 | Vacation
  6 | Work

我有一个映射表，将两个连接在一起。

   photo_id | tag_id
----------+--------
        1 |      5
        2 |      5
        2 |      6

因此，在此示例中，照片 1 具有单个标记 (5(，照片 2 具有多个标记 (5， 6(

在我的应用程序中，我需要查找具有特定标签列表的所有照片。更重要的是，如果可能的话，我需要在单个查询中完成它

(为什么它需要单个查询的原因超出了这个问题的范围，并且与我的应用程序的具体实现有关(。

例如，我想"查找同时具有标签 5 和 6 的所有照片">

SELECT DISTINCT "photos".*
FROM "photos"
INNER JOIN "photo_tags"
  ON "photo_tags"."photo_id" = "photos"."id"
WHERE "photo_tags"."tag_id" IN (5, 6)
;

但此查询不正确，因为它返回具有标记 6 或 7 的所有照片。我正在寻找有 6 和 7 的照片。

有什么方法可以转换上述查询来做到这一点吗？

谢谢！

Answer 1

在连接之前将标签聚合到数组中。然后检查数组是否包含所需的标签：

select distinct photos.*
from
    photos
    inner join (
        select photo_id as id, array_agg(tag_id) as tags
        from photo_tags
        group by photo_id
    ) photo_tags using(id)
where array[5, 6] <@ tags

Answer 2

您可以使用

EXISTS 来查找同时具有 ID 5 和 ID 6 的照片：

SELECT "photos".*
FROM "photos"
WHERE EXISTS (
    SELECT "photo_tags"."tag_id" 
    FROM "photo_tags" 
    WHERE "photo_tags"."photo_id" = "photos"."id" 
        AND "photo_tags"."tag_id" = 5)
AND EXISTS (
    SELECT "photo_tags"."tag_id" 
    FROM "photo_tags" 
    WHERE "photo_tags"."photo_id" = "photos"."id" 
        AND "photo_tags"."tag_id" = 6)

Answer 3

使用聚合和条件不同tag_id计数来尝试此操作：

select p.*
from photos p
join (
    select 
        photo_id
    from tags
    where 
        tag_id in (5, 6)
    group by 
        photo_id
    having 
        count(distinct tag_id) = 2
) t on p.id = t.photo_id;

或相关查询：

select p.*
from photos p
where exists (
    select
        1
    from tags t
    where
        t.tag_id in (5, 6)
        and t.photo_id = p.id
    group by 
        id
    having 
        count(distinct tag_id) = 2
);