通过匹配的标签数量订购带有匹配标签的项目 [英] Ordering items with matching tags by number of tags that match

问题描述

我正在尝试根据匹配标签的数量来确定如何对具有匹配标签的商品进行排序.

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:

• tags(tag_id, title)
• articles(article_id, some_text)
• articles_tags(tag_id, article_id)

• tags(tag_id, title)
• articles(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;

这篇关于通过匹配的标签数量订购带有匹配标签的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！