Выбрать посты, содержащие все теги из списка
12 августа 2011
В этом блоге примерно с середины 2007-го, когда я переписал его с файлового велосипедохранилища на нормальный SQL, появилась возможность выбрать посты, содержащие все заданные теги. В 2008-м возможность окончательно оформилась в теговую навигацию.
Изначальный способ выборки, который используется и сейчас:
SELECT * FROM post p JOIN post_tag pt1 ON pt1.post_id = p.id JOIN tag t1 ON t1.id = pt1.tag_id AND t1.name = 'php' JOIN post_tag pt2 ON pt2.post_id = p.id JOIN tag t2 ON t2.id = pt2.tag_id AND t2.name = 'yii' -- для каждого дополнительного тега добавляем ещё JOIN-ы
Недавно Максим подсказал способ поизвращённее:
-- Сначала выбираем посты с тегом yii и посты с тегом php -- через IN. Если у поста и тот и тот тег, он будет выбран -- несколько раз. SELECT * FROM post p JOIN post_tag pt ON pt.post_id = p.id JOIN tag t ON pt.tag_id = t.id WHERE t.name IN ('php', 'yii') -- Считаем количество раз, которые выбрался пост. Если оно -- совпадает с количеством тегов в нашем списке, то это -- означает, что у поста есть и тот и тот тег. GROUP BY p.id HAVING COUNT(pt.tag_id) = 2
Есть ли ещё интересные способы решения этой задачи?
Комментарии RSS по email OK
в первом варианте, можно избавиться от лишнего джоина, если предварительно получить id-ы тегов:
Спасибо, интересные решения. Запросы возвращают список постов имеющих указанные теги, но без списка тегов, которые эти посты имеют. Как здесь быть? Понятное дело, интересует наиболее эффективное решение в плане производительности.
Как 1 из вариантов:
К сожалению плодить придется селект для каждого нового тега, а так же проверку на 0
2Alex: GROUP_CONCAT или отдельным запросом выбирать теги
Ну вот к примеру:
(Внимание, использовать только в ознакомительных целях этот запрос)
Но вообще mysql все on при join переносит в where, а если используется <столбец> in (<подзапрос>), то он выполняет подзапрос для каждого уникального незакешированого столбца, причем, он обычно пытается сделать нечто вроде join а таблицы в подзапросе, а уже потом применяет её условие. (это мои выводы из анализа explain extended).
Упростил пред. запрос, так будет проще понять:
Этот запрос эмулирует group by(@t) + having(@c)
Первый вариант (с JOIN-ами) не масштабируется, второй тоже скорее всего начнет загибать базу при большом кол-ве тегов...
PS: сделайте шрифт в поле ввода покрупнее! не видно что печатаю же!
Сергей, учитывая развитие mysql можно думать что его оптимизатор вполне будет способен заменить group by+having на join. А запрос логически останется верным. Именно потому я за вариант с group by.
Сергей, сделал шрифт крупнее.
А с помощью ALL нельзя решить эту задачу?
PS. Сам пока не попробовал :)
ZhAN когда ваш запрос выполнится в MySQL (через несколько минут), почитайте про подзапросы в IN()
2bettrrr, Сомневаюсь, в mysql довольно неслабый оптимизатор. Он сперва выполнит самый глубокий, потом выше уровнем, а потом уже главный запрос. Я специально пару раз проверял подзапросы в in. Хотя опять же, все зависит только от размера БД.
bettrrr мускул выполнит три простейших для него запроса SELECT IN (pk)
Мне кажется, все интересные способы решения в данном случае связаны с неиспользованием или частичным использованием SQL (Sphinx, к примеру).
Как вариант, на ряду с использованием таблицы post_tag, так же хранить список тегов через запятую в самой таблице post. Тогда запрос на поиск поста с двумя тегами будет таким:
SELECT * FROM post WHERE FIND_IN_SET('yii', post.tags) > 0 AND FIND_IN_SET('tag2', post.tags) > 0
На практике не тестировал.
Да профессионалы! ))