При построении отчетов или обработок разработчики часто сталкиваются с необходимостью объединить данные двух регистров — например, остатки на складах и продажи за период — с расчетом итогов по иерархии номенклатуры. Нередко для получения единого поля номенклатуры при полном соединении используется выражение ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ или функция ЕСТЬNULL. Однако после добавления такого выражения иерархия в результатах запроса неожиданно перестает работать. Разберем, почему платформа 1С ведет себя подобным образом и как правильно реализовать запрос для получения иерархических итогов.
Для корректного решения проблемы проанализируем две основные причины, из-за которых расчет иерархии может не отрабатывать:
Номенклатура.Ссылка). Когда мы подменяем прямое ссылочное поле выражением ВЫБОР КОГДА ... или ЕСТЬNULL(...), платформа воспринимает результат как составное или вычисляемое значение. При этом внутренняя связь со структурой дерева метаданных (связь элемента с родителем Родитель) теряется. Даже если попытаться явно указать тип с помощью конструкции ВЫРАЗИТЬ(Выражение КАК Справочник.Номенклатура), механизм построения иерархии не восстановится. Платформа не выдаст синтаксической ошибки, а просто выполнит расчет как для обычной плоской группировки.ПО ОБЩИЕ, Номенклатура, платформа построит лишь стандартные группировочные итоги. Для включения иерархических узлов обязательно требуется ключевое слово ИЕРАРХИЯ — ПО ОБЩИЕ, Номенклатура ИЕРАРХИЯ.Самый наглядный способ сохранить иерархию — сделать ведущей таблицей сам справочник, к которому затем левым соединением подключить таблицы остатков и оборотов. В секции выборки мы берем прямое поле Справочник.Номенклатура.Ссылка, по которому платформа гарантированно сможет построить дерево групп и элементов.
Чтобы исключить из отчета позиции справочника, по которым не было ни остатков, ни движений, добавим условие в секцию ГДЕ:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток,
ЕСТЬNULL(ПродажиОбороты.КоличествоОборот, 0) КАК КоличествоОборот
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, ) КАК ТоварыНаСкладахОстатки
ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
ПО СпрНоменклатура.Ссылка = ПродажиОбороты.Номенклатура
ГДЕ
НЕ ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) = 0
ИЛИ НЕ ЕСТЬNULL(ПродажиОбороты.КоличествоОборот, 0) = 0
ИТОГИ
СУММА(КоличествоОстаток),
СУММА(КоличествоОборот)
ПО
ОБЩИЕ,
Номенклатура ИЕРАРХИЯ
Такой подход полностью решает задачу формирования иерархии. Однако на больших объемах данных в высоконагруженных базах соединение со всей таблицей справочника и последующая фильтрация могут создавать существенную нагрузку на СУБД.
Рассмотрим более производительный и профессиональный шаблон. Использование ПОЛНОЕ СОЕДИНЕНИЕ для виртуальных таблиц регистров не рекомендуется стандартами разработки 1С, так как на уровне SQL это приводит к тяжелым операциям. Оптимальнее собрать список нужных ссылок через ОБЪЕДИНИТЬ ВСЕ, сгруппировать их и связать со справочником.
Разберем по шагам построение такого запроса:
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток,
0 КАК КоличествоОборот
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, ) КАК ТоварыНаСкладахОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПродажиОбороты.Номенклатура,
0,
ПродажиОбороты.КоличествоОборот
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
СУММА(ВТ_Данные.КоличествоОстаток) КАК КоличествоОстаток,
СУММА(ВТ_Данные.КоличествоОборот) КАК КоличествоОборот
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Данные КАК ВТ_Данные
ПО СпрНоменклатура.Ссылка = ВТ_Данные.Номенклатура
СГРУППИРОВАТЬ ПО
СпрНоменклатура.Ссылка
ИТОГИ
СУММА(КоличествоОстаток),
СУММА(КоличествоОборот)
ПО
ОБЩИЕ,
Номенклатура ИЕРАРХИЯ
В этом варианте внутреннее соединение ВНУТРЕННЕЕ СОЕДИНЕНИЕ оставляет только те элементы справочника, по которым были данные. При этом поле СпрНоменклатура.Ссылка сохраняет исходные метаданные, и конструкция ИТОГИ ПО Номенклатура ИЕРАРХИЯ отрабатывает штатно и быстро.
Если запрос написан правильно, но при выводе в таблицу или дерево иерархия по-прежнему отсутствует, необходимо проверить код обхода выборки. При стандартном линейном вызове метода Выбрать() иерархические уровни не будут разворачиваться в дерево.
Для корректного отображения иерархии используем параметр ОбходРезультатаЗапроса.ПоГруппировкамСИерархией:
РезультатЗапроса = Запрос.Выполнить();
// Вариант 1. Выгрузка напрямую в Дерево Значений
ДеревоРезультата = РезультатЗапроса.Выгрузить(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);
// Вариант 2. Ручной иерархический обход выборки
ВыборкаИерархия = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);
Пока ВыборкаИерархия.Следующий() Цикл
// Обработка узлов и элементов иерархии
КонецЦикла;
Если задача заключается в формировании отчета для пользователя, рекомендуется отказаться от ручного секционирования ИТОГИ в тексте запроса и перенести задачу в СКД. Система компоновки данных умеет самостоятельно восстанавливать иерархию для ссылочных полей через настройки группировки (выбор типа группировки «Иерархия» или «Только иерархия»), что исключает необходимость писать сложные объединения вручную.