Глубоко вложенные подзапросы для обхода деревьев в MySQL

У меня есть таблица в моей базе данных, где я храню древовидную структуру, используя модель гибридного вложенного набора (MPTT) (которая имеет lft а также rght значения) и модель списка смежности (хранение parent_id на каждом узле).

my_table (id, parent_id, lft, rght, alias)

Этот вопрос не относится ни к одному из аспектов дерева MPTT, но я подумал, что оставлю его на всякий случай, если у кого-то будет хорошее представление о том, как его использовать.

Я хочу преобразовать путь псевдонимов для конкретного узла. Например: "users.admins.nickf" найдет узел с псевдонимом "nickf", который является дочерним по отношению к псевдониму "admin", который является дочерним по отношению к "users", который находится в корне. Существует уникальный индекс на (parent_id, alias),

Я начал с написания функции, чтобы она разделяла путь к ее частям, а затем опрашивал базу данных один за другим:

SELECT `id` FROM `my_table` WHERE `parent_id` IS NULL AND `alias` = 'users';-- 1
SELECT `id` FROM `my_table` WHERE `parent_id` = 1 AND `alias` = 'admins';   -- 8
SELECT `id` FROM `my_table` WHERE `parent_id` = 8 AND `alias` = 'nickf';    -- 37

Но потом я понял, что могу сделать это с помощью одного запроса, используя переменное количество вложений:

SELECT `id` FROM `my_table` WHERE `parent_id` = (
    SELECT `id` FROM `my_table` WHERE `parent_id` = (
        SELECT `id` FROM `my_table`
        WHERE `parent_id` IS NULL AND `alias` = 'users'
    ) AND `alias`  = 'admins'
) AND `alias` = 'nickf';

Поскольку количество подзапросов зависит от количества шагов в пути, я столкнусь с проблемами, связанными с наличием слишком большого количества подзапросов? (Если есть даже такая вещь)

Есть ли лучшие / более умные способы выполнить этот запрос?

Источник

2 ответа

Решение

Это работает?

select r0.id 
  from my_table as r0 
  join my_table as r1 on(r0.parent_id = r1.id) 
  join my_table as r2 on(r1.parent_id = r2.id)
 where r0.alias='nickf'
   and r1.alias='admins'
   and r2.alias='users'
   and r2.parent_id is null

Мне кажется, на самом деле нет необходимости во вложенных подзапросах..

или я не прав, что-то упустил?

Источник

Я сам задавался вопросом об этом, и искал что-то, что не становилось медленнее, когда вы углублялись (имеется в виду большее количество уровней в обоих вариантах выше). Проблема, с которой я столкнулся в "моей версии", состоит в том, что она должна создавать все возможные пути. прежде чем он сузит результат до того, который вы на самом деле ищете... так что я думаю, что версия lexu должна превзойти мою даже для очень большого вложения, потому что это простое объединение, но я надеюсь, что кто-то может увидеть его и пожелать расширить на это дальше.

Кроме того, этот способ сделать это определенно выиграл бы от хранимого процесса и / или просмотра его части "путей" (без предложения HAVING). Возможно, для них это лучшее решение, но я, к сожалению, пока недостаточно знаю о производительности SQL, чтобы сказать наверняка. Я могу сказать, что мой становится медленнее, так как данные (число возможных комбинаций путей) увеличиваются, но с представлением (поскольку результат кэшируется и используется для его сужения), оно кажется быстрым (самый большой набор данных, который я нашел было 370 всего, в какой-то момент я создам гораздо больший набор для тестирования.)

SELECT node.id, GROUP_CONCAT(parent.alias
                 ORDER BY parent.lft SEPARATOR '.') AS path_name
FROM my_table AS node, my_table AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rght
GROUP BY node.id HAVING path_name = 'users.admins.nickf'
Источник
Другие вопросы по тегам