按匹配的标签数量对具有匹配标签的项目进行排序

2022-01-18 00:00:00 tags search mysql sphinx

我正在尝试弄清楚如何根据匹配的标签数量对带有匹配标签的商品进行排序.

I'm trying to figure out how to order items with matching tags by the number of tags that match.

假设您有三个 MySQL 表:

Let's say you have three MySQL tables:

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

现在假设您有四篇文章，其中:

Now let's say you have four articles where:

article_id = 1 有标签幽默"、搞笑"和搞笑".

article_id = 1 has tags "humor," "funny," and "hilarious."

article_id = 2 有标签有趣"、愚蠢"和愚蠢".

article_id = 2 has tags "funny," "silly," and "goofy."

article_id = 3 有标签有趣"、愚蠢"和愚蠢".

article_id = 3 has tags "funny," "silly," and "goofy."

article_id = 4 带有完全严重"的标签.

article_id = 4 has the tag "completely serious."

您需要通过至少一个匹配的标签找到与article_id = 2相关的所有文章，并按照匹配最佳的顺序返回结果.换句话说，article_id = 3 应该先出现，article_id = 1 第二，article_id = 4 根本不应该出现.

You need to find all articles related to article_id = 2 by at least one matching tag, and return the results in order of the best matches. In other words, article_id = 3 should come first, with article_id = 1 second, and article_id = 4 should not show up at all.

这是在 SQL 查询中可行还是单独使用，还是更适合 Sphinx 之类的东西?如果是前者，应该做什么样的查询，应该为最高性能的结果创建什么样的索引?如果是后者，请展开.

Is this something that's doable in SQL queries or alone, or is this better suited for something like Sphinx? If the former, what kind of query should be done, and what sort of indexes should be created for the most performant results? If the latter, please do expand.

推荐答案

试试这样的:

select article_id, count(tag_id) as common_tag_count
from articles_tags 
group by tag_id
where tag_id in (
    select tag_id from articles_tags where article_id = 2
) and article_id != 2
order by common_tag_count desc;

MySQL 的语法可能需要稍作调整.

Syntax may need a little tweaking for MySQL.

或者这个真正有效的:;-)

or this one that actually works: ;-)

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 Count(at1.tag_id) DESC;

相关文章