<rmcreative>

RSS

Выбрать посты, содержащие все теги из списка

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

  1. №5174
    xandeadx
    xandeadx 12 авг. 2011 г., 3:32:19

    в первом варианте, можно избавиться от лишнего джоина, если предварительно получить id-ы тегов:

    SELECT * FROM post p
    JOIN post_tag pt1 ON pt1.post_id = p.id AND pt1.tag_id = 12
    JOIN post_tag pt2 ON pt2.post_id = p.id AND pt2.tag_id = 34
    ...
  2. №5179
    ZhAN
    ZhAN 12 авг. 2011 г., 9:51:56
    SELECT *
    FROM post p
    WHERE p.id IN (
        SELECT post_id 
        FROM post_rag pt
        WHERE pt.tag_id IN (
            SELECT t.id FROM tag t
            WHERE t.name IN ('php', 'yii')
        )
    )
  3. №5182
    Alex
    Alex 12 авг. 2011 г., 10:22:36

    Спасибо, интересные решения. Запросы возвращают список постов имеющих указанные теги, но без списка тегов, которые эти посты имеют. Как здесь быть? Понятное дело, интересует наиболее эффективное решение в плане производительности.

  4. №5183
    Digital God
    Digital God 12 авг. 2011 г., 12:49:34

    Как 1 из вариантов:

    SELECT * from
    (SELECT p.name,
      MAX( CASE WHEN t.name = 'yii' THEN t.id ELSE 0 END) tag1_id,
      MAX( CASE WHEN t.name = 'yii' THEN t.name ELSE '' END) tag1_name,
      MAX( CASE WHEN t.name = 'php' THEN t.id ELSE 0 END) tag2_id,
      MAX( CASE WHEN t.name = 'php' THEN t.name ELSE '' END) tag2_name
    FROM post p
    JOIN post_tag pt
    ON pt.post_id = p.id
    JOIN tag t
    ON t.id       = pt.tag_id
    WHERE t.name IN ('php','yii')
    GROUP BY p.name) where tag1_id != 0 and tag2_id != 0

    К сожалению плодить придется селект для каждого нового тега, а так же проверку на 0

  5. №5184
    Spider
    Spider 12 авг. 2011 г., 13:47:22

    2Alex: GROUP_CONCAT или отдельным запросом выбирать теги

  6. №5185
    Максим
    Максим 12 авг. 2011 г., 14:12:32

    Ну вот к примеру:

    select q.* from (
    select p.*,t.Id as tag_id,t.tag as tag_name from post p 
    join post2tag pt on pt.idPost=p.Id
    join tag t on t.Id=pt.idTag
    where t.tag in ('Политика','Экономика')
    /** можно убрать group, поставил на случай сломаной БД */
    group by p.Id,t.Id
    order by p.Id,t.Id
    ) q
    where if(@t<>q.Id,(@t:=q.Id) and (@c:=2),@c:=@c-1) and @c=1;

    (Внимание, использовать только в ознакомительных целях этот запрос)

    Но вообще mysql все on при join переносит в where, а если используется <столбец> in (<подзапрос>), то он выполняет подзапрос для каждого уникального незакешированого столбца, причем, он обычно пытается сделать нечто вроде join а таблицы в подзапросе, а уже потом применяет её условие. (это мои выводы из анализа explain extended).

  7. №5186
    Максим
    Максим 12 авг. 2011 г., 14:26:22

    Упростил пред. запрос, так будет проще понять:

    select q.* from (
    select p.*,t.Id as tag_id,t.tag as tag_name from post p 
    join post2tag pt on pt.idPost=p.Id
    join tag t on t.Id=pt.idTag
    where t.tag in ('Политика','Экономика')
    order by p.Id,t.Id
    ) q
    where IF(@t=q.id,@c:=@c+1,(@t:=q.id) AND (@c:=1))=2;

    Этот запрос эмулирует group by(@t) + having(@c)

  8. №5188
    Сергей
    Сергей 13 авг. 2011 г., 0:19:08

    Первый вариант (с JOIN-ами) не масштабируется, второй тоже скорее всего начнет загибать базу при большом кол-ве тегов...

    PS: сделайте шрифт в поле ввода покрупнее! не видно что печатаю же!

  9. №5190
    Максим
    Максим 13 авг. 2011 г., 22:49:18

    Сергей, учитывая развитие mysql можно думать что его оптимизатор вполне будет способен заменить group by+having на join. А запрос логически останется верным. Именно потому я за вариант с group by.

  10. №5191
    Sam
    Sam 14 авг. 2011 г., 0:56:54

    Сергей, сделал шрифт крупнее.

  11. №5196
    igorekk
    igorekk 15 авг. 2011 г., 17:50:44

    А с помощью ALL нельзя решить эту задачу?

    PS. Сам пока не попробовал :)

  12. №5197
    bettrrr
    bettrrr 15 авг. 2011 г., 17:57:01

    ZhAN когда ваш запрос выполнится в MySQL (через несколько минут), почитайте про подзапросы в IN()

  13. №5198
    Максим
    Максим 16 авг. 2011 г., 0:07:06

    2bettrrr, Сомневаюсь, в mysql довольно неслабый оптимизатор. Он сперва выполнит самый глубокий, потом выше уровнем, а потом уже главный запрос. Я специально пару раз проверял подзапросы в in. Хотя опять же, все зависит только от размера БД.

  14. №5232
    ZhAN
    ZhAN 26 авг. 2011 г., 10:00:27

    bettrrr мускул выполнит три простейших для него запроса SELECT IN (pk)

  15. №5273
    maksimko
    maksimko 03 сент. 2011 г., 0:08:26

    Мне кажется, все интересные способы решения в данном случае связаны с неиспользованием или частичным использованием SQL (Sphinx, к примеру).

  16. №5403
    webkyter
    webkyter 25 сент. 2011 г., 23:07:39

    Как вариант, на ряду с использованием таблицы post_tag, так же хранить список тегов через запятую в самой таблице post. Тогда запрос на поиск поста с двумя тегами будет таким:

    SELECT * FROM post WHERE FIND_IN_SET('yii', post.tags) > 0 AND FIND_IN_SET('tag2', post.tags) > 0

    На практике не тестировал.

  17. №5506
    Роман
    Роман 18 окт. 2011 г., 16:35:23

    Да профессионалы! ))

  1. Почта опубликована не будет.

  2. Можно использовать синтаксис Markdown или HTML.

  3. Введите ответ в поле. Щёлкните, чтобы получить другую задачу.