получить родителей и детей структуры дерева папок в моем sql <8 и без CTE

У меня есть таблица папок, которая присоединяется к себе по отношению id, parent_id:

CREATE TABLE folders (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title nvarchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO folders(id, title, parent_id) VALUES(1, 'root', null);
INSERT INTO folders(id, title, parent_id) values(2, 'one', 1);
INSERT INTO folders(id, title, parent_id) values(3, 'target', 2);
INSERT INTO folders(id, title, parent_id) values(4, 'child one', 3);
INSERT INTO folders(id, title, parent_id) values(5, 'child two', 3);
INSERT INTO folders(id, title, parent_id) values(6, 'root 2', null);
INSERT INTO folders(id, title, parent_id) values(7, 'other child one', 6);
INSERT INTO folders(id, title, parent_id) values(8, 'other child two', 6);

Я хочу запрос, который возвращает всех родителей этой записи, обратно к маршруту и любых детей.

Поэтому, если я запрашиваю папку с id=3, я получаю записи: 1, 2, 3, 4, 5. Я застрял, как получить родителей.

Версия MYSQL 5.7, и нет никаких планов по обновлению, так что, к сожалению, CTE не вариант.

Я создал эту скрипку sql

Ответ 1

В MySQL 8.0 вы можете использовать Рекурсивные выражения общих таблиц для решения этого варианта использования.

Следующий запрос дает вам родителей данной записи (включая саму запись):

with recursive parent_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join parent_cte pc on f.id = pc.parent_id
)
select * from parent_cte;

| id  | title  | parent_id |
| --- | ------ | --------- |
| 3   | target | 2         |
| 2   | one    | 1         |
| 1   | root   |           |

А вот немного другой запрос, который возвращает дочернее дерево данной записи:

with recursive children_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where parent_id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join children_cte cc on f.parent_id = cc.id
)
select * from children_cte;

| id  | title     | parent_id |
| --- | --------- | --------- |
| 4   | child one | 3         |
| 5   | child two | 3         |

Оба запроса могут быть объединены следующим образом:

with recursive parent_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join parent_cte pc on f.id = pc.parent_id
),
children_cte (id, title, parent_id) as (
  select id, title, parent_id
  from folders
  where parent_id = 3
  union all
  select  f.id, f.title, f.parent_id
  from folders f
  inner join children_cte cc on f.parent_id = cc.id
)
select * from parent_cte
union all select * from children_cte;

| id  | title     | parent_id |
| --- | --------- | --------- |
| 3   | target    | 2         |
| 2   | one       | 1         |
| 1   | root      |           |
| 4   | child one | 3         |
| 5   | child two | 3         |

Демо на БД Fiddle

Ответ 2

В вашей схеме таблицы ID и PARENT_ID соответствуют " Модели списка PARENT_ID " для хранения дерева.

Существует еще одна конструкция, называемая " Модель вложенного набора ", которая упрощает выполнение необходимых операций.

Посмотрите эту прекрасную статью Майка Хиллиера, в которой описываются оба: управление-иерархическими-данными-в-mysql

В итоге:

Дерево хранится в виде таблицы:

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

Нахождение пути от корня к заданному узлу (здесь, "FLASH"):

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY parent.lft;

Поиск всех дочерних элементов данного узла (здесь "ПОРТАТИВНАЯ ЭЛЕКТРОНИКА"):

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

После переименования в таблицу папок

TABLE nested_category → TABLE папки
Столбец category_id → Идентификатор столбца
Название столбца → Название столбца

Решение:

CREATE TABLE folders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO folders(id, title, lft, rgt) values(1, 'root', 1, 10);
INSERT INTO folders(id, title, lft, rgt) values(2, 'one', 2, 9);
INSERT INTO folders(id, title, lft, rgt) values(3, 'target', 3, 8);
INSERT INTO folders(id, title, lft, rgt) values(4, 'child one', 4, 5);
INSERT INTO folders(id, title, lft, rgt) values(5, 'child two', 6, 7);
INSERT INTO folders(id, title, lft, rgt) values(6, 'root 2', 11, 16);
INSERT INTO folders(id, title, lft, rgt) values(7, 'other child one', 12, 13);
INSERT INTO folders(id, title, lft, rgt) values(8, 'other child two', 14, 15);

Путь к цели:

SELECT parent.title
FROM folders AS node,
        folders AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.title = 'target'
ORDER BY parent.lft;

Целевые дети:

SELECT node.title, (COUNT(parent.title) - (sub_tree.depth + 1)) AS depth
    FROM folders AS node,
            folders AS parent,
            folders AS sub_parent,
            (
              SELECT node.title, (COUNT(parent.title) - 1) AS depth
                    FROM folders AS node,
                            folders AS parent
                    WHERE node.lft BETWEEN parent.lft AND parent.rgt
                            AND node.title = 'target'
                    GROUP BY node.title
                    ORDER BY node.lft
            )AS sub_tree
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
            AND sub_parent.title = sub_tree.title
    GROUP BY node.title
    HAVING depth <= 1
    ORDER BY node.lft;

Смотрите sqlfiddle

Чтобы получить все данные в одном запросе, union должно сделать.

Ответ 3

Я решил это в прошлом со второй таблицей, которая содержит транзитивное замыкание всех путей через дерево.

mysql> CREATE TABLE folders_closure (
 ancestor INT UNSIGNED NOT NULL,
 descendant INT UNSIGNED NOT NULL,
 PRIMARY KEY (ancestor, descendant),
 depth INT UNSIGNED NOT NULL
);

Загрузите в эту таблицу кортежи всех пар предок-потомок, включая те, на которые ссылается узел в дереве (путь длиной 0).

mysql> INSERT INTO folders_closure VALUES
     (1,1,0), (2,2,0), (3,3,0), (4,4,0), (5,5,0), (6,6,0),
     (1,2,1), (2,3,1), (3,4,1), (3,5,1), (1,4,2), (1,5,2),
     (6,7,1), (6,8,1);

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

mysql> SELECT d.id, d.title, cl.depth FROM folders_closure cl
     JOIN folders d ON d.id=cl.descendant WHERE cl.ancestor=1;
+----+-----------+-------+
| id | title     | depth |
+----+-----------+-------+
|  1 | root      |     0 |
|  2 | one       |     1 |
|  4 | child one |     2 |
|  5 | child two |     2 |
+----+-----------+-------+

Я вижу, что многие люди рекомендуют решение Nested Sets, которое было представлено в 1992 году и стало популярным после того, как Джо Селко включил его в свою книгу " SQL для умных людей" в 1995 году. Но мне не нравится техника Nested Sets, потому что цифры на самом деле не являются ссылки на первичные ключи узлов в вашем дереве, и это требует перенумерации множества строк при добавлении или удалении узла.

Я написал о методе закрывающих таблиц в разделе Каков наиболее эффективный/элегантный способ разбить плоский стол на дерево? и некоторые другие мои ответы с тегом иерархических данных.

Я сделал презентацию об этом: Модели для иерархических данных.

Я также рассказал об этом в главе моей книги " Антипаттерны SQL: предотвращение ловушек программирования баз данных".

Ответ 4

Если это гарантирует, что дочерние узлы всегда имеют более высокий идентификатор, чем родительский, тогда вы можете использовать пользовательские переменные.

Получить потомков:

select f.*, @l := concat_ws(',', @l, id) as dummy
from folders f
cross join (select @l := 3) init_list
where find_in_set(parent_id, @l)
order by id

Результат:

id | title     | parent_id | dummy
---|-----------|-----------|------
 4 | child one |         3 | 3,4
 5 | child two |         3 | 3,4,5

Получить предков (в том числе и себя):

select f.*, @l := concat_ws(',', @l, parent_id) as dummy
from folders f
cross join (select @l := 3) init_list
where find_in_set(id, @l)
order by id desc

Результат:

id | title  | parent_id | dummy
 3 | target |         2 | 3,2
 2 | one    |         1 | 3,2,1
 1 | root   |      null | 3,2,1

демонстрация

Обратите внимание, что этот метод основан на недокументированном порядке оценки и не будет возможен в будущих версиях.

Кроме того, он не очень эффективен, поскольку оба запроса требуют полного сканирования таблицы, но могут подойти для небольших таблиц. Однако для небольших таблиц я бы просто взял полную таблицу и решил задачу с помощью рекурсивной функции в коде приложения.

Для больших таблиц я бы рассмотрел более сложное решение, такое как следующая хранимая процедура:

create procedure get_related_nodes(in in_id int)
begin
  set @list = in_id;
  set @parents = @list;

  repeat
    set @sql = '
      select group_concat(id) into @children
      from folders
      where parent_id in ({parents})
    ';
    set @sql = replace(@sql, '{parents}', @parents);
    prepare stmt from @sql;
    execute stmt;
    set @list = concat_ws(',', @list, @children);
    set @parents = @children;
  until (@children is null) end repeat;

  set @child = in_id;
  repeat
    set @sql = '
      select parent_id into @parent
      from folders
      where id = ({child})
    ';
    set @sql = replace(@sql, '{child}', @child);
    prepare stmt from @sql;
    execute stmt;
    set @list = concat_ws(',', @parent, @list);
    set @child = @parent;
  until (@parent is null) end repeat;

  set @sql = '
    select *
    from folders
    where id in ({list})
  ';
  set @sql = replace(@sql, '{list}', @list);
  prepare stmt from @sql;
  execute stmt;
end

Используйте это с

call get_related_nodes(3)

Это вернется

id | title     | parent_id
---|-----------|----------
 1 | root      | 
 2 | one       | 1
 3 | target    | 2
 4 | child one | 3
 5 | child two | 3

демонстрация

Я ожидаю, что эта процедура будет работать так же хорошо, как рекурсивный запрос CTE. В любом случае у вас должен быть индекс parent_id.

Ответ 5

Вы можете выполнить объединение между родительскими и дочерними строками следующим образом:

select title, id, @parent:=parent_id as parent from
               (select @parent:=3 ) a join (select * from folders order by id desc) b where @parent=id
union select title, id, parent_id as parent from folders where  parent_id=3 ORDER BY id

вот образец dbfiddle

Ответ 6

Примечание. Мое решение более или менее совпадает с @Marc Alff. Не понимал, что это было уже там, прежде чем набирать/готовить ответ в редакторе.

Очень трудно получить запрос для достижения вашей цели (или других типичных требований к иерархическому набору данных) без использования CTE или другой поддержки иерархических запросов (например, ранее, соединение в Oracle). Это был основной драйвер для баз данных, чтобы придумывать CTE и т.д.

Много-много лет назад, когда такая поддержка моделирования иерархических объектов не была доступна в базах данных, требования, изложенные вами и многими другими, были решены путем моделирования таких объектов немного по-другому.

Концепция проста. По сути, в иерархической таблице (или в отдельном внешнем ключе таблицы в иерархической таблице) введены еще два атрибута, которые называются left_boundary и right_boundary (вызывайте все, что хотите, после того, что в имени). Для каждой строки значения (числа) для этих атрибутов выбираются так, чтобы они охватывали значения этих атрибутов для всех их дочерних элементов. Другими словами, левая и правая границы ребенка будут находиться между левой и правой границами его родителей.

Кстати пример

Создание этой иерархии было частью раннего утреннего пакетного задания, или границы были выбраны настолько широко друг от друга во время разработки, что они легко охватывали все глубины дерева.

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

CREATE TABLE folder_boundaries (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  folder_id int(10) unsigned NOT NULL,
  left_boundary int(10) unsigned,
  right_boundary int(10) unsigned,
  PRIMARY KEY (id),
  FOREIGN KEY (folder_id) REFERENCES folders(id)
);

Данные для этой таблицы основаны на вашем наборе данных

NSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(1, 1, 10);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(2, 2, 9);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(3, 3, 8);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(4, 4, 4);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(5, 4, 4);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(6, 21, 25);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(7, 22, 22);
INSERT INTO folder_boundaries(folder_id, left_boundary, right_boundary) VALUES(7, 22, 22);

Вот запрос для достижения того, что вы после

select f.id, f.title
from folders f
join folder_boundaries fb on f.id = fb.folder_id
where fb.left_boundary < (select left_boundary from folder_boundaries where folder_id = 3)
and fb.right_boundary > (select right_boundary from folder_boundaries where folder_id = 3)
union all
select f.id, f.title
from folders f
join folder_boundaries fb on f.id = fb.folder_id
where fb.left_boundary >= (select left_boundary from folder_boundaries where folder_id = 3)
and fb.right_boundary <= (select right_boundary from folder_boundaries where folder_id = 3)

Результат

Ответ 7

Небольшой код с использованием хранимых процедур, протестированный на 5.6:

drop procedure if exists test;
DELIMITER //
create procedure test(in testid int)
begin
    DECLARE parent int;
    set parent = testid;

    drop temporary table if exists pars;
    CREATE temporary TABLE pars (
      id int(10) unsigned NOT NULL AUTO_INCREMENT,
      title nvarchar(255) NOT NULL,
      parent_id int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (id)
    );

    #For getting heirarchy
    while parent is not null do
        insert into pars
        select  * from folders where id = parent;
        set parent = (select parent_id from folders where id = parent);
    end while;

    #For getting child
    insert into pars
    select  * from folders where parent_id = testid;

    select * from pars;
end //
DELIMITER ;

ниже приведен вызов коду:

call test(3);

И вывод:

Конечный результат может быть отформатирован со строкой, объединенной как требуется, как только мы получим таблицу, остальное должно быть легким, я думаю. Кроме того, если идентификатор может быть отсортирован, это было бы здорово для форматирования.

Не говоря уже о том, что поля id и parent_id должны быть индексами, чтобы это работало эффективно.

Ответ 8

Предположим, вы знаете максимальную глубину дерева, вы можете "создать" цикл, чтобы получить то, что вы хотите:

Получить родительские узлы:

SELECT  @id :=
        (
        SELECT  parent_id
        FROM    folders
        WHERE   id = @id
        ) AS folderId, vars.id
FROM    (
        SELECT  @id := 7 AS id
        ) vars
INNER JOIN (
    SELECT 0 AS nbr UNION ALL SELECT 1 UNION ALL SELECT 2 
 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 
 UNION ALL SELECT 9) temp
WHERE @id IS NOT NULL

Получить дочерние узлы:

SELECT  @id :=
        (
        SELECT  GROUP_CONCAT(id)
        FROM    folders
        WHERE   FIND_IN_SET(parent_id, @id)
        ) AS folderIds, vars.id
FROM    (
        SELECT  @id := 1 AS id
        ) vars
INNER JOIN (
    SELECT 0 AS nbr UNION ALL SELECT 1 UNION ALL SELECT 2 
 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 
 UNION ALL SELECT 9) temp
WHERE @id IS NOT NULL

Это работает

Создание соединения между статическим переменным подзапросом (SELECT @id := 1 AS id) и статическим набором из 10 строк в этом случае (максимальная глубина)
использование подзапроса в select для обхода дерева и поиска всех родительских или дочерних узлов

Цель объединения - создать результирующий набор из 10 строк, чтобы подзапрос в выборе выполнялся 10 раз.

В качестве альтернативы, если вы не знаете максимальную глубину, вы можете заменить объединенный подзапрос на

INNER JOIN (
SELECT 1 FROM folder) temp

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

INNER JOIN (
SELECT 1 FROM folder LIMIT 100) temp

Ссылки: - Иерархические запросы в MySQL

Ответ 9

если ваш parent_id всегда идет в порядке возрастания, тогда запрос ниже - отличное решение.

если вы получите в результате ваш идентификатор в нулевое родительское значение, тогда, пожалуйста, перейдите по ссылке http://www.sqlfiddle.com/#!9/b40b8/258 (при передаче id = 6) http://www.sqlfiddle.com/#! 9/b40b8/259 (при передаче id = 3)

SELECT * FROM folders f
WHERE id = 3 
OR 
  (Parent_id <=3 AND Parent_id >= 
  (SELECT id FROM folders Where id <= 3 AND parent_id IS NULL Order by ID desc LIMIT 1)) OR (id <= 3 AND IFNULL(Parent_id,0) = 0)
  AND id >= (SELECT id FROM folders Where id <= 3 AND parent_id IS NULL Order by ID desc LIMIT 1);

ИЛИ ЖЕ

Вы не получите ваш проходной идентификатор наверху у родителей, тогда, пожалуйста, перейдите по ссылке, как показано ниже. http://www.sqlfiddle.com/#!9/b40b8/194 (при передаче id = 3)
http://www.sqlfiddle.com/#!9/b40b8/208 (при передаче id = 6)

SELECT 
      * 
FROM 
     folders f
WHERE 
    id = 3 OR Parent_id <=3 
    OR (id <= 3  AND IFNULL(Parent_id,0) = 0);