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