Выбор из таблицы «многие ко многим»

Я хочу получить post_ID и миниатюру сообщений, которые имеют несколько тегов. Мои таблицы следующие:

table: post
-------------------
post_ID | thumbnail
1       | 1.jpg
2       | 2.jpg
3       | 3.jpg
4       | 4.jpg
5       | 5.jpg

table: tags
-----------
tag_ID | tag
1      | red
2      | yellow
3      | orange
4      | blue
5      | pink

table: post_tags
----------------
post_ID | tag_ID
1       | 1
1       | 2
1       | 4
1       | 5
2       | 2
2       | 3
3       | 4
3       | 1
3       | 2
4       | 5
4       | 4
5       | 1
5       | 3
5       | 5

В настоящее время я использую что-то подобное, но это не работает: я сначала получаю все теги сообщений в массиве, а затем сравниваю их, чтобы найти, есть ли в сообщении все 3 упомянутых тега.

 SELECT post_ID FROM post_tags 
 WHERE tag_ID IN ($thisTags[0], $thisTags[1], $thisTags[2], $thisTags[3])

Мне нужно присоединиться или группу или что-то? Я новичок в SQL и PHP. Большое спасибо.


php sql database select relational-division
person Duane    schedule 17.07.2013    source источник
comment
Когда вы говорите «не работает», что вы видите? И как вы выполняете свой php-код? (фрагмент кода?)   -  person Nathaniel Ford    schedule 18.07.2013
comment
какой результат вы хотите получить?   -  person echo_Me    schedule 18.07.2013

Ответы (3)


arrow_upward
0
arrow_downward

Если у вас есть ограничение UNIQUE для post_tags(post_ID,tag_ID) и вы можете убедиться, что элементы thisTags уникальны, вы можете сделать что-то вроде этого:

SELECT post_ID FROM post_tags 
 WHERE tag_ID IN ($thisTags[0], $thisTags[1], $thisTags[2], $thisTags[3])
 GROUP BY post_ID
HAVING COUNT(1) = 4

Этот подход ТРЕБУЕТ, чтобы элементы в $thisTags были уникальными. Вы можете легко обойти проблему уникальности в таблице post_tags (но приведенный выше запрос также требует, чтобы она была уникальной).

Существуют другие операторы SQL, другие подходы, которые могут дать эквивалентный результат, но приведенный выше запрос является наиболее кратким. (Некоторые из других подходов заключаются в использовании предикатов EXISTS или использовании операций JOIN для наборов post_ID, возвращаемых для каждого тега.)

person spencer7593    schedule 17.07.2013

arrow_upward
0
arrow_downward

Это пример подзапроса "набор внутри наборов". Я рекомендую использовать group by и having для максимальной гибкости. Идея состоит в том, чтобы переместить логику в предложение having. В вашем случае это выглядит так:

SELECT post_ID
FROM post_tags 
group by post_id
having sum(tag_id = $thisTags[0]) > 0 and
       sum(tag_id = $thisTags[1]) > 0 and
       sum(tag_id = $thisTags[2]) > 0 and
       sum(tag_id = $thisTags[3]) > 0;

Если вам нужны только эти теги и никакие другие:

SELECT post_ID
FROM post_tags 
group by post_id
having sum(tag_id = $thisTags[0]) > 0 and
       sum(tag_id = $thisTags[1]) > 0 and
       sum(tag_id = $thisTags[2]) > 0 and
       sum(tag_id = $thisTags[3]) > 0 and
       count(distinct tag_id) = 4;

Если вам нужны хотя бы три из этих четырех тегов:

SELECT post_ID
FROM post_tags 
group by post_id
having (max(tag_id = $thisTags[0]) + and
        max(tag_id = $thisTags[1]) + and
        max(tag_id = $thisTags[2]) + and
        max(tag_id = $thisTags[3])
       ) >= 3;
person Gordon Linoff    schedule 17.07.2013
comment
Это самый гибкий подход. Но мы могли бы захотеть отметить, что эта форма запроса, скорее всего, приведет к полному сканированию, а не к сканированию диапазона, что может повлиять на производительность, если мы ищем небольшое количество тегов из огромного домена тегов. - person spencer7593; 18.07.2013
comment
Это сработало! Большое спасибо! :) - person Duane; 18.07.2013

arrow_upward
0
arrow_downward

Вы ищете что-то подобное?

SELECT post_ID 
  FROM post_tags 
 WHERE tag_ID IN (1, 2) -- IN($thisTags[0], $thisTags[1], $thisTags[2], $thisTags[3])
 GROUP BY post_ID
 HAVING COUNT(DISTINCT tag_ID) = 2 -- COUNT(DISTINCT tag_ID) = 4

Вот демонстрация SQLFiddle.

HAVING COUNT(DISTINCT tag_ID) = 2 гарантирует, что набор результатов содержит только те post_ID, которые имеют оба значения tag_ID 1 и 2.

person peterm    schedule 17.07.2013
comment
Спасибо, это сработало отлично. Я добавил к нему лимит и ордер, и он работал идеально, как я и хотел. Спасибо. - person Duane; 18.07.2013