Извлечение древовидной структуры из базы данных

Для всех запросов в статье используется синтаксис СУБД SQLite. Для других СУБД синтаксис может незначительно отличаться.

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

create table tree (
  id     integer,
  parent integer,
  name   text,

  primary key (id),
  foreign key (parent) references tree(id)
    on update cascade on delete cascade
);

В поле parent задается родитель для элемента древовидной структуры, причем в качестве значения этого поля используется значение поля id другого элемента. В поле nameзадается имя элемента.

Для наглядности зададимся древовидной структурой:

  • Овощи
    • Картофель
      • Аспиа
      • Виталот
      • Диво
    • Томаты
      • Томат обыкновенный
      • Томат Перуанский
  • Фрукты
    • Груши
      • Груша обыкновенная
    • Яблоки

Такую структуру можно создать следующими запросами:

insert into tree values ( 1, null, 'Овощи');
insert into tree values ( 2,    1, 'Картофель');
insert into tree values ( 3,    2, 'Аспиа');
insert into tree values ( 4,    2, 'Виталот');
insert into tree values ( 5,    2, 'Диво');
insert into tree values ( 6,    1, 'Томаты');
insert into tree values ( 7,    6, 'Томат обыкновенный');
insert into tree values ( 8,    6, 'Томат перуанский');
insert into tree values ( 9, null, 'Фрукты');
insert into tree values (10,    9, 'Груши');
insert into tree values (11,   10, 'Груша обыкновенная');
insert into tree values (12,    9, 'Яблоко');

Здесь null означает корневую группу.

Предположим, что требуется извлечь данные из таблицыtree в следующем виде:

/Овощи
/Фрукты
/Овощи/Картофель
/Овощи/Томаты
/Фрукты/Груши
/Фрукты/Яблоко
/Овощи/Картофель/Аспиа
/Овощи/Картофель/Виталот
/Овощи/Картофель/Диво
/Овощи/Томаты/Томат обыкновенный
/Овощи/Томаты/Томат перуанский
/Фрукты/Груши/Груша обыкновенная

Для этого понадобится рекурсивный запрос:

with cte as (
  select
    id,
    parent,
    '/' || name as name
  from tree where parent is null
  
  union all
  
  select
    tree.id,
    tree.parent,
    cte.name || '/' || tree.name
  from cte, tree on tree.parent = cte.id
)
select name from cte;

Рекурсивные запросы поддерживаются в SQLite начиная с версии 3.8.3 от 3 февраля 2014 года.

-->
X
Обратный звонок