“标记”搜索/排除查询设计问题

Question

背景：我正在做一个自制项目，用于管理我自己的图像集合，并一直在尝试实现基于标签的搜索，以便我可以轻松筛选它们。

现在，我

正在使用RedBean的标记API将标签应用于每个图像的数据库条目，但是我被困在实现的特定细节上;目前，为了允许搜索多个标签将细化搜索的标签（当搜索"ABC XYZ"时，标记的图像必须具有标签"ABC"和"XYZ"），

我必须使用服务器端语言而不是 SQL 处理一些处理，然后运行（可选）第二个查询来验证任何返回的图像都没有已从结果中显式排除的标记。（搜索"ABC -XYZ"时，标记的图像必须具有标记"ABC"而不是"XYZ "）。

这里的问题是我当前的方法要求我通过服务器端代码运行所有结果，并且使任何合理的分页/结果偏移量尝试都不准确。

我的目标是只通过一个查询抓取post表中包含请求的标签（并且不包含任何排除的标签）的行，并且仍然能够对我的查询使用 LIMIT/OFFSET 参数以获得合理的分页结果。

表架构如下所示：

Table "post"
Columns:
  id (PRIMARY KEY for post table)
  (image metadata, not relevant to tag search)
Table "tag"
Columns:
  id (PRIMARY KEY for tag table)
  title (string of varying length - assume varchar(255))
Table "post_tag"
Columns:
  id (PRIMARY KEY for post_tag table)
  post_id (associated with column "post.id")
  tag_id (associated with column "tag.id")

如果可能的话，我也希望能够具有特定于post表列的 WHERE 条件。

我应该对查询结构使用什么？ 我一直在玩左连接，但无法获得解决此问题所需的确切结构。

Answer 1

这是基本思想：

LEFT OUTER JOIN是一组与您要排除的标签匹配的帖子。查询中的最后一个 WHERE 子句确保这些帖子都与第一个post表中的条目不匹配。

INNER JOIN是一组与所有标签匹配的帖子。请注意，数字 2 必须与您在 IN 子句中提供的唯一标记名称的数量匹配。

select p.*
from post p
left outer join (
    select pt.post_id    
    from post_tag pt
    inner join tag t on pt.tag_id = t.id
    where t.title in ('UVW', 'XYZ')
) notag on p.id = notag.post_id 
inner join (
    select pt.post_id    
    from post_tag pt
    inner join tag t on pt.tag_id = t.id
    where t.title in ('ABC', 'DEF')
    group by pt.post_id
    having count(distinct t.title) = 2
) yestag on p.id = yestag.post_id 
where notag.post_id is null
--add additional WHERE filters here as needed