查找共享最多标签的类似对象 [英] Find similar objects that share the most tags

问题描述

我有两个表 objects 和标签，每个 object 有一个id，每个标签有 id a 名称和 parent （对象的id）。

I have two tables objects and tags, each object having an id, and each tag having an id a name and a parent (the id of the object).

我想做的是选择一个对象然后找到其他对象按共同的标签数量排序，例如返回5个最相似的对象。

What I want to do is to choose an object then find other objects ordered by the amount of tags in common , e.g. to return the 5 most similar objects.

编辑：

SELECT parent,COUNT(*) as count
FROM `tag` 
WHERE tag="house" OR tag="dog" OR tag="cat" 
GROUP BY parent 
ORDER BY count DESC

想要，我可以找到obejcts标签房子，狗，猫与另一个查询之前这一个。

This one does what I want, and I could find the obejcts tags "house,dog,cat" with another query before this one. Any idea how I could combine these two queries?

推荐答案

给定一个对象，你可以找到如下的标签：

Given one object, you can find its tags like this:

 SELECT t1.id
 FROM tags t1
 where t1.parent_id = ?

在此基础上，您需要获取这个标签列表并找到共享它们的其他parent_ids。 / p>

Building on that, you want to take that list of tags and find other parent_ids that share them.

 SELECT parent_id, count(*)
 FROM tags t2
 WHERE EXISTS (
     SELECT t1.id
     FROM tags t1
     WHERE t1.parent_id = ?
     AND t1.id = t2.id
 )
 GROUP BY parent_id

这将告诉你这些其他parent_ids分享的标签数量。

That will give you a count of how many tags those other parent_ids share.

如果您想先找到最相似的行，可以 ORDER BY count（*）desc 。

You can ORDER BY count(*) desc if you'd like to find the "most similar" rows first.

希望有帮助。

这篇关于查找共享最多标签的类似对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！