Каталог решений - Иерархия без "В ИЕРАРХИИ"

Введение

Судя по статье Что делает "В ИЕРАРХИИ" в запросе?, интерес к теме работы с иерархическими данными в запросах еще не исчез. В то же время, решения, приведенные в статье Уровни, глубина, прародители, циклы и аналоги запросом, нуждаются в уточнениях и дополнениях.

Для этого есть несколько причин. Во-первых, запросы в статье Уровни, глубина, прародители, циклы и аналоги запросом были написаны как пример применения более общего приема "транзитивного замыкания". Но оказалось, что в частых случаях некоторых конкретных задач, таких как определения глубины, уровней и прародителей в справочниках, запросы могут быть реализованы эффективнее.  Во-вторых, в исходной статье для удобства были приведены не запросы, а универсальные функции для их построения. Из-за чего у многих сложилось неверное представление, что речь идет об ограниченно используемой или не о чисто запросной технике. Это желательно поправить. В третьих, есть смысл посмотреть на проблему шире, с точки зрения того, как еще можно представлять иерархию. В четвертых, в запросах с того времени появились новые конструкции, одна из которых оказалась полезной при работе с иерархиями.

Итак, при представлении иерархических справочников в платформе 1С:Предприятие, хранится только "остов" отношения иерархии, а именно, связь конкретного элемента с его непосредственным родителем. Из-за этого при поиске всех потомков одного элемента на уровне СУБД фактически используется цикл из нескольких последовательных запросов (Что делает "В ИЕРАРХИИ" в запросе?). При этом готовой конструкции, позволяющей выбрать всех предков одного элемента, вообще нет. В литературе способ, используемый в платформе, называется Adjacency List (AL).

Для того, чтобы быстро решать все задачи на иерархию, требуется "развертка" иерархии, где отношение непосредственно определено для любой пары элементов, независимо от того, находятся ли они на смежных уровнях или нет. В теории используется минимум три разновидности "развернутого" представления иерархии: использование полного списка потомков CT (Closure Table), путей к элементам MP (Matherialized Path), вложенных множеств NS (Nested Sets). Есть еще всевозможные модификации перечисленных способов: Реализация иерархии — объединение Adjacency List и Materialized Path через one-to-many, Строим Nested Set дерево без рекурсии.

Естественно думать, что развернутое представление иерархии должно храниться в СУБД. Это порождает проблему усложнения процедуры записи при изменениях в структуре справочника.  Например, изменение подчинения группы элементов требует переписывания путей во всех элементах группы. Но, вообще говоря, развернутое представление можно получать и "на лету" — непосредственно перед построением отчета. Это хотя и может несколько замедлить его выполнение, но не требует хранения избыточных данных в СУБД и их перестроения. В дальнейшем основной акцент изложения сделан именно на этом варианте. К тому же, этот способ не исключает первого, то есть того, что при необходимости развернутая иерархия, полученная приводимыми далее запросами, будет сохраняться в СУБД.

1. Использование полного списка потомков (Closure Table)

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

Например, для структуры, изображенной на Фиг.1,

AL задается таблицей Таб.1,

а CT — таблицей Таб.2.

Получение таблицы CT по таблице AL одним пакетным запросом было описано в [Уровни, глубина, прародители, циклы и аналоги запросом]. Предлагалось использовать следующий запрос: