Search multiple tags

gewk · #1 (**permalink**) 04-27-08, 07:18

I tried the search function, but didn't find what I was looking for. Mainly because I'm quite unsure of what to really search for.

I have a database, and I want to be able to search for tags associated with the movies I have in my database.

Currently I have;
- one table that contains the movies (id and title)
- another table that contains the tags (id and title)
- and finally I have a third table that contains the connections between the movies and the tags, the fields are as follows...

id
movie_id - the same 'id' as the 'id' in the movie table
tag_id - the same 'id' as the 'id' in the tags table

I want to be able to search multiple tags in the tag table and the results will show the related movies from the movie table.

Anyone that could point me in the right direction of how to do this?

r937 · #2 (**permalink**) 04-27-08, 08:29

Code:

select movie_id
  from movietags
 where tag_id in ( 1, 9, 3, 7 )
group
    by movie_id
having count(*) = 4

basically, select the relationship rows for all the tags you're interested in, group by the movie, and keep only those movies which have the full number of tags

gewk · #3 (**permalink**) 04-27-08, 09:34

Thanks for your reply.

I not sure if this i the right way to do this... this is more of pseudo code of what I want to achieve...

Code:

SELECT title, id
  FROM tags t
  WHERE title LIKE 'string1%' OR title LIKE 'string2%'
IN (
  SELECT *
   FROM movie_tags
   WHERE tag_id
    IN ( t.id, t.id, t.id )
    GROUP
    BY movie_id
)

I basically want to wild card match multiple strings found in the tags table, and get the movies related to the tags... is this the wrong approach?

r937 · #4 (**permalink**) 04-27-08, 09:38

it looks like you are wild card matching strings found in the movie title instead of the tags

could you perhaps rephrase what you're trying to do?

gewk · #5 (**permalink**) 04-27-08, 09:52

Ok, I'll try to rephrase.

So lets say I have 5 tags in the tags table.

Code:

id, title
1, ball
2, car
3, cat
4, dog
5, doll

and in the movies table I have 2 movies.

Code:

id, title
1, movie about a dog
2, movie about a cat

in the movie_tags table I connect the tags to different movies...

Code:

id, movie_id, tag_id
1, 1, 4
2, 1, 3
3, 1, 5
4, 2, 3
5, 2, 2
6, 2, 1

When I begin to search for "ca" it should wild card match all the movies tagged with car and cat.

Do you understand what I'm trying to do?

gewk · #6 (**permalink**) 04-27-08, 16:12

To maybe simplify what I'm trying to achieve, I split the SQL query in different parts. I practically just need to merge it somehow.

Code:

SELECT title, id FROM tags t
WHERE title LIKE 's%'

This will get all the tags starting with the letter 's'. Now I would like to use the id associated with the tag...

Code:

SELECT * FROM movie_tags
WHERE tag_id IN
(SELECT id FROM movie
WHERE id IN ('7', '3'))

This will get all movies tagged with id 7 and 3 (the id is linked to the id in the tags table). So the 7 and 3 have to be changed to the id's I got from the previous query.

I think this is what I need, so how do I merge this into one query?

r937 · #7 (**permalink**) 04-27-08, 18:30

Code:

SELECT movies.*
  FROM movies
INNER
  JOIN ( SELECT movietags.movie_id
           FROM tags
         INNER
           JOIN movietags
             ON movietags.tag_id = tags.id
          WHERE tags.title LIKE 'string1%' 
             OR tags.title LIKE 'string2%'
         GROUP
             BY movietags.movie_id
         HAVING COUNT(*) = 
                ( SELECT COUNT(*)
                    FROM tags
                   WHERE title LIKE 'string1%' 
                      OR title LIKE 'string2%' )
       ) as t
    ON t.movie_id = movies.id

gvee · #8 (**permalink**) 04-27-08, 19:19

Quote:

Originally Posted by gewk

Code:

id, movie_id, tag_id
1, 1, 4
2, 1, 3
3, 1, 5
4, 2, 3
5, 2, 2
6, 2, 1

A bit off topic but still...

You don't need the "id" field here; a compound key of movie_id and tag_id is more than sufficient

r937 · #9 (**permalink**) 04-27-08, 20:49

more than????

i'd like to see whatever you think might be sufficient that includes less than a compound key of movie_id and tag_id

gvee · #10 (**permalink**) 04-28-08, 03:41

True enough, should have have said with more conviction!

I was going to joke about how you could use a comma delimited value string or a table with multiple tag fields, but I won't. The correct design is the compound key and that's that

...better?

gewk · #11 (**permalink**) 04-28-08, 05:28

Oh, thank you guys! It's working like intended

gewk · #12 (**permalink**) 04-29-08, 10:08

Sorry for bothering again...

I've been trying to use the same string input for searching for the 'title' field in the movies table as well. But I haven't managed to get it to work.

Something like...

Code:

         SELECT movietags.movie_id
           FROM movies, tags
         INNER
           JOIN movietags
             ON movietags.tag_id = tags.id
         WHERE tags.title LIKE 'string1%' 
             OR tags.title LIKE 'string2%'
             OR movies.title LIKE 'string1%'
             OR movies.title LIKE 'string2%'

Seems like the movies table and/or fields aren't recognized in the the sub query...

Anyone care to help me out?

r937 · #13 (**permalink**) 04-29-08, 15:47

there is no subquery

and you are mixing an implicit join (FROM movies, tags) with an explicit JOIN

and what happened to the HAVING clause i gave you?

perhaps you had better start over and explain what your latest query needs to do

gewk · #14 (**permalink**) 04-30-08, 09:59

Sorry for being confusing... I'm quite a noob when it comes to SQL.

The code I pasted was just a snippet of the full code.

And it didn't work for me when using the HAVING clause, didn't get any results. Executing just the sub query of the HAVING clause gives me a count of 26. I'm not sure if that helps (I got 11 movies in the movies table, and about 120 tags).

So what I want is to use the code you gave me, and add the possibility so that I can search for title's in the movie table as well.

Internet Services

Web Development

Digital Marketing

Consumer Tech