Есть иерархический каталог. Есть продукты. Как объединить?
Предположим, у нас есть иерархический библиотечный каталог. И есть книги, отнесенные к разделам каталога. Как построить дерево, включающее и каталог, и книги, включенные в это дерево в виде листьев?
Каталог - это изначально иерархическая структура:
(id, parent_id, text)
Книги - это плоская структура со ссылкой (topic_id) на каталог:
(id, authors, title, year, topic_id)
Они хранятся в разных таблицах базы данных. Как их объединить в единый иерархический запрос?
Если мы хотим построить дерево иерархии по изначально иерархической таблице каталога, то это в Oracle очень просто:
select id, parent_id, text
from digest.lib_topics_vi
start with parent_id is null
connect by prior id = parent_id
;
1 Образование
2 1 Дошкольное образование
3 1 Высшее образование
Но мы хотим "подвесить" публикации в это же дерево. Это значит, что нам необходимо расширить таблицу (представление) каталога, присоединив к ней данные из таблицы публикаций, но так, чтоб они составляли единую иерархию.
Применим составной ключ:
- В качестве первого домена составного ключа "старшей" по иерархии таблицы каталога используем более раннюю по сортировке букву, например 'A';
- В качестве первого домена составного ключа "младшей" по иерархии таблицы публикаций используем более позднюю по сортировке букву, например 'B';
- В качестве вторых доменов составных ключей используем их естественный цифровой идентификатор;
- Не забудем, что ссылка публикации на раздел каталога должна стать иерархической ссылкой на родителя, т.е. иметь первый домен из таблицы каталога, т.е. 'A', а не 'B'.
Возьмем реальную часть запроса, содержащую условие и форматирование, включая иконки для ветвей каталога и публикаций, в которой описанное применено:
select 'A'||id as id, 'A'||parent_id as parent_id, text, 'fa-folders' as icon from digest.lib_topics_vi
union all
select 'B'||id, 'A'||topic_id, year||' - '||rtrim(authors, '. ')||nvl2(authors, '. ', null)||title, 'fa-book' as icon from digest.lib_list_vi where type = :P7_TYPE
Внимательный читатель заметит характерный для Oracle APEX бинд :P7_TYPE. Конечно, это элемент страницы № 7 реального приложения.
Для параметра, равного 3 (презентации), получим опорное множество со сплошной иерархией:
A1 A Образование fa-folders
A2 A1 Дошкольное образование fa-folders
A3 A1 Высшее образование fa-folders
B1 A3 2019 - Владимир Жанович Куклин. Механизмы оценки текущего состояния и тенденций развития в системе высшего образования: проблемы и перспективы fa-book
B3 A3 2019 - Татьяна Львовна Клячко. Тенденции развития высшего образования fa-book
Запишем иерархический запрос:
select id, parent_id, text
from
(
select 'A'||id as id, 'A'||parent_id as parent_id, text, 'fa-folders' as icon from digest.lib_topics_vi
union all
select 'B'||id, 'A'||topic_id, year||' - '||rtrim(authors, '. ')||nvl2(authors, '. ', null)||title, 'fa-book' as icon from digest.lib_list_vi where type = :P7_TYPE
)
start with parent_id = 'A'
connect by prior id = parent_id
order siblings by text
;
A1 A Образование
A3 A1 Высшее образование
B1 A3 2019 - Владимир Жанович Куклин. Механизмы оценки текущего состояния и тенденций развития в системе высшего образования: проблемы и перспективы
B3 A3 2019 - Татьяна Львовна Клячко. Тенденции развития высшего образования
A2 A1 Дошкольное образование
На глаз иерархия, возможно, видна плохо - подчеркнем ее псевдографикой:
select --id, parent_id,
rpad('|----', 5*(level-1), '|----')||text
from
(
...
)
...
;
Образование
|----Высшее образование
|----|----2019 - Владимир Жанович Куклин. Механизмы оценки текущего состояния и тенденций развития в системе высшего образования: проблемы и перспективы
|----|----2019 - Татьяна Львовна Клячко. Тенденции развития высшего образования
|----Дошкольное образование
Задача решена.
Далее дело техники. Берем Oracle APEX, создаем экранный регион, определяем ему тип tree, записываем полученный запрос с учетом некоторых дополнительных требований:
Сдаем продукт заказчику. Заняло, как обычно, минут 15.