I have a table of items, table of tags and a table which connects these two (many-to-many).
Fields of each table:
items:
id_item INT
tags:
id_tag INT
item_tag:
id_item INT
id_tag INT
Now each item can have one or more tags. I want to perform a tag based search which will select only these items which contain the specified tags. So if I have item with tags FLASH (id=1), PHP(id=2), MYSQL(id=3), RAGE(id=4)
I can find it when searching for:
FLASH, [1] (This is an array of tag ID's)
FLASH + PHP, [1,2]
MYSQL + PHP + RAGE, [3,2,4]
PHP + FLASH + MYSQL + RAGE [2,1,3,4]
but it should be found when I am looking for, eg:
ACTIONSCRIPT, [8]
PHP + ACTIONSCRIPT, [2,8]
I thought about using simple LEFT JOIN with IN(...) but then I realized it wouldn't work... Any hints? Can it be done just via Mysql query without manually parsing the data in PHP?
UPDATE:
Taking into consideration answer by Brad Christie, here is an example query with the question being asked:
SELECT t1.some_field, GROUP_CONCAT(t2.id_tag ORDER BY t2.id_tag) AS tagList FROM items t1
LEFT JOIN item_tag t2 ON t1.id_item = t2.id_item
WHERE HERE_LIES_THE_PROBLEM
Now, where we have HERE_LIES_THE_PROBLEM should be a function which checks if all elements presented in X are in tagList. So if tagList is "1,开发者_运维知识库2,3,4" and X is "1,2" it should work, but if X is (1,2,5) it shouldn't. How to do this part?
Does this previous SO question help out at all?
EDIT
Also, what's wrong with concatenating the query in PHP and using AND
to include all search terms?
EDITV2
Excuse the fact that I'm doing this on the phone. As I said, ill post a better solution when I have an opportunity. But something like the following, though long, should work:
SELECT *
FROM items
WHERE id_item IN (
SELECT item_tag.id_item
FROM item_tag
INNER JOIN tags
ON tags.id_tag = item_tag.id_tag
WHERE tags.tag_name='PHP'
)
AND id_item IN (
--- same as above, just different tag
)
Only problem is it's long and inefficient. Like I said, when I'm not on a phone ill make improvements.
精彩评论