Как составить такой запрос с поиском по тегам?

Question

[lolrofl01]

Добрый день.
Пишу фильтр постов, и столкнулся с большой проблемой. Фильтровать посты можно по юзеру, дате, статусу, тегам. С последним и возникла проблема. У меня есть промежуточная таблица postTags. У нее следующие колонки:
id
post_id
tag_id

Фильтрую пока вот так:

SELECT p.*, u.name FROM posts p
LEFT JOIN users u ON u.id = p.user_id
LEFT JOIN postTags pt ON pt.post_id = p.id
WHERE p.user_id = 4
AND pt.tag_id IN (3,5,7)

Разумеется, мне показываются посты, которые присутствуют хотя бы в одном из тегов с айди 3, 5, 7.
Но необходимо чтобы пост ОБЯЗАТЕЛЬНО был с тегами, у которых айди 3, 5, 7. Если хотя бы одного из этих тегов нет - значит пост не подходит под условия.

Перекопал документацию, но что-то никаких аналогов IN я не нашел...видел какой-то пример с having count, но что-то совсем его не понял...

Answer 1

[alexalexes]

Решение 1 - с модификацией запроса под определенный набор параметров.
Сколько одновременных тегов должно присутствовать на входе, столько и будет join-ов таблицы тегов.

SELECT p.*, u.name FROM posts p
JOIN users u ON u.id = p.user_id
JOIN postTags pt1 ON pt1.post_id = p.id and pt1.tag_id = 3
JOIN postTags pt2 ON pt2.post_id = p.id and pt2.tag_id = 5
JOIN postTags pt3 ON pt3.post_id = p.id and pt3.tag_id = 7
WHERE p.user_id = 4

PS: Если в where указали конкретного пользователя, то users мы присоединяем полностью (без left), так будет работать быстрее.
Решение 2 - контролируем число уникальных тегов на выходе.

Select a.*
from
(
SELECT p.*, u.name, count (distinct  pt1.tag_id) over (partition by p.id) unik_tag_count
 FROM posts p
JOIN users u ON u.id = p.user_id
JOIN postTags pt1 ON pt1.post_id = p.id
WHERE p.user_id = 4
and pt1.tag_id in (3, 5, 7)
) a
where  a.unik_tag_count = 3 -- уникальное кол-во тегов на один пост

Comments

[lolrofl01]

Спасибо за ответ. А Вы уверены, что это единственный способ? Я его рассматривал, но тегов может быть и 20, и 30. Вообще постов очень много, теги ставятся автоматически, порой и вручную еще дополняют, иногда их по 50-60 на статью. А юзер так вообще может и 70 навыбирать...не ляжет ли сервер от десятка таких одновременных запросов? Система не хайлоад, в день от силы 3к юзеров. Но фильтром могут запросто одновременно 10-20 человек...и черт знает сколько чего они навыбирают. Тем более там кроме указанных мною опций - еще с десяток других...

[alexalexes]

Попробуйте решение 2.
Проверьте оба решения на производительность, distinct может оказаться коварнее, чем куча лишних join.

[alexalexes]

Поправил решение. Нужен оконный вариант функции count. Если будете испытывать на mySQL ниже 8 версии, то нужен будет велосипед с пользовательскими параметрами, чтобы сделать аналог такой функции.
PS: Прелесть такого решения в том, что group by не нужно прописывать, окно его инкапсулирует по своей сути.

[lolrofl01]

alexalexes, Огромное спасибо за помощь!) Конечно второй вариант выглядит сложно, во всяком случае для меня) Попробую завтра, сегодня уже без сил) Скажите, а у меня архитектура таблиц неправильная, раз так с бубном приходится танцевать? И можно ли обойтись без промежуточной таблицы postTags? С ней как-то все проще...за исключением фильтрации..

[alexalexes]

Если у вас еще есть таблица Tags, которая содержит свойства тегов, например, его наименование, а таблица postTags является промежуточной таблицей для организации связи "многие ко многим", то тут вполне корректно по архитектуре с точки зрения классического реляционного подхода.
Да, запросы к такой структуре могут выглядеть пугающими и они будут не самые быстрые.
Если есть проблемы в производительности, то к дополнению к основной структуре таблиц разработчики могут дополнять архитектуру еще таблицами, которые не нормализованы сильно и используются в качестве кэша (если нужно искать посты, где есть некоторый набор обязательных тегов, то в такой кэш-таблице будет одно поле со всеми тегами для одного поста и используем поиск по строке, либо разбор json, если СУБД позволяет использовать этот тип данных). Кэш-таблицы не универсальны, они заточены под определенный поиск данных и могут быть сильно избыточны по объему.
PS: Для ускорения могут еще использоваться другие инструменты стека приложения, но это совсем другая история.

[lolrofl01]

Еще раз спасибо большое за ответ! Просто и понятно) Да, в моем случае есть таблица Post с постами, Tags с тегами, и связующая postTags между ними. Кэш-таблицы очень объемны, не подойдет в моем случае. Не так уж и часто этим фильтром будут пользоваться относительно. Еще раз спасибо, было приятно пообщаться)