开发者

Mysql query, item contains all specified tags

开发者 https://www.devze.com 2023-01-31 01:38 出处:网络
I have a table of items, table of tags and a table which connects these two (many-to-many). Fields of each table:

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消