Need a hand writing a MySQL query for related videos

Question

So firstly here is the relevant table structure:

TUBE_VIDEOS
------
id
video_name

TAGS
----
id
tag_name
tag_type [either set to "actor" or "tag"]

TUBE_VIDEO_TAGS
----
tube_video_id
tag_id

I had asked a question a while back about how to use this data to get related videos: here -- this solution basically took videos with the most common similar tags to decide which were most similar. Since then my database has been modified a bit to show what a tag is describing. So for example "Tom Cruise" is set to tag_type "actor" whereas "explosion" is set to tag_type "tag". What I'd like to do is adjust the query to weigh actors heavier than tags. So essentially we would say to MySQL: Give me a list of video ID's with the most matching actors, in the event of tie use the most matching tags next.

Answer 1

You can do that with just a lot of joins. The following query starts with the current video tv. It looks for all tags belonging to that video tvt. Then it looks for all other video's sharing one or more tags rtvt. For the related video's, it looks up the tag details rt and the video details rtv.

select      rtv.video_name
,           sum(case when rt.tag_type = 'actor' then 1 else 0 end) ActorCount
,           sum(case when rt.tag_type = 'tag' then 1 else 0 end) TagCount
from        tube_videos tv
left join   tube_video_tags tvt
on          tvt.tube_video_id = tv.id
left join   tube_video_tags rtvt
on          rtvt.tag_id = tvt.tag_id
and         rtvt.tube_video_id <> tv.id
left join   tags rt
on          rt.id = rtvt.tag_id
left join   tube_videos rtv
on          rtv.id = rtvt.tube_video_id
where       tv.id = <YourMovieId>
group by    rtv.id, rtv.video_name
order by    ActorCount desc, TagCount desc

After all the joins, you can count the various badge types and order by them.