Приветствуем вас, коллеги! Сегодня мы детально разберем крайне интересную и частую задачу, с которой сталкиваются разработчики при работе с иерархическими справочниками в платформе 1С:Предприятие. Представьте ситуацию: у нас есть справочник с иерархией групп и элементов. Уровень вложенности групп может быть абсолютно произвольным. У элементов справочника присутствует булевский реквизит Особенный.
На входе у нас есть таблица значений, содержащая одну колонку — Группа. В этой колонке могут находиться группы любого уровня иерархии (как корневые, так и глубоко вложенные). Наша цель — строго в рамках запроса получить таблицу из двух колонок: исходная Группа и булевский признак ЕстьОсобенныйЭлемент. Этот признак должен принимать значение ИСТИНА, если в самой группе или где-то глубоко внутри ее поддерева (на любом уровне вложенности) существует хотя бы один элемент с флагом Особенный.
Основная сложность заключается в том, что язык запросов 1С не позволяет использовать конструкцию В ИЕРАРХИИ внутри условия связи таблиц (ПО). Мы не можем просто соединить исходную таблицу групп со справочником по иерархическому условию. Ниже мы подробно рассмотрим четыре эффективных метода решения этой задачи, начиная от продвинутых SQL-алгоритмов и заканчивая встроенными механизмами платформы 1С.
Этот метод является наиболее академическим и мощным, поскольку он позволяет решить задачу в рамках одного пакетного запроса без динамического формирования текста и без использования внешних механизмов вроде СКД. Идея транзитивного замыкания заключается в построении полной карты путей «Предок — Потомок» для всех уровней иерархии справочника.
Вместо медленного пошагового перебора уровней (один шаг — один уровень), мы применим алгоритм двоичного удвоения путей. Каждое каскадное соединение временных таблиц будет удваивать максимальную глубину покрываемой иерархии. Всего за 4 шага мы сможем гарантированно покрыть иерархию глубиной до 16 уровней ($2^4 = 16$), чего с избытком хватает для 99% реальных баз данных.
Рассмотрим по шагам, как построить такой пакет запросов:
Шаг 1. Получение прямых связей (длина пути = 1). Мы выбираем все элементы и группы справочника, у которых заполнен родитель. Это наши базовые связи.
// Помещаем в ВТ_Связи_1 связи «Потомок -> Непосредственный родитель»
ВЫБРАТЬ
Спр.Ссылка КАК Потомок,
Спр.Родитель КАК Родитель
ПОМЕСТИТЬ ВТ_Связи_1
ИЗ
Справочник.МойСправочник КАК Спр
ГДЕ
Спр.Родитель <> ЗНАЧЕНИЕ(Справочник.МойСправочник.ПустаяСсылка)
;
Шаг 2. Удвоение путей (длина пути до 2). Мы соединяем таблицу связей саму с собой. Если у нас есть связь А -> Б и Б -> В, то в результате соединения мы получим прямую связь А -> В.
ВЫБРАТЬ
Связи1.Потомок КАК Потомок,
Связи1.Родитель КАК Родитель
ПОМЕСТИТЬ ВТ_Связи_2
ИЗ
ВТ_Связи_1 КАК Связи1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Связи1.Потомок,
Связи2.Родитель
ИЗ
ВТ_Связи_1 КАК Связи1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Связи_1 КАК Связи2
ПО Связи1.Родитель = Связи2.Потомок
;
Шаг 3. Второе удвоение (длина пути до 4). Повторяем ту же операцию над таблицей ВТ_Связи_2.
ВЫБРАТЬ
Связи2.Потомок КАК Потомок,
Связи2.Родитель КАК Родитель
ПОМЕСТИТЬ ВТ_Связи_4
ИЗ
ВТ_Связи_2 КАК Связи2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Связи2_1.Потомок,
Связи2_2.Родитель
ИЗ
ВТ_Связи_2 КАК Связи2_1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Связи_2 КАК Связи2_2
ПО Связи2_1.Родитель = Связи2_2.Потомок
;
Шаг 4. Третье удвоение (длина пути до 8). Повторяем операцию над таблицей ВТ_Связи_4. При необходимости аналогичным образом можно сделать таблицу ВТ_Связи_16.
ВЫБРАТЬ
Связи4.Потомок КАК Потомок,
Связи4.Родитель КАК Родитель
ПОМЕСТИТЬ ВТ_Связи_8
ИЗ
ВТ_Связи_4 КАК Связи4
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Связи4_1.Потомок,
Связи4_2.Родитель
ИЗ
ВТ_Связи_4 КАК Связи4_1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Связи_4 КАК Связи4_2
ПО Связи4_1.Родитель = Связи4_2.Потомок
;
Шаг 5. Финальное сопоставление. Теперь у нас есть полная таблица связей ВТ_Связи_8, где для любого элемента (или группы) сопоставлены абсолютно все его родители любого уровня вложенности. Нам остается лишь соединить исходную таблицу групп с этой картой связей и наложить фильтр по признаку Особенный.
// Предположим, исходный список групп уже передан в ВТ_ИсходныеГруппы
ВЫБРАТЬ
ИсходныеГруппы.Группа КАК Группа,
МАКСИМУМ(ВЫБОР
КОГДА СпрЭлементы.Особенный = ИСТИНА
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ) КАК ЕстьОсобенныйЭлемент
ИЗ
ВТ_ИсходныеГруппы КАК ИсходныеГруппы
// Соединяем по родителю любого уровня
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Связи_8 КАК Связи
ПО ИсходныеГруппы.Группа = Связи.Родитель
// Соединяем с элементами справочника для проверки признака
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.МойСправочник КАК СпрЭлементы
ПО Связи.Потомок = СпрЭлементы.Ссылка
СГРУППИРОВАТЬ ПО
ИсходныеГруппы.Группа
;
Этот метод работает невероятно быстро, так как СУБД отлично оптимизирует соединения временных таблиц по индексам. Единственное ограничение — нужно заранее заложить в пакет запросов количество шагов удвоения, соответствующее максимальной глубине иерархии в вашей базе данных.
Если задача позволяет вывести результат через СКД (или программно выгрузить результат схемы компоновки данных в Таблицу Значений), мы можем переложить всю сложную работу по обходу дерева на саму платформу 1С. В СКД встроен мощнейший оптимизированный механизм связи наборов данных, поддерживающий иерархию.
Проанализируем настройку схемы по шагам:
Группа.Особенный и их непосредственных родителей:
ВЫБРАТЬ
Ссылка КАК Элемент,
Родитель КАК Родитель
ИЗ
Справочник.МойСправочник
ГДЕ
Особенный = ИСТИНА
И ЭтоГруппа = ЛОЖЬ
НаборДанных2.Благодаря настройке проверки иерархии, СКД самостоятельно выполнит рекурсивный поиск вниз по дереву групп и сопоставит исходную группу со всеми вложенными особыми элементами. На выходе в детальных записях мы получим список сопоставлений, который останется лишь сгруппировать.
Платформа 1С содержит встроенный в язык запросов механизм расчета иерархических итогов. Мы можем использовать его для автоматического "поднятия" признака Особенный с нижних уровней на верхние. Важное ограничение: расчет итогов нельзя использовать внутри временных таблиц в пакете запросов, поэтому этот запрос должен выполняться отдельно.
Разберем пример такого запроса:
ВЫБРАТЬ
Спр.Ссылка КАК Ссылка,
МАКСИМУМ(ВЫБОР
КОГДА Спр.Особенный = ИСТИНА
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ) КАК ЕстьОсобенный
ИЗ
Справочник.МойСправочник КАК Спр
ГДЕ
Спр.Ссылка В ИЕРАРХИИ(&СписокИсходныхГрупп)
СГРУППИРОВАТЬ ПО
Спр.Ссылка
ИТОГИ МАКСИМУМ(ЕстьОсобенный) ПО Ссылка ИЕРАРХИЯ
При обходе результатов этого запроса во встроенном языке с помощью метода Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам), платформа предоставит нам дерево. На уровне каждой исходной группы из параметра &СписокИсходныхГрупп в группировочном поле ЕстьОсобенный будет рассчитан максимум по всей ее ветке, включая все вложенные элементы.
Программно обойти такое дерево и заполнить плоскую таблицу значений не составит труда, при этом основную сложную работу по расчету иерархии СУБД возьмет на себя.
Участники обсуждения упоминали использование системного поля ПолныйКод (или ПолноеНаименование). Этот метод основан на том, что полный код иерархического справочника содержит коды всех вышестоящих родителей, разделенные косой чертой, например: "00001/00015/00124".
Теоретически, мы могли бы связать исходную группу с подчиненными элементами по условию подобия текстовых строк:
ВЫБРАТЬ
ИсходныеГруппы.Группа,
ИСТИНА КАК ЕстьОсобенный
ИЗ
ВТ_ИсходныеГруппы КАК ИсходныеГруппы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.МойСправочник КАК СпрЭлементы
ПО СпрЭлементы.ПолныйКод ПОДОБНО ИсходныеГруппы.Группа.ПолныйКод + "/%"
ГДЕ
СпрЭлементы.Особенный = ИСТИНА
Почему мы настоятельно НЕ рекомендуем использовать этот метод на реальных базах данных?
ПОДОБНО (особенно со знаком процента в начале или середине шаблона) полностью исключает использование индексов СУБД. На справочниках объемом более нескольких тысяч элементов такой запрос вызовет полное сканирование таблицы (Table Scan) и приведет к зависанию системы.Мы проанализировали четыре различных подхода к решению этой непростой задачи. Какой же метод выбрать?