Как получить всю иерархию комплектующих номенклатуры без запроса в цикле?

← На главную

Разберем классическую задачу платформы 1С: как развернуть многоуровневую спецификацию или комплектацию до конечных узлов. Проблема возникает, когда мы из нескольких номенклатур собираем одну, но сами эти комплектующие тоже состоят из других деталей. Например, есть станок, который собран из генератора и станины; генератор состоит из платы и проводов; плата — из конденсаторов и так далее. Количество таких вложений заранее неизвестно.

В языке запросов 1С отсутствует полноценная поддержка рекурсивных общих табличных выражений (аналог WITH RECURSIVE в SQL). Поэтому просто написать один элегантный рекурсивный запрос не получится. Начинающие разработчики часто прибегают к самому очевидному, но крайне неоптимальному решению — запросу в цикле. Проанализируем ситуацию и рассмотрим правильные методы обхода этого ограничения, которые обеспечат высокую производительность.

Способ 1. Итеративный запрос по уровням (массивы)

Если мы не можем получить все уровни одним запросом, мы можем получать их послойно, при этом количество обращений к базе данных будет равно максимальной глубине дерева (обычно это 5-10 уровней), а не количеству узлов.

Алгоритм действий следующий:

Первым запросом получаем дочерние элементы для корневой номенклатуры и помещаем их в массив.
В цикле выполняем запрос, который ищет комплектующие, где родительская номенклатура находится в этом массиве (условие В (&МассивНоменклатуры)).
Найденные дочерние элементы становятся новым массивом для следующей итерации.
Цикл прерывается, когда запрос возвращает пустой результат.

Таким образом, для сложного станка из 10 000 элементов с глубиной дерева в 5 уровней сервер выполнит всего 5 запросов к БД. Это отличный компромисс между сложностью кода и производительностью.

Способ 2. Ограничение максимальной глубины и использование левых соединений

На практике уровень вложенности комплектаций редко бывает бесконечным и почти никогда не превышает 10-15 уровней. Зная это, мы можем написать один большой запрос, в котором таблица комплектующих будет присоединяться сама к себе левым соединением (ЛЕВОЕ СОЕДИНЕНИЕ) фиксированное количество раз.

Посмотрим на пример запроса, который попытался реализовать автор темы, объединяя данные из документа КомплектацияНоменклатуры:


ВЫБРАТЬ 
    КомплектацияНоменклатурыКомплектующие.Номенклатура КАК Номенклатура 
ПОМЕСТИТЬ ВсяНоменклатураНеоднократнойСборки 
ИЗ 
    Документ.КомплектацияНоменклатуры.Комплектующие КАК КомплектацияНоменклатурыКомплектующие 
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.КомплектацияНоменклатуры КАК КомплектацияНоменклатуры 
    ПО КомплектацияНоменклатурыКомплектующие.Номенклатура = КомплектацияНоменклатуры.Номенклатура;

ВЫБРАТЬ 
    ВсяНоменклатураНеоднократнойСборки.Номенклатура КАК Номенклатура 
ПОМЕСТИТЬ ТекущаяНоменклатураНеоднократнойСборки 
ИЗ 
    Документ.КомплектацияНоменклатуры КАК КомплектацияНоменклатуры 
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.КомплектацияНоменклатуры.Комплектующие КАК КомплектацияНоменклатурыКомплектующие 
    ПО КомплектацияНоменклатуры.Ссылка = КомплектацияНоменклатурыКомплектующие.Ссылка 
ГДЕ 
    КомплектацияНоменклатуры.Номенклатура = &Номенклатура;

ВЫБРАТЬ 
    КомплектацияНоменклатурыКомплектующие.Номенклатура КАК Номенклатура 
ИЗ 
    ТекущаяНоменклатураНеоднократнойСборки КАК ТекущаяНоменклатураНеоднократнойСборки 
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.КомплектацияНоменклатуры КАК КомплектацияНоменклатуры 
    ПО ТекущаяНоменклатураНеоднократнойСборки.Номенклатура = КомплектацияНоменклатуры.Номенклатура 
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.КомплектацияНоменклатуры.Комплектующие КАК КомплектацияНоменклатурыКомплектующие 
    ПО КомплектацияНоменклатуры.Ссылка = КомплектацияНоменклатурыКомплектующие.Ссылка

Хотя этот конкретный пример ограничен по уровням вложенности и может потерять ветви (как справедливо заметили участники обсуждения), сама идея жизнеспособна. Сервер СУБД выполняет задачу за один проход, возвращая «плоскую» таблицу со всеми уровнями. Затем эту таблицу легко обойти в коде и свернуть в дерево.

Способ 3. Выгрузка данных в память и программная рекурсия

Если справочник номенклатуры и регистры спецификаций не являются экстремально огромными, лучшим решением будет перенос расчета в оперативную память. Мы избавляемся от запросов в цикле путем одного-единственного запроса.

Разберем по шагам этот подход:

Пишем один запрос, который вытаскивает все записи спецификаций (или с отбором по корневой папке) и выгружает результат в ТаблицаЗначений.
Для ускорения поиска индексируем нужные колонки в таблице значений или перекладываем данные в структуру/соответствие.
Пишем классическую рекурсивную функцию в программном коде 1С.

Важно понимать, что в этом случае наша рекурсия обращается не к базе данных (что вызывает сетевые задержки и блокировки), а к оперативной памяти сервера приложений. Поиск по закэшированной таблице значений выполняется за миллисекунды.

Способ 4. Поддержание транзитивного замыкания (Closure Table)

В крупных типовых конфигурациях (например, 1С:ERP Управление предприятием) проблему часто решают еще на этапе записи данных. При проведении документа комплектации или утверждении спецификации система сразу рассчитывает всю иерархию (кто во что входит на всех уровнях).

Рассчитанный результат записывается в специальный служебный регистр сведений, где хранятся пары «Конечный узел — Корневой узел». При таком архитектурном подходе для получения всех деталей станка нам понадобится всего один простейший запрос к этому служебному регистру с отбором по корневой номенклатуре. Никаких соединений, рекурсий или циклов при чтении данных не требуется вообще. Этот паттерн идеально подходит для высоконагруженных систем.

Способ 5. Перенос логики на сторону SQL Server

Если мы работаем с колоссальными объемами данных, а типовые средства 1С не справляются, логику разузлования можно делегировать самой СУБД. На стороне SQL-сервера (например, MS SQL Server или PostgreSQL) создается хранимая процедура или представление (View), использующее нативную конструкцию рекурсии.

Из платформы 1С к этому объекту можно обратиться напрямую, используя внешние источники данных или технологию ADO. Этот метод работает молниеносно и использует все возможности СУБД, однако следует помнить, что он нарушает стандарты кроссплатформенности 1С, так как жестко привязывает ваше решение к конкретной системе управления базами данных.

Подведем итог: столкнувшись с задачей разузлования, никогда не используйте запрос в цикле. В зависимости от объема данных и требований к производительности выбирайте либо итеративные запросы по массиву родителей, либо программную рекурсию по выгруженной в память ТаблицаЗначений. Для масштабных архитектурных решений лучшим выходом станет создание служебного регистра для хранения транзитивного замыкания спецификаций.