Вопрос, должно быть, предельно простой, но я вдруг понял, что совсем не знаю на него ответа.
Как грамотно сделать выборку по тегам, используя только MySQL (желательно за один запрос и без хранимых процедур)?
Имеется три таблицы: таблица users, содержащая пользователей, таблица tags, содержащая id и названия тегов, и таблица user_tags, содержащая пары (user_id, tag_id). Задача: реализовать выборку всех пользователей, имеющих каждый из набора тегов (1, 2, ..., N), а также (опционально) имеющих хотя бы один из этих тегов.
И если во втором случае, наверное, может помочь DISTINCT (хотя у меня с ним были некоторые проблемы недавно), то с первым я вообще не представляю.
Для правильного вопроса надо знать половину ответа
Зависит от того, уникальна пара (user_id, tag_id) или нет.
Для уникальных всё просто
SELECT `u`.*
FROM `users` AS `u`
JOIN `user_tags` AS `ut` ON `ut`.`user_id` = `u`.`id`
AND `ut`.`tag_id` IN (1, 2, ..., N)
GROUP BY `u`.`id`
HAVING COUNT(*) = N
Для неуникальных добавляется DISTINCT
SELECT `u`.*
FROM `users` AS `u`
JOIN `user_tags` AS `ut` ON `ut`.`user_id` = `u`.`id`
AND `ut`.`tag_id` IN (1, 2, ..., N)
GROUP BY `u`.`id`
HAVING COUNT(DISTINCT `ut`.`tag_id`) = N
Вот который раз смотрю и прошлый раз не сдержался, а в этот прямо напишу: ну вот на кой черт советовать, если вы пока не разбираетесь в теме? Что он получит в вашем прекрасном варианте с "прилепленными" джойнами? Не получится ли так, случайно, что один и тот же юзер ему будет возвращаться N-ное число раз? И придется как раз применять DISTINCT, что еще более замедлит выборку?
Любой код, который выглядит некрасиво - скорее всего, и работает неверно. А ваш код выглядит отвратительно и повторяется. Повторение кода - это второй и еще более верный признак того, что делается все через левую пятку.
Что произойдет, если в вашем варианте добавится еще один таг? Потребуется лезть в код и дописывать его. Третий, итоговый признак того, что вот так делать нельзя.
Артём Каретников: А вы попробуйте сами и убедитесь, что, во первых, никаких дублей не будет, похоже вы JOIN с UNION перепутали. Во-вторых, попробуйте сами реализовать нужную выборку если в таблице связи (а здесь как раз вариант многие-ко-многим с промежуточной таблицей) пары id-ов не уникальны.
Артём Каретников: Нет. Оно у вас только для варианта ИЛИ. Вы для варианта И сделайте, где у пользователя должны быть все указанные тэги, с учётом возможного их повторения в таблице связи.
Именно. Оно для варианта "или", явно короче, чем тот вариант, чем у вас и лишен всех недостатков, о которых я писал. Для варианта И, я считаю, топик-стартер должен сам просто чуть-чуть подумать.
Решение на поверхности.
Но вот что совершенно справедливо, коллега, вы отметили - так это то, что в варианте топик-стартера нет уникальности на сочетание юзер-таг. На моей выборке это никак не скажется, но если можно несколько одинаковых тегов добавить одному юзеру - база спроектированна неверно. Что надо сделать? Добавить уникальный индекс на id_user + id_tag.
Предлагаю пари. Вот мы взрослые люди, поэтому - я ставлю одну тысячу долларов на то, что допишу ровно одну строку без всяких distinct к своему решению, и будут верно отобраны все результаты плюс не надо будет лезть в код при каждом добавлении нового тега или удалении существующего.
Согласны ли вы, коллега, что в вашем варианте в код таки лезть придется? А поддержать меня своей ставкой, пусть даже и меньшей? :)
Артём Каретников: Ну, для варианта ИЛИ я и не писал, только для И с уникальными и неуникальными тэгами. Но таки да, второй вариант я на автомате взял с поиска товаров по параметрам, тут такой не нужен, достаточно слегка COUNT поправить в первом варианте.
Кстати, ни большом количестве пользователей и тэгов надо ещё посмотреть, что будет быстрее - DISTINCT или IN (SELECT), когда в выборке будет раз по 10-20 один и тот же user_id повторяться.
О май гаш... Да не надо там никакой Count. Also you can use:
select id_user from tbl_user t
where id_user exists (select * from tbl_user_tag i where i.id_user = t.id_user)
Это тоже вернет любого, у кого есть хотя бы один тег.
Жаль, что вы не хотите со мной поспорить, жаль. Про скорость вашего и моих вариантов тоже можем, кстати.
Небольшой апдейт: считаем, что пары уникальны. Я вижу, это стало предметом недопонимания. В БД это не заложено, но это поддерживается бизнес-логикой, плодить одинаковые пары нет никакого смысла. Ну и да, можно создать индекс, который запретит добавление одинаковых пар.
Артём Каретников: не могли бы вы по шагам расписать, как это работает? Я вижу, тут перекрёстная ссылка: вложенный запрос ссылается на алиас из внешнего запроса. Но вложенный выполняется первым... Как в таком случае работает условие с алиасом?
Я кстати долго сейчас читал мануалы, и сам пришёл к тому, что написано у Вас) Правда, немного в конце допустил ошибку, использовав WHERE вместо HAVING, но такой вариант MySQL не принял, и выдал ошибку.
Одно только жаль - я сколько ни гуглил, вроде не нашёл способа посчитать длину списка динамически. Похоже, такое сделать невозможно. Было бы круто, если бы можно было, тогда от PHP требовался бы только список ID тегов, но не общее их число. Меньше теоретическая вероятность ошибок была бы.
Александр Попов: В принципе, можно написать пачку вложенных REPLACE, удалив цифры и пробелы, затем взять длину строки, в которой должны остаться только запятые, и прибавить единицу.
Не выглядеть такая пачка вложенных вызовов будет жутко.
Rsa97: хм, почему-то ваше решение работает только для одного тега. Я что-то делаю не так, или что-то мы не учли? К слову, даже для того чтобы работало с одним пришлось модифицировать запрос, добавив "WHERE `tag_id` IS NOT NULL", потому что при JOIN там где не находит - MySQL комбинирует строки со строками с нулевыми значениями в колонках, где условие не может быть выполнено.
Rsa97: смотрите, я немного поменял запрос, добавив вложенный запрос для наглядности происходящего, сути он вроде не меняет
SELECT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL GROUP BY `user_tags`.`user_id` HAVING `count`=2 - не работает, пустой результат
SELECT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL AND `count`=2 - не работает, пишет, что `count` является неизвестной колонкой
SELECT DISTINCT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL HAVING `count`=2 - работает, хотя HAVING без GROUP BY - это что-то странное конечно
SELECT `people`.`user_id`, (SELECT COUNT(*) FROM `user_tags` WHERE `tag_id` IN (1,2) AND `user_id`=`people`.`user_id`) AS `count` FROM `people` LEFT JOIN `user_tags` ON `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2) WHERE `tag_id` IS NOT NULL GROUP BY `user_id` HAVING `count`=2 - тоже работает
Как вы это объясните? По логике то, что вы написали, должно было работать, но оно не хочет :)
А, чёрт, ну логично. Соотнесённый запрос выполняется для каждой найденной строки. На этапе анализа внешнего WHERE никакой `count` ещё не существует :)
Тогда логичнее оставить последний вариант. Хотя механизм работы третьего варианта я так и не до конца понял. Видимо, для HAVING всё равно с чем работать, и тут он просто создаёт одну большую группу и фильтрует её. Оставляя, конечно же, дубли строк.
Rsa97: спасибо, не заметил. Теперь работает. Не очень правда понял, почему в случае с LEFT JOIN всё "сыпится". Кроме заполнения нулевыми значениями в документации особых оговорок нет про специфику LEFT/RIGHT JOIN вроде.
Такой ещё вопрос немного не по теме: почему при выполнении данного запроса я получаю три одинаковых строки, ведь по идее должна проходиться вся таблица `people`, и для каждой её строки должен вычисляться подзапрос (и в зависимости от его значения строка берётся в выдачу или не берётся). Откуда дублирование строк?
SELECT `user_id` FROM `people` WHERE (SELECT COUNT(*) FROM `user_tags` WHERE `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2))=2
Rsa97: да, прошу прощения, я уже понял) Там потому что нет как таковой таблицы пользователей. А таблица people хранит пары (user_id, event_id) - вроде информации о посещении разных событий. Вот что значит неудачное название для таблицы выбрано...
Тогда да, DISTINCT только в помощь :)
Но это не объясняет, почему INNER JOIN выдавал пустой результат.
А самое изящное на ваш взгляд решение было бы какое? Создать дублирующую таблицу, где будут только id людей из вк в качестве единственной колонки (с требованием уникальности)? Или просто оставить мой текущий вариант
SELECT DISTINCT `user_id` FROM `people` WHERE (SELECT COUNT(*) FROM `user_tags` WHERE `people`.`user_id`=`user_tags`.`user_id` AND `tag_id` IN (1,2))=2
Я тут вспомнил, что такая таблица есть, и хранит она пары (id, имя) в единственном экземпляре (там нет записей с двумя одинаковыми id). Стоит наверное использовать её. Оказывается, JOIN работает быстрее чем вложенный запрос, во всяком случае в логе меньше операций при этом. Там весь JOIN занимал 0.0009 секунды, а тут только удаление временной таблицы занимает 0.006 мс.
Кстати, и это тоже логично. В вашем варианте сначала скорее всего просматривается таблица тегов благодаря оптимизатору, а она очень мала (сейчас там меньше 10 строк). Дальше выбираются из первой таблицы уже нужные строки (для этого можно использовать частичный индекс в случае people и первичный ключ id в случае names). Отбирается их ровно столько, сколько строк в user_tags (если у нас нет дублирований во второй таблице). И выборка идёт по индексу. Дальше группировка и оценка числа элементов в группе идёт уже в маленькой временной таблице.
А то, что сотворил я, хоть и смотрится неплохо - это похоже fullscan по таблице people, в которой несколько тысяч строк как минимум, и для каждой итерации ещё не самый лёгкий подзапрос делается.
Александр Попов: EXPLAIN надо делать. Он покажет, какие индексы используются запросом.
В принципе, если вам нужны только `user_id`, то достаточно запроса по таблицу `user_tags`
SELECT `user_id`
FROM `user_tags`
WHERE `tag_id` IN (1, 2)
GROUP BY `user_id`
HAVING COUNT(DISTINCT `user_id`) = 2
Rsa97: я знаю про EXPLAIN, но как-то не умею читать его выдачу. Он пишет про использование каких-то индексов, а я даже не всегда в курсе, откуда они берутся. Например, написано using index, а какой - не сказано. А их иногда несколько у таблицы. И вообще форма вывода там специфичная.
Доброго. Я не буду давать вам готовый результат, а дам пищу к размышлению и один пример. Никакой Distinct не нужен, использовать его приходится в редких случаях. В остальном он говорит о том, что либо база спроектирована неверно, либо неверно написан запрос.
select * from tbl_user where id_user in (select id_user from tbl_user_tag);
Одна строка - и вот вам все пользователи, у которых есть хотя бы один признак.
Не менее просто делается выборка всех пользователей, у которых есть все признаки, но это для самостоятельного размышления. :) Удачи.
Александр Попов @popov654
Не очень понял... В вашем варианте во вложенном запросе будет куча неуникальных пар. Да, я понимаю, что это не принципиально, поскольку IN принимает список, а в списке неуникальные значения просто будут проигнорированы, грубо говоря. Спасибо за наводку. Но у меня главная сложность была с "И", а не с "ИЛИ". То есть мне нужно вернуть id пользователей, у которых встречаются теги с id 5 и 7. Значит вложенный запрос должен вернуть только те id, у которых есть две строки в таблице пар: (x, 5) и (x, 7), (y, 5) и (y, 7). Не важно, будут ли дубли, главное, что надо вернуть те айди, у которых есть две пары с заданными id тегов (или более), но не возвращать те, где всего одна из этих двух пар. Честно говоря, затрудняюсь сходу разгадать вашу загадку)
Была ещё такая мысль - как-то посчитать количество найденных результатов через COUNT(*) и сравнить его с количеством переданных элементов (его можно статически вставить через PHP), но считать надо по каждому id_user... Возможно, что-то типа GROUP BY тут поможет. Но я такого никогда не делал, поэтому опять же, затруднюсь.
Александр Попов: Не так. Уникальный индекс на tbl_user_tag - сделать надо. И надо запомнить, что индекс должен быть на каждое поле, которое участвует в JOIN. Дальше надо просто думать. Например, можно подумать о том, что бы сделать специальную таблицу tbl_tag_search, в нее вставлять список требуемых для поиска тегов и не страдать с тем, сколько переменных будет и как с ними работать.
