php - 在 mysql 表中查找标记项的出现

Question

假设我有这张表:

item_id tag_id
------- ------
1       1
1       2
2       2
2       3

正如您可能想象的那样，这是一个表格，我在其中引用了一些项目和属于它们的标签。一个项目可以有多个标签，并且可以为多个项目选择一个标签。

假设我还有一个特定的标签集合(例如 tag_id = 50、73 和 119)和一个带有 ID(由 item_id 引用)的“项目”表。

是否有一个高效查询可以给我:

1. 带有这些标签的项目数
2. 元素本身？

我尝试过的

SELECT COUNT(*) FROM
(
SELECT COUNT(*) AS c FROM items_tags it JOIN items i ON i.id = it.item_id
WHERE (tag_id=7 OR tag_id=95 OR tag_id=150) AND `status`='active'
GROUP BY item_id
) t1 WHERE c=3 <-- c= number of tags

我可以得到两个结果，但查询效率非常低(看起来)。在用 EXPLAIN 检查后，我想摆脱 OR 给出的“范围”。

细化我的问题:问题是我得到了一个编写非常糟糕的 PHP 框架，它通过各种标签 ID 迭代了 900 多次。假设您有一个或多个固定 ID(选定的标签)，它会遍历所有 900 多个标签，以查找具有给定标签以及迭代标签的项目的出现次数(这是一种优化搜索，只显示所有给定标签加一个的元素)。

给定的代码是这样工作的:我选择一个或多个标签并将它们的 ID 放入查询字符串中。假设我选择了标签 54 和 77。该代码必须找到同时具有标签 54 和 77 的项目的每个项目 ID，并将它们一一列出:我们获得“具有选定标签的项目”列表。

然后，它提供了优化搜索的选择，奇怪的部分来了:PHP 代码循环遍历所有 900 多个标签，并且对于每次迭代，它都需要一个标签，并计算有多少项目具有所有标签54、77 和迭代中的那个。如果计数 > 0，它会显示带有计数的标签的名称，过滤掉所有其项目与所选标签没有任何链接的标签。

以一种“强度”较低的方式实现相同的结果会很好。

Answer 1

要获取匹配所有标签的项目 ID 列表，您可以使用此查询:

SELECT items.id
FROM items
JOIN items_tags ON items.id = items_tags.item_id
WHERE (items_tags.tag_id IN (7,95,150))
  AND (items.status = 'active')
GROUP BY items.id
HAVING COUNT(DISTINCT items_tags.tag_id) = 3

请注意，如果您确定同一项目永远不会有重复标签，则可以将 COUNT(DISTINCT items_tags.tag_id) 替换为 COUNT(*)效率。

要计算这些项目的数量，请将其包装在 COUNT 查询中:

SELECT COUNT(*)
FROM (
  SELECT items.id
  ...
) t

要获取项目列表，请将其包装在这个 SELECT 查询中:

SELECT *
FROM items
WHERE id IN (
  SELECT items.id ...
)

更新

要在与原始列表组合时获取每个剩余标签的项目计数，您可以这样做:

SELECT tag_id, COUNT(DISTINCT item_id)
FROM items_tags
WHERE item_id IN (
  SELECT items.id
  ...
)
  AND tag_id NOT IN (7,95,150)
GROUP BY tag_id