如何基于标签获取相似的对象 [英] how to get similar objects based on tags

问题描述

我有三个这样的表:

• 电影:ID，名称

• movie: id, name

标签:id，名称，值

已标记:id，movie(FK)，tag(FK)

tagged: id, movie(FK), tag(FK)

因此，每部电影都有自己的标签集.我需要的是基于标签集检索相似的电影.我想说10部按匹配标签计数排序的电影.

Thus, each movie has it's own set of tags. What I need is to retrieve similar movies based on tag set. I want to get say 10 movies sorted by count of matched tags.

如果我创建如下所示的视图，它将使MySQL消失. 标记"表和标记"表中都有超过30k条记录.

If I create view like below, it makes MySQL go away. There are 30k+ records in both 'tag' and 'tagged' tables.

create view relatedtags as 

select
    entityLeft.id as id,
    entityRight.id as rightId,
    count(rightTagged.id) as matches

from
    entity as entityLeft join tagged as leftTagged on leftTagged.entity = entityLeft.id, 
    entity as entityRight join tagged as rightTagged on rightTagged.entity = entityRight.id

where leftTagged.tag = rightTagged.tag
and entityLeft.id != entityRight.id
group by entityLeft.id, entityRight.id

推荐答案

这将返回所有电影的列表，这些电影与给定的<current_movie_id>至少共享1个标签，并通过减少共同标签的数量进行排序

This will return a list of all movies that share at least 1 tag with the given <current_movie_id> ordered by decreasing number of tags in common

SELECT movie.*, count(DISTINCT similar.tag) as shared_tags FROM movie INNER JOIN 
    ( tagged AS this_movie INNER JOIN tagged AS similar USING (tag) )
    ON similar.movie = movie.id
WHERE this_movie.movie=<current_movie_id>
AND   movie.id != this_movie.movie
GROUP BY movie.id
ORDER BY shared_tags DESC

希望为您提供一些帮助

这篇关于如何基于标签获取相似的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！