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