php - 在 MySQL 中对具有权重的匹配标签的项目进行排序

Question

我很难在这里找到正确的查询。当您想使用标签查找相关项目时，在 MySQL 中，您可以使用“公共(public)标签计数”来查找最相似的项目。

假设我的架构如下所示:

• 标签(tag_id，标题)
• 文章(article_id, some_text)
• articles_tags(tag_id, article_id)

然后您可以获取项目并使用“article 2”等常用标签对它们进行排序，例如，如下所示:

SELECT at1.article_id, Count(at1.tag_id) AS common_tag_count
FROM articles_tags AS at1 INNER JOIN articles_tags AS at2 ON at1.tag_id = at2.tag_id
WHERE at2.article_id = 2
GROUP BY at1.article_id
HAVING at1.article_id != 2
ORDER BY common_tag_count DESC;

但在我的情况下，有一个挑战。我想找到基于多篇文章而不是一篇的类似文章(类似于“阅读历史”)。如果 2 篇文章都有标签 X，我希望标签 X 变得更重要。

所以基本上，我正在寻找一种方法来进行 common_tag_count 匹配，但具有标签权重。任何人都知道如何做到这一点？

Answer 1

要获取多篇文章使用的标签，包括它们的使用频率，您可以使用此查询:

SELECT tag_id, COUNT(article_id) as tag_weight
FROM articles_tags
WHERE article_id IN ( /* Read articles */ 1, 2 )
GROUP BY tag_id;

要根据该选择获取类似的文章，您必须在类似的连接中使用上述查询，因为您已经拥有:

SELECT articles.article_id, articles.title, SUM(tag_weights.tag_weight)
FROM articles
JOIN articles_tags ON articles_tags.article_id = articles.article_id
JOIN (
    SELECT tag_id, COUNT(article_id) as tag_weight
    FROM articles_tags
    WHERE article_id IN ( /* Read articles */ 1, 2 )
    GROUP BY tag_id
) AS tag_weights ON articles_tags.tag_id = tag_weights.tag_id
WHERE articles.article_id NOT IN ( /* Read articles */ 1, 2 )
GROUP BY articles.article_id
ORDER BY SUM(tag_weights.tag_weight) DESC;

我们在这里在可以访问标签权重的子查询上添加一个额外的 JOIN。使用 ORDER BY，您首先会获得“最佳”结果。

演示:http://www.sqlfiddle.com/#!2/b35432/2/1(阅读文章 1 和 2，赋予标签 1 权重 2，标签 2 权重 1)。