Блог

Я просматривал всю сеть и просил людей о руководстве, но, похоже, никто не знает правильного (относительно быстрого) решения проблемы:

У меня есть три таблицы, классическое решение «многие ко многим»:

• entries : идентификатор (int), заголовок (varchar[255]), содержимое (текст)
• tags : id (int), name (varchar[255]), slug (varchar[255])
• entries_tags : id (int), entry_id (int), tag_id (int)

Пока ничего необычного. Теперь предположим, что у меня есть тестовые данные в тегах (я не использую пули, поскольку они не важны):

 ID | name
1. | one 
2. | two 
3. | three
4. | four
5. | five 
  

У меня также есть три записи:

 ID | title
1. | Something
2. | Blah blah blah
3. | Yay!
  

И отношения:

 ID | entry_id | tag_id
1. | 1        | 1
2. | 1        | 2
3. | 2        | 1
4. | 2        | 3
5. | 3        | 1
6. | 3        | 2
7. | 3        | 3
8. | 4        | 1
9. | 4        | 4
  

Хорошо, у нас есть наши тестовые данные. Я хочу знать, как получить все записи, которые имеют тег Один, но не имеют тега Три (это были бы записи 1 и 4).

Я знаю, как это сделать с помощью подзапроса, проблема в том, что это занимает много времени (при 100 тыс. записей это заняло около 10-15 секунд). Есть ли какой-нибудь способ сделать это с помощью объединений? Или я что-то упускаю?

редактировать Полагаю, я должен был упомянуть, что мне нужно решение, которое работает с наборами данных, а не с отдельными тегами, поэтому замените «Один» в моем вопросе на «Один», «Два» и «Два» на «Три», «Четыре»

edit2 Предоставленный ответ правильный, но он слишком медленный, чтобы его можно было использовать практически. Я думаю, единственный способ заставить его работать — использовать стороннюю поисковую систему, такую как Lucene или ElasticSearch.

Следующий скрипт выбирает записи, которые имеют теги One Two и не имеют тегов Three и Four :

 SELECT DISTINCT
  et.entry_id
FROM entries_tags et
  INNER JOIN tags t1 ON et.tag_id = t1.id AND t1.name IN ('One', 'Two')
  LEFT JOIN  tags t2 ON et.tag_id = t2.id AND t2.name IN ('Three', 'Four')
WHERE t2.id IS NULL
  

Альтернативное решение: INNER JOIN заменяется на WHERE EXISTS , что позволяет нам избавиться от (довольно дорогого) DISTINCT :

 SELECT
  et.entry_id
FROM entries_tags et
  LEFT JOIN  tags t2 ON et.tag_id = t2.id AND t2.name IN ('Three', 'Four')
WHERE t2.id IS NULL
  AND EXISTS (
    SELECT *
    FROM tags t1
    WHERE t1.id = et.tag_id
      AND t1.name IN ('One', 'Two')
  )
  

Это должно делать то, что вы хотите.

(Это может быть или не быть быстрее, чем решение с вложенным запросом, я предлагаю вам сравнить планы запросов)

 SELECT DISTINCT e.* 
FROM tags t1 
INNER JOIN entries_tags et1 ON t1.id=et1.tag_id 
INNER JOIN entries e ON e.entry_id=et1.entry_id 
INNER JOIN tags t2 on t2.name='three'
INNER JOIN tags t3 on t3.name='four'
LEFT JOIN entries_tags et2 ON (et1.entryid=et2.entryid AND t2.id = et2.tag_id ) 
       OR (et1.entryid=et2.entryid AND t3.id = et2.tag_id )
WHERE t1.name IN ('one','two') AND et2.name is NULL 
  

Присоединив СЛЕВА к таблице et2 entries_tags (данные, которые вам не нужны), вы можете выбрать только те записи, в которых et2.name ИМЕЕТ значение NULL (если запись et2 не существует).

Вы упомянули попытку выполнения подзапроса. Это то, что вы пробовали?

 SELECT entries.id, entries.content
FROM entries
  LEFT JOIN entries_tags ON entries.id=entries_tags.entries_id
  LEFT JOIN tags ON entries_tags.tag_id=tags.id
WHERE tag.id=XX
  and entries.id NOT IN (
    SELECT entries.id
    FROM entries
      LEFT JOIN entries_tags ON entries.id=entries_tags.entries_id
      LEFT JOIN tags ON entries_tags.tag_id=tags.id
    WHERE tag.id=YY
  )
  

(Где XX — это тег, который вам нужен, а YY — тег, который вам не нужен)

С индексами в полях идентификаторов это не должно быть так медленно, как вы говорите. Это будет зависеть от набора данных, но с индексами все должно быть в порядке (и без сравнения строк).