Выборка из таблицы с сортировкой по иерархии

@Зверушь · Регистрация: 01.05.2010

Студворк — интернет-сервис помощи студентам

Здравствуйте!
Имеется таблица в бд с полями id, parent_id, name. Parent_id может быть либо NULL, либо являться ссылкой на строку этой же таблицы.
Допустим, таблица в бд содержит следующие данные:

Code
1
2
3
4
5
6
7
8
9
10
11
12
id      parent_id       name
----------------------------------------------------------
1       NULL            Раздел 1
2       NULL            Раздел 2
3       NULL            Раздел 3
4       1           Раздел 1.1
5       1           Раздел 1.2
6       4           Раздел 1.1.1
7       5           Раздел 1.2.1
8       2           Раздел 2.1
9       3           Раздел 3.1
10      NULL            Раздел 4

Я хочу вывести в документ эту таблицу иерархически, то есть в таком виде:

Code
1
2
3
4
5
6
7
8
9
10
11
12
id      parent_id       name
----------------------------------------------------------
1       NULL            Раздел 1
4       1           Раздел 1.1
6       4           Раздел 1.1.1
5       1           Раздел 1.2
7       5           Раздел 1.2.1
2       NULL            Раздел 2
8       2           Раздел 2.1
3       NULL            Раздел 3
9       3           Раздел 3.1
10      NULL            Раздел 4

Реально ли получить результирующую выборку в таком виде, используя лишь 1 SQL запрос?
Так как не очень хочется использовать в цикле множество запросов, постепенно углубляясь вглубь иерархии. К тому же в этом случае придется еще пользоваться сортировкой и обрезанием на php. А это слишком замедлит систему.

@fanatikus · 04.11.2016, 19:49

Сообщение от Зверушь

используя лишь 1 SQL запрос

думаю нет
выбрать все, и с помощью рекурсии сформировать как нужно

@Jewbacabra · 04.11.2016, 19:51

Надо использовать Nested Sets

@Зверушь · 05.11.2016, 12:40 **[ТС]**

Спасибо!
Но что-то я туплю... не могу понять как это использовать? Точнее застрял в самом начале... что означают значения полей left и right? В описании об этом толком ничего не написано.

@Jewbacabra · 05.11.2016, 13:36

Сообщение от Зверушь

что означают значения полей left и right?

некоторый индекс, на этой картинке показано в как заполняется https://en.wikipedia.org/wiki/... rsal-2.svg

@Зверушь · 05.11.2016, 17:58 **[ТС]**

Jewbacabra, спасибо! С этим я разобрался. С добавлением и удалением. Но не с выводом. Как вывести в документ иерархически? У меня выводится отсортированным по id... что в принципе логично
На скрине tree изображено дерево, которое я хочу вывести. На двух других скринах, то как оно хранится в базе и то, как оно выводится запросом.
Как их отсортировать иерархически? Какой запрос использовать?

@Зверушь · 05.11.2016, 18:07 **[ТС]**

К слову. Мое дерево может состоять из нескольких сотен элементов. До тысячи в худшем случае. Но далее изменения в нем будут проводится крайне редко.
Дерево мне нужно вывести списком отсортированным по иерархии. Причем нужно использовать постраницную навигацию, выводить не более 50 строк на страницу. Поэтому хочется извлекать данные уже отсортированными одним запросом и использовать LIMIТ 50 для извлечения ограниченного кол-ва строк.
Если же придется использовать несколько запросов, то LIMIT 50 уже не поможет

@Jewbacabra · 05.11.2016, 18:12

Сообщение от Зверушь

Как их отсортировать иерархически?

Сортировать по левому индексу

@Зверушь · 05.11.2016, 18:43 **[ТС]**

Круто! о_О
Пока что магия какая-то... Сложно осознать как это работает.

Добавлено через 16 минут
Кстати. Вопрос по добавлению узлов. В мануале написан следующий код для обновления:

SQL
1
2
UPDATE `nodes` SET `Left` = `Left` + 2 WHERE `Left` > 13;
UPDATE `nodes` SET `Right` = `Right` + 2 WHERE `Right` > 13

Но в этом случае не обновится правый индекс у родителя. Это ошибка? Надо использовать больше или равно в этом случае? или отдельно обновить родителя?

@Jewbacabra · 05.11.2016, 20:02

Сообщение от Зверушь

Это ошибка?

Вроде все правильно. Если у элемента слева 13, то справа будет в любом случае больше значение

@Зверушь · 06.11.2016, 10:15 **[ТС]**

Jewbacabra, вчера дико уставший был, не до конца все описал

В запросе выше 13 - это значение правого индекса родительского узла. То есть если в узле А справа 13 и я добавляю прямого потомка к узлу А, то надо выполнить вышеуказанный запрос. Но если его выполнить, то в узле А справа таки останется 13, хотя должно стать 15 по идее. Поэтому я вот и подумал о том, что в запросе ошибка, и должно быть не

SQL
1
2
WHERE `Left` > 13
WHERE `Right` > 13

а

SQL
1
2
WHERE `Left` >= 13
WHERE `Right` >= 13

@fanatikus · 06.11.2016, 17:12

SQL
1
2
3
UPDATE `nodes` SET `Left` = `Left` + 2, `Right` = `Right` + 2 WHERE `Left` > 13
UPDATE `nodes` SET `Right` = `Right` + 2 WHERE `Right` >=  13 AND `Left` < 13
INSERT INTO `nodes` SET `Left` = 13, `Right` = 13 + 1

@Зверушь · 28.11.2016, 10:09 **[ТС]**

Продолжает тему.
Никак не могу разобраться как перемещать узлы... Гугл юзал, не помог. нашел пару статей, но я почти ничего не понял...
Как перемещать узлы с потомками? может кто детально разжует? Было бы здорово

Или хотя бы просто напишите запросы с пояснениями...
Спасибо!

@fanatikus · 28.11.2016, 14:36

тут все расписано nested set

@Зверушь · 29.11.2016, 00:28 **[ТС]**

fanatikus, спасибо, буду думать.

Добавлено через 9 часов 15 минут
Я все равно не въезжаю... не тянут мои мозги это... надеюсь со временем разберусь.
Решил проблему по-другому.
Так как удаление и добавление узлов у меня реализовано, то решил проблему перемещения при помощи этих двух методов. вот так это реализовано у меня...

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
private function move($id, $pid=1) {
    if (is_null($pid)) {
        $pid = 1;
    }
    $movable = $this->get_by_id($id);
    $parent = $this->get_by_id($pid);
    
    //В подчиненные узлы не перемещаем.
    if ($parent['nested_left'] > $movable['nested_left'] && $parent['nested_right'] < $movable['nested_right']) {
        return false;
    }
    
    $this->db->beginTransaction();
    
    $moving_tree = $this->get_subtree($id);
    $this->delete($id);
    
    foreach ($moving_tree as $num => $node) {
        if ($num == 0) {
            $node['parent_id'] = $pid;
        }
        $data = array(
            'id' => $node['id'],
            'name' => $node['name'],
            'description' => $node['description'],
            'parent_id' => $node['parent_id']
        );
        $this->insert($data);
    }
    
    $this->db->commit();
    
    return true;
}

Новые блоги и статьи Все статьи Все блоги /
Изучаю kubernetes lagorue 13.01.2026 А пригодятся-ли мне знания kubernetes в России?	Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .
Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .	Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .

@Jewbacabra 4925 / 3920 / 1620 Регистрация: 24.04.2014 Сообщений: 11,441
	04.11.2016, 19:51
	Надо использовать Nested Sets 1

@Зверушь 461 / 369 / 94 Регистрация: 01.05.2010 Сообщений: 1,761
	05.11.2016, 12:40 [ТС]
	Спасибо! Но что-то я туплю... не могу понять как это использовать? Точнее застрял в самом начале... что означают значения полей left и right? В описании об этом толком ничего не написано. 0

@Зверушь 461 / 369 / 94 Регистрация: 01.05.2010 Сообщений: 1,761
	05.11.2016, 17:58 [ТС]
	Jewbacabra, спасибо! С этим я разобрался. С добавлением и удалением. Но не с выводом. Как вывести в документ иерархически? У меня выводится отсортированным по id... что в принципе логично На скрине tree изображено дерево, которое я хочу вывести. На двух других скринах, то как оно хранится в базе и то, как оно выводится запросом. Как их отсортировать иерархически? Какой запрос использовать? Миниатюры 0

@Зверушь 461 / 369 / 94 Регистрация: 01.05.2010 Сообщений: 1,761
	05.11.2016, 18:07 [ТС]
	К слову. Мое дерево может состоять из нескольких сотен элементов. До тысячи в худшем случае. Но далее изменения в нем будут проводится крайне редко. Дерево мне нужно вывести списком отсортированным по иерархии. Причем нужно использовать постраницную навигацию, выводить не более 50 строк на страницу. Поэтому хочется извлекать данные уже отсортированными одним запросом и использовать LIMIТ 50 для извлечения ограниченного кол-ва строк. Если же придется использовать несколько запросов, то LIMIT 50 уже не поможет 0

@Зверушь 461 / 369 / 94 Регистрация: 01.05.2010 Сообщений: 1,761
	28.11.2016, 10:09 [ТС]
	Продолжает тему. Никак не могу разобраться как перемещать узлы... Гугл юзал, не помог. нашел пару статей, но я почти ничего не понял... Как перемещать узлы с потомками? может кто детально разжует? Было бы здорово Или хотя бы просто напишите запросы с пояснениями... Спасибо! 0

@fanatikus 1932 / 1523 / 703 Регистрация: 17.11.2012 Сообщений: 6,585
	28.11.2016, 14:36
	тут все расписано nested set 1