CODEWITHSUNDEEP
phpmysqlsearch
Nate
Nate

Reputation: 28354

How to implement search for a tagging system?

Many types of systems use tags to organize content, including blogs and StackOverflow. Let's say I'm making a movies website and want users to be able to tag movies, as well as search for movies using combinations of tags.

Here's a sample database structure:

movies:
id, name

movie_tag_options (used to provide users with list of available tag options):
id, name

movie_tags:
id, m_id, mto_id

Let's say there's a movie called "Transformers 9 - Explosions" and it has the tags "action", "PG-13", and "terrible movie". I want users to be able to find this movie by performing a search for "action,PG-13,terrible movie".

I will explode the string of tags in PHP to get an array of tags:

action
PG-13
terrible movie

So my question is, how do I use the tag names to find movies with these tags? The movies can have other tags as well, but should only be returned if they have all of the tags in the search.

The only solution I can think of is to denormalize the tag name and to store it in movie_tags as well as movie_tag_options (i.e. adding a duplicate name column to movie_tags), then constructing a query in PHP that generates JOIN statements for each tag. Something like this:

SELECT id, name FROM movies
JOIN movie_tags mt0 ON mt0.name = "action"
JOIN movie_tags mt1 ON mt1.name = "PG-13"
JOIN movie_tags mt2 ON mt2.name = "terrible movie"

The JOIN lines would be generated via PHP and inserted into the query.

The downside to this approach is storing the tag names in two tables instead of one.

Is there a better way?

Upvotes: 0

Views: 119

Answers (1)

Fabricator
Fabricator

Reputation: 12772

or you can use 

select a.name, count(c.id) c
from movies a
join movie_tags b on a.id = b.m_id
join movie_tag_options c on b.mto_id = c.id
where c.name in ('action', 'PG-13', 'terrible movie')
group by a.id
having c = 3;

Upvotes: 2

Related Questions