Best way to select items based on tags

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Best way to select items based on tags

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Best way to select items based on tags

Post 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
Post Reply