Есть иерархический каталог групп товаров, но не во всех группах товар присутствует. Надо исключить из совокупного дерева ветви, на которых нет листьев-товаров. Решим на примере библиотеки.
В предыдущей заметке мы решили задачу, показав полный каталог:
Теперь нам необходимо его ограничить, подавив вывод пустых рубрик:
Начнем с определения множества рубрик, на которые есть публикации:
select topic_id from digest.lib_list_vi where type = :P7_TYPE
;
Если бы у нас была не иерархия, а плоская таблица, мы бы использовали конструкцию IN:
select... from... where id in
(select topic_id from digest.lib_list_vi where type = :P7_TYPE)
;
Но у нас иерархия и нам необходимо определить необходимые ветви в обратном направлении - не от корней к листьям, как обычно, а от нужных листьев к их корням. Соответственно, наше только что определенное множество встанет не в конструкцию IN, а в конструкцию START WITH запроса. Кроме этого, поскольку мы выполняем обратный траверс иерархии, в конструкции CONNECT BY отношение иерархического предшествования инвертируется: вместо традиционного connect by prior id = parent_id будет connect by prior parent_id = id, рождение пошло вспять от детей к родителям:
select * from digest.lib_topics_vi
start with id in (select topic_id from digest.lib_list_vi where type = :P7_TYPE)
connect by prior parent_id = id
;
Далее мы просто заменяем в каталожной части объединенного синтетического иерархического множества с составными ключами таблицу каталога на только что полученное выражение:
Было в предыдущей заметке:
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
;
Стало:
select id, parent_id, text
from
(
select 'A'||id as id, 'A'||parent_id as parent_id, text, 'fa-folders' as icon
from
(
select * from digest.lib_topics_vi
start with id in (select topic_id from digest.lib_list_vi where type = :P7_TYPE)
connect by prior parent_id = id
)
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
;
Задача решена:
Встроенный механизм иерархических запросов Oracle является мощным средством компактного решения нетривиальных задач. Oracle APEX позволяет на основе этих решений получить промышленный Web продукт с очень высокой скоростью разработки и доведения от прототипа до коммерческого результата.
