Справка по запросам HABTM

У меня есть отношение HABTM между «статьями» и «тегами».

Проблема: я ищу только статьи с ОБОИМ тегом "спорт" и "на открытом воздухе", но не статьи только с одним из этих тегов.

Я пробовал это:

SELECT DISTINCT article.id, article.name FROM articles
inner JOIN tags ON (tags.name IN ('outdoors', 'sports')
inner JOIN articles_tags ON articles_tags.article_id = article.id AND articles_tags.tag_id = tags.id

... но мне достаются статьи, которые относятся только к спорту, только к открытому воздуху И оба вида спорта + к открытому воздуху

Вопрос, какой запрос следует использовать? (я использую MySQL)


mysql sql has-and-belongs-to-many sql-match-all
person cardflopper    schedule 09.12.2009    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Есть два распространенных решения.

  • Первое решение использует GROUP BY для подсчета тегов в статье, которые соответствуют «на открытом воздухе» или «спорту», ​​а затем возвращает только те группы, которые имеют оба тега.

    SELECT a.id, a.name
FROM articles AS a
INNER JOIN articles_tags AS at ON (a.id = at.article_id)
INNER JOIN tags AS t ON (t.id = at.tag_id)
WHERE t.name IN ('outdoors', 'sports')
GROUP BY a.id
HAVING COUNT(DISTINCT t.name) = 2;

    Некоторым людям это решение кажется более читаемым, а добавление значений более простым. Но запросы GROUP BY в MySQL имеют тенденцию создавать временную таблицу, что снижает производительность.

  • В другом решении используется JOIN для каждого отдельного тега. Используя внутренние соединения, запрос естественным образом ограничивается статьями, которые соответствуют всем указанным вами тегам.

    SELECT a.id, a.name
FROM articles AS a
INNER JOIN articles_tags AS at1 ON (a.id = at1.article_id)
INNER JOIN tags AS t1 ON (t1.id = at1.tag_id AND t1.name = 'outdoors')
INNER JOIN articles_tags AS at2 ON (a.id = at2.article_id)
INNER JOIN tags AS t2 ON (t2.id = at2.article_id AND t2.name = 'sports');

    Предполагая, что tags.name и articles_tags.(article_id,tag_id) имеют ограничения UNIQUE, вам не нужен модификатор запроса DISTINCT.

    Этот тип запроса, как правило, лучше оптимизируется для MySQL, чем решение GROUP BY, при условии, что вы определили соответствующие индексы.

Что касается вашего дополнительного вопроса в комментарии, я бы сделал что-то вроде этого:

SELECT a.id, a.name, GROUP_CONCAT(t3.tag) AS all_tags
FROM articles AS a
INNER JOIN articles_tags AS at1 ON (a.id = at1.article_id)
INNER JOIN tags AS t1 ON (t1.id = at1.tag_id AND t1.name = 'outdoors')
INNER JOIN articles_tags AS at2 ON (a.id = at2.article_id)
INNER JOIN tags AS t2 ON (t2.id = at2.article_id AND t2.name = 'sports');
INNER JOIN articles_tags AS at3 ON (a.id = at3.article_id)
INNER JOIN tags AS t3 ON (t3.id = at3.article_id);
GROUP BY a.id;

Это по-прежнему находит только статьи, которые имеют оба тега "на открытом воздухе" и "спорт", но затем присоединяется к этим статьям всем своим тегам.

Это вернет несколько строк для каждой статьи (по одной для каждого тега), поэтому мы затем используем GROUP BY, чтобы снова сократить до одной строки для каждой статьи. GROUP_CONCAT() возвращает запятую -разделенный список значений в соответствующей группе.

person Bill Karwin    schedule 09.12.2009
comment
спасибо за объяснение... Кроме того, возможно ли в этом запросе вернуть все теги, связанные с соответствующими статьями? - person cardflopper; 12.12.2009

arrow_upward
1
arrow_downward

Попробуй это:

SELECT a1.id, a1.name FROM articles a1
    JOIN tags t1 ON t1.name ='outdoors'
    JOIN articles_tags at1 ON at1.article_id = a1.id AND at1.tag_id = t1.id
    JOIN tags t2 ON t2.name = 'sports'
    JOIN articles_tags at2 ON at2.article_id = a1.id AND at2.tag_id = t2.id
person jamesaharvey    schedule 09.12.2009