MySQL - 如何按标签查询项目，并按匹配的标签计数排序

Question

我的架构大致如下：

items ( id, title, blah )
tags (id, name )
item_tags ( item_id, tag_id )

我希望列出所有标签为“在”所选标签数组中的项目，并按与选择匹配的标签数（例如[1,2,3]）排序。

目前我有以下内容：

SELECT *, COUNT(item_tags.tag_id) AS tag_count
FROM items
JOIN item_tags
ON item_tags.item_id = items.id
WHERE item_tags.tag_id IN (1, 2, 3)
GROUP BY items.id
ORDER BY tag_count DESC

这个做法很好，但是tag_count只获取了所选物品的标签总数，我想要它是包含在(1, 2, 3)中选择的标签数。

具有标签(1, 2, 3)的物品应该排在具有标签(1, 5, 6, 7)之前。

如果有Kohana 3的ORM解决方案，则我将使用它。

- tom

Answer 1

只需将您的SELECT更改为：

SELECT *, COUNT(*) AS tag_count
.....

- gview

Answer 2

我认为你真正想要的是 GROUP_CONCAT(tag_id)（可能还要加上计数）。GROUP_CONCAT 会将你的ID连接起来，就像你展示的那样... 1, 2, 3 和 1, 5, 6, 7。如果你将这个列作为你的排序依据，应该没问题。

现在，话虽如此，你可能需要强制对连接过程进行一些格式处理，以处理像 1, 10, 100 这样在 1, 2, 3 之前的情况。因此，如果将连接后的ID字符串格式化为2或3个位置，你会得到

001, 002, 003 
vs
001, 005, 006, 007 
vs
001, 010, 100

HTH

- DRapp

---

一个群组连接不会改变分组行数（当前由COUNT(*)表示）错误的事实。正如您指出的那样，它只会创建额外的排序问题。 - Ivar Bonsaksen

然而，使用GROUP_CONCAT来调试SQL可能是个好主意。通过在SELECT语句中添加类似于CAST(GROUP_CONCAT(tag_id SEPARATOR ',') AS CHAR)的内容，可以清楚地看到每个项目计算了哪些标签。 - Ivar Bonsaksen

谢谢你 - 现在我可以更清楚地看到我的原始查询正在按照我计划的方式工作。 - tom

@Tom，很高兴听到我的解决方案提供了帮助...有时候，通过研究其他替代方案，可以让你看到SQL在幕后做了什么，然后删除用于分析的“调试”列（@Ivar Bonsaksen）。 - DRapp

@Ivar，DRapp，GROUP CONCAT在这里绝对是有用的。该列似乎非常适合向最终用户显示与所列项目匹配的标签，而无需对每个项目进行单独查询（当然，我需要单独查询以显示标签的名称）-您认为GROUP CONCAT在那里是可行的解决方案吗？ - tom