mysql many-to-many tag implementation + full tag listing

Question

based on http://stackoverflow.com/questions/1529073/how-to-implement-tagging-system-similar-to-so-in-php-mysql

ive made small changes, and have the following image tag implementation:

SELECT I.imageId, GROUP_CONCAT(DISTINCT T.tagName SEPARATOR ' '), COUNT(*)
FROM Images I
INNER JOIN ImageTagMap M ON I.imageId = M.imageId
INNER JOIN ImageTags T ON T.tagId = M.tagId
WHERE T.tagName LIKE '%new%' OR T.tagName LIKE '%yo%'
GROUP BY I.imageId
ORDER BY COUNT(*) DESC

currently, this successfully gets the imageId's of all of those images that fullfil the WHERE clause, and orders them according to the most hits (no. hits being generated by the GROUP BY clause). it also gets all the matched tag names and sticks them in a single string. nice.

what i would really like for the user, is to have all the tagNames for the relevant picture. currently the tagNames returned are only those that matched with the LIKE statements. e.g. if a picture is tagged "2010 new york" and i search (as above) "new yo" it will return imageId, "new york". but i want all the tag info for the matched image, i want imageId, "new york 2010".

a simple solution would be to stick the above query in a subquery and rerun the join with a new WHERE clause of imageId IN( above query as subquery ). although that seems silly (even if the optimiser might do magic with it), surely i dont need to rerun the exact same join twice? or do i?

as an aside, is there a better way to search tags than the bunch of LIKE's ive stuck in?

Answer 1

Add another JOIN to the ImageTags table used, and concat on that instead of the one you're testing tags against:

SELECT
    M.imageId,
    GROUP_CONCAT(DISTINCT Ta.tagName SEPARATOR ' ') AS tagNames,
    COUNT(DISTINCT Tc.tagName) AS relevance
FROM ImageTagMap M
JOIN ImageTags Tc ON Tc.tagId=M.tagId
JOIN ImageTags Ta ON Ta.tagId=M.tagId
WHERE Tc.tagName LIKE '%new%' OR Tc.tagName LIKE '%yo%'
GROUP BY M.imageId
ORDER BY relevance DESC

(I got rid of the join to Images as you didn't appear to be using it.)

is there a better way to search tags than the bunch of LIKE's ive stuck in?

If you really must arbitrary-substring-match against tags then no, there's no indexable way to do that. An equality test or a fixed-left-part LIKE (ie. LIKE 'new yo%') will be much more efficient given suitable indexes.

Answer 2

SELECT
M.imageId,
GROUP_CONCAT(DISTINCT T2.tagName SEPARATOR ' ') AS tagNames,
COUNT(DISTINCT T.tagName) AS relevance
FROM ImageTagMap M
JOIN ImageTags T ON T.tagId=M.tagId
JOIN ImageTagMap M2 ON M2.imageId=M.imageId
JOIN ImageTags T2 ON T2.tagId=M2.tagId
WHERE T.tagName LIKE '%new%' or T.tagName LIKE '%yo%'
GROUP BY M.imageId
ORDER BY relevance DESC

had to rejoin twice. any shorter ways are welcome.