Простая задача построение дерева с динамическим количеством уровней вложенности и возможностью настраиваемой сортировки ветвей и листьев
**Schema (PostgreSQL v12)**
create table catalog
(id int, parent int, name text, ordered int);
insert into catalog (id, parent, name, ordered)
VALUES
(1, null, 'root10', 10),
(2, null, 'root2', 8),
(3, null, 'root1', 1),
(4, 3, 'child1-root1', 1),
(5, 3, 'child3-root1', 3),
(6, 3, 'child2-root1', 2),
(7, 1, 'child3-root10', 30),
(8, 1, 'child2-root10', 20),
(9, 1, 'child1-root10', 10),
(7, 2, 'child3-root2', 30),
(8, 2, 'child1-root2', 10),
(9, 2, 'child2-root2', 20)
;
---
**Query #1**
with recursive
r as (
select c1.*, 1 lvl, (row_number() over (partition by parent order by ordered))::text path from catalog c1 where c1.parent is null
union all
select c2.*, r.lvl + 1 lvl, concat(r.path, row_number() over (partition by c2.parent order by c2.ordered)) path from catalog c2, r where c2.parent = r.id
)
select * from r
order by path;
| id | parent | name | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3 | | root1 | 1 | 1 | 1 |
| 4 | 3 | child1-root1 | 1 | 2 | 11 |
| 6 | 3 | child2-root1 | 2 | 2 | 12 |
| 5 | 3 | child3-root1 | 3 | 2 | 13 |
| 2 | | root2 | 8 | 1 | 2 |
| 8 | 2 | child1-root2 | 10 | 2 | 21 |
| 9 | 2 | child2-root2 | 20 | 2 | 22 |
| 7 | 2 | child3-root2 | 30 | 2 | 23 |
| 1 | | root10 | 10 | 1 | 3 |
| 9 | 1 | child1-root10 | 10 | 2 | 31 |
| 8 | 1 | child2-root10 | 20 | 2 | 32 |
| 7 | 1 | child3-root10 | 30 | 2 | 33 |
С Postgres все ОК, в Oracle и MS SQL Server тоже все работает.
MySQL и MariaDB заявляют о поддержке рекурсивных обобщенных табличных выражений, но похоже не полностью.
with recursive
r as (
select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
union all
select c2.*, r.lvl + 1 lvl, concat(r.path, convert(row_number() over (partition by c2.parent order by c2.ordered), char)) path from catalog c2, r where c2.parent = r.id
)
select * from r order by path
MySQL 8.0.12
Query Error: Error: UNKNOWN_CODE_PLEASE_REPORT: Recursive Common Table Expression 'r' can contain neither aggregation nor window functions in recursive query block
---
MariaDB 10.4.12-MariaDB
ER_NOT_STANDARD_COMPLIANT_RECURSIVE (conn=2466, no: 4008, SQLState: HY000) Restrictions imposed on recursive definitions are violated for table 'r' sql: with recursive r as ( select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null union all select c2.*, r.lvl + 1 lvl, concat(r.path, convert(row_number() over (partition by c2.parent order by c2.ordered), char)) path from catalog c2, r where c2.parent = r.id ) select * from r order by path - parameters:[]
А вот если из второго запроса убрать оконную функцию row_number, и немного изменив логику формирования пути сортировки, то получим нужный результат.
**Schema (MySQL v8.0)**
create table catalog
(id int, parent int, name text, ordered int);
insert into catalog
(id, parent, name, ordered)
VALUES
(1, null, 'root10', 10),
(2, null, 'root2', 8),
(3, null, 'root1', 1),
(4, 3, 'child1-root1', 1),
(5, 3, 'child3-root1', 3),
(6, 3, 'child2-root1', 2),
(7, 1, 'child3-root10', 30),
(8, 1, 'child2-root10', 20),
(9, 1, 'child1-root10', 10),
(10, 2, 'child3-root2', 30),
(11, 2, 'child1-root2', 10),
(12, 2, 'child2-root2', 20),
(13, 8, 'child1-child2-root10', 2),
(14, 8, 'child2-child2-root10', 3)
;
---
**Query #1**
with recursive
r as (
select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
union all
select c2.*, r.lvl + 1 lvl, concat(r.path, convert(c2.ordered, char)) path from catalog c2, r where c2.parent = r.id
)
select * from r
order by path;
| id | parent | name | ordered | lvl | path |
| --- | ------ | -------------------- | ------- | --- | ---- |
| 3 | | root1 | 1 | 1 | 1 |
| 4 | 3 | child1-root1 | 1 | 2 | 11 |
| 6 | 3 | child2-root1 | 2 | 2 | 12 |
| 5 | 3 | child3-root1 | 3 | 2 | 13 |
| 2 | | root2 | 8 | 1 | 2 |
| 11 | 2 | child1-root2 | 10 | 2 | 210 |
| 12 | 2 | child2-root2 | 20 | 2 | 220 |
| 10 | 2 | child3-root2 | 30 | 2 | 230 |
| 1 | | root10 | 10 | 1 | 3 |
| 9 | 1 | child1-root10 | 10 | 2 | 310 |
| 8 | 1 | child2-root10 | 20 | 2 | 320 |
| 13 | 8 | child1-child2-root10 | 2 | 3 | 3202 |
| 14 | 8 | child2-child2-root10 | 3 | 3 | 3203 |
| 7 | 1 | child3-root10 | 30 | 2 | 330 |
UPDATE
Ан, нет, проглядел я, без использования оконной функции во втором подзапросе правильный «путь» не получить. В примере все хорошо, так как данные удачно легли.
А если, так, то беда
**Schema (MySQL v8.0)**
create table catalog
(id int, parent int, name text, ordered int);
insert into catalog
(id, parent, name, ordered)
VALUES
(1, null, 'root10', 10),
(2, null, 'root2', 8),
(3, null, 'root1', 1),
(4, 3, 'child1-root1', 1),
(5, 3, 'child3-root1', 10),
(6, 3, 'child2-root1', 2),
(7, 1, 'child3-root10', 30),
(8, 1, 'child2-root10', 20),
(9, 1, 'child1-root10', 10),
(7, 2, 'child3-root2', 30),
(8, 2, 'child1-root2', 10),
(9, 2, 'child2-root2', 20)
;
---
**Query #1**
with recursive
r as (
select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
union all
select c2.*, r.lvl + 1 lvl, concat(r.path, convert(c2.ordered, char)) path from catalog c2, r where c2.parent = r.id
)
select * from r
order by path;
| id | parent | name | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3 | | root1 | 1 | 1 | 1 |
| 4 | 3 | child1-root1 | 1 | 2 | 11 |
| 5 | 3 | child3-root1 | 10 | 2 | 110 |
| 6 | 3 | child2-root1 | 2 | 2 | 12 |
| 2 | | root2 | 8 | 1 | 2 |
| 8 | 2 | child1-root2 | 10 | 2 | 210 |
| 9 | 2 | child2-root2 | 20 | 2 | 220 |
| 7 | 2 | child3-root2 | 30 | 2 | 230 |
| 1 | | root10 | 10 | 1 | 3 |
| 9 | 1 | child1-root10 | 10 | 2 | 310 |
| 8 | 1 | child2-root10 | 20 | 2 | 320 |
| 7 | 1 | child3-root10 | 30 | 2 | 330 |
---
UPDATE 2
Есть выход, но нужно будет немного усложнить механизм сохранения значений сортировки в разрезе ветви одного уровня. Например, добиваем «0»-ми до максимального кол-ва знаков в числе по ветви одного уровня
**Schema (MySQL v8.0)**
create table catalog
(id int, parent int, name text, ordered varchar(10));
insert into catalog
(id, parent, name, ordered)
VALUES
(1, null, 'root10', '10'),
(2, null, 'root2', '08'),
(3, null, 'root1', '01'),
(4, 3, 'child1-root1', '01'),
(5, 3, 'child3-root1', '10'),
(6, 3, 'child2-root1', '02'),
(7, 1, 'child3-root10', '30'),
(8, 1, 'child2-root10', '20'),
(9, 1, 'child1-root10', '10'),
(7, 2, 'child3-root2', '30'),
(8, 2, 'child1-root2', '10'),
(9, 2, 'child2-root2', '20')
;
---
**Query #1**
with recursive
r as (
select c1.*, 1 lvl, c1.ordered path from catalog c1 where c1.parent is null
union all
select c2.*, r.lvl + 1 lvl, concat(r.path, c2.ordered) path from catalog c2, r where c2.parent = r.id
)
select * from r
order by path;
| id | parent | name | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3 | | root1 | 01 | 1 | 01 |
| 4 | 3 | child1-root1 | 01 | 2 | 0101 |
| 6 | 3 | child2-root1 | 02 | 2 | 0102 |
| 5 | 3 | child3-root1 | 10 | 2 | 0110 |
| 2 | | root2 | 08 | 1 | 08 |
| 8 | 2 | child1-root2 | 10 | 2 | 0810 |
| 9 | 2 | child2-root2 | 20 | 2 | 0820 |
| 7 | 2 | child3-root2 | 30 | 2 | 0830 |
| 1 | | root10 | 10 | 1 | 10 |
| 9 | 1 | child1-root10 | 10 | 2 | 1010 |
| 8 | 1 | child2-root10 | 20 | 2 | 1020 |
| 7 | 1 | child3-root10 | 30 | 2 | 1030 |