how to get similar objects based on tags

Question

I have three tables like these:

• movie: id, name

• tag: id, name, value

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

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.

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

Answer 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

hope that gives you something to work with