Multiple tags search query

Question

I working on a tag based search. I have three tables tag(id,name), tagXmedia(id,tag_id,media_id), and media(id,...). tagXmedia is the mapping table between the tag and media tables. This is a one to many relationship.

I could really use a little direction on how to create an "AND" type of search. For instance I need to be able to search for an entry in the media table that is associated with both the "home" and "hawaii" tags.

I have experimented with MySQL exists such as

SELECT
    tam.media_id
FROM
    tagXmedia tam
    LEFT JOIN tag ON tag.id = tam.tag_id
WHERE
    EXISTS (SELECT * FROM tag  WHERE tag.name = "home")
AND EXISTS (SELECT * FROM tag WHERE tag.name = "hawaii")

Any help on this would really be appreciated.

Answer 1

@kba's answer is correct but you can also do this with a JOIN which is probably more efficient.

SELECT media_id
  FROM tagXmedia
  LEFT JOIN tag ON tag_id = tag.id
  WHERE tag.name IN ('home', 'hawaii')
  GROUP BY media_id
  HAVING COUNT(tag_id) = 2;

I had a similar problem where I wanted to get not just the media_id but the actual object and wanted to pass in arbitrary comma separated lists of tags. Here's my complete solution using a stored procedure:

CREATE PROCEDURE FindByTag(IN _tags VARCHAR(256))
BEGIN
  DECLARE _length INT;

  -- Get the length of the list
  SET _tags =  TRIM(BOTH ',' FROM _tags);
  SET _length = LENGTH(_tags) - LENGTH(REPLACE(_tags, ',', '')) + 1;

  -- Find media
  SELECT * FROM media
    WHERE id IN (
      SELECT media_id FROM tagXmedia
        LEFT JOIN tag ON tag_id = tag.id
        WHERE FIND_IN_SET(tag.name, _tags)
        GROUP BY media_id
        HAVING COUNT(tag_id) = _length
    )
END

Answer 2

The following should work.

SELECT media_id
FROM tagXmedia
WHERE tag_id IN (SELECT id FROM tag WHERE name IN ('home','hawaii'))
GROUP BY media_id
HAVING COUNT(tag_id) = 2;

If you wish to have it match more than just two tags, you can easily add them. Just remember to change the 2 in the HAVING clause.

I assumed all the rows in tagXmedia are unique. In case they aren't, you will have to add DISTINCT to the COUNT part.