使用MySQL和约束条件搜索匹配的标签(相似性搜索) [英] Search for matching tags (Similarity Search) with MySQL and Constraints

问题描述

这是我的第一个话题.我很抱歉，如果这是重复的，但是我无法搜索正确的关键字.

this is my first thread. I apologize if this is a duplicate, but then i was not able to search for the right keywords.

我的问题: 网站上有带标签的项目.在物品的详细视图上 http://localhost/items/a.html 我想显示类似/相关项目的列表根据他们的标签.

My Problem: There are tagged items on a website. On a detailed view of a item e.g. http://localhost/items/a.html i want to show a list of similar/related items based on their tags.

• 商品A标签

• Item A-Tags

• 劳动
• 化学
• 分析
• Mehrweg

商品B标签

• 劳动
• 化学
• quantitativ
• Laktose

商品C标签

• quantitativ
• abscheiden
• Flaschenpfand

表的结构 :(如表:文章 字段:articleid，标题

Table: article Fields: articleid, title

表:标记 字段:tagid，tagtext

Table: tag Fields: tagid, tagtext

表:articletag 字段:tagid，articleid

Table: articletag Fields: tagid, articleid

当我现在访问以下入口点时( http://localhost/items/b.html )对于项目B，我想向用户打印一个列表，以该顺序显示相关项目:

When I now visit following entry point (http://localhost/items/b.html) for item B i want to print a list to the user which show the related items in this order:

• 项目A(因为有两个相同的标签)
• 项目C(因为有一个相同的标签)

当我在Google中搜索时，我发现了以下内容: 如何使用MySQL比较两个逗号分隔的字符串列表

As i searched in google i found something like this: How to compare two comma-separated string lists using MySQL

这和我的问题一样，但是我没有看到如何解决查询的任何提示.我发现的其他最多的帖子只是排除了项目a和c，因为它们与所有标签都不匹配(项目a与标签qantitativ和Laktose不匹配)

It looks just as my problem, but i didn't see any hints how to solve that for my query. The most other post that i found would just exclude item a and c because they dont match all of the tags (item a doesnt match for the tag qantitativ and Laktose)

您需要哪些进一步的信息? 顺便说一句:由于不会对此列进行匹配，因此我不会将所有标签作为附加属性添加到项目表.我只是不想总是在标签更改时更新此标签.这只是将数据库中已保存数据的大小加倍.

Which further information do you need? Btw: I will not add all tags as a extra property to the items table due to be able to perform a match against on this column. I just don't want to update this tags always when the tags changes. This is just doubling the size of saved data in the db.

推荐答案

SELECT  c.articleid, COUNT(*) AS ct
    FROM articletag AS b
    JOIN articletag AS c  ON c.tagid = b.tagid
                         AND c.articleid != b.articleid
    WHERE b.articleid = 123
    GROUP BY c.articleid
    ORDER BY ct DESC;

在搜索与123(示例中的项目B)相似"的内容时， 输出应该

When searching for what is 'similar' to 123 (Item-B in your example), the output should

Item-A, 2
Item-C, 1

这是articletag的完整扫描.因此，请注意我在 许多:许多映射的讨论中的技巧. .

This is a full scan of articletag. So, heed the tips in my discussion of many:many mapping .

如果执行查询后需要获取有关文章的信息，请将其用作派生"表；例如:

If you need to get information about the articles after performing the query, use it as a 'derived' table; for example:

SELECT articles.*
    FROM ( the above SELECT ) AS x
    JOIN articles USING(articleid)
    ORDER BY x.ct DESC;

(您可以从内部查询中删除ORDER BY，因为它会优先于外部ORDER BY而被忽略.)

(You can remove the ORDER BY from the inner query, since it will be ignored in preference to the outer ORDER BY.)

这篇关于使用MySQL和约束条件搜索匹配的标签(相似性搜索)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！