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