Page 1 of 1

Best way to select items based on tags

Posted: Thu Apr 18, 2013 10:11 pm
by twinedev
Something similar to this was asked on another site, and it got me wondering the best solution, would it be strictly SQL that can do it, or have to use code as well. (other site: http://www.reddit.com/r/PHPhelp/comment ... nto_image/)

Basically I'm taking that challenge to be that you have a table of uploaded files, a table of tags that apply to the files, and a table that links file_id to tag_id to show which tags should be associated with the file.

Now, getting files based upon matching a single tag is quite easy, but the question over there was how to get the files that match MORE than one tag.

Now I'm pretty decent with basic joins and what not, but advanced joins, groupings and such, I struggle with unless I play with them, so was wondering if it can efficiently be done strictly with SQL, or if it is better to to retrieve matching items then look for matching all three in PHP (or possible create temp table then select from it)

From a pure SQL standpoint, my thought would be get all files, joined to tags (well tag_id), and then possible do a grouping off of the file ID then a where the count of the group = 3 (the number of items you are looking for)... But like I said, working with GROUP BY and using HAVING... there i get a little lost.

So while I'm sitting here playing with it on my end, thought I would throw it out there for others who may have experience off of the top of their head, or just general discussion of best ways.

-Greg

Re: Best way to select items based on tags

Posted: Fri Apr 19, 2013 3:15 am
by requinix
That's exactly how I do it: with SQL, start with the image table, join in the tags table, filter it down to just the tags you want to look at, group by the image, and then only keep the images that have a count equaling the number of tags.

Code: Select all

SELECT i.*
FROM images i
JOIN images_tags it ON i.imageID = it.imageID
JOIN tags t ON it.tagID = t.tagID
WHERE t.tagName IN ("White", "Nissan", "Turbo")
GROUP BY i.imageID
HAVING COUNT(*) = 3