При разработке отчетов или механизмов выгрузки данных программисты часто сталкиваются с задачей получения данных в иерархическом виде. Классический пример — документ и его табличная часть. Мы хотим видеть одну строку с реквизитами «шапки» (номер, дата, контрагент) и вложенный набор строк с товарами. В этой статье мы разберем, как эта задача решается средствами платформы 1С и какие механизмы предоставляют современные системы управления базами данных (СУБД), такие как MS SQL Server и PostgreSQL.
Начнем с того, что диалект языка запросов 1С:Предприятие обладает уникальной особенностью, которой нет во многих классических диалектах SQL — поддержкой вложенных таблиц. Рассмотрим ситуацию: нам нужно получить список документов и для каждого документа сразу получить список его товаров. Проанализируем, как это выглядит в коде 1С:
ВЫБРАТЬ
Ссылка.Номер КАК НомерДока,
Ссылка.Дата КАК ДатаДока,
Состав (
Номенклатура,
Количество,
Цена
) КАК ТаблицаТоваров
ИЗ
Документ.РеализацияТоваровУслуг КАК Ссылка
ГДЕ
Ссылка.Проведен = ИСТИНА
В данном примере поле ТаблицаТоваров будет содержать объект типа РезультатЗапроса для каждой строки основного запроса. Это очень удобно для последующей обработки в коде на встроенном языке. Однако важно понимать, что на уровне СУБД этот запрос трансформируется платформой либо в серию последовательных запросов, либо в один сложный запрос с последующей постобработкой данных на сервере приложений 1С.
Если мы работаем с «чистым» SQL или хотим максимально оптимизировать выборку, используется метод соединения таблиц. Рассмотрим пример запроса к базе данных напрямую. Выясним причину, почему этот метод считается самым производительным, несмотря на избыточность данных.
SELECT
Doc.Number,
Doc.Date,
Items.LineNumber,
Items.ItemName,
Items.Quantity
FROM
Document_Sales AS Doc
LEFT JOIN
Document_Sales_Items AS Items ON Doc.ID = Items.RefID
WHERE
Doc.Posted = 1
ORDER BY
Doc.Number, Items.LineNumber
Проанализируем ситуацию: в результате мы получим «плоскую» таблицу. Если в документе 10 товаров, то данные шапки (номер и дата) продублируются 10 раз. Это та самая ситуация, о которой упоминалось в обсуждении — «данные по шапке нагрузкой». Платформа 1С умеет элегантно обходить это дублирование при использовании обхода по группировкам:
ВыборкаШапка = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаШапка.Следующий() Цикл
// Работаем с данными шапки
ВыборкаСтроки = ВыборкаШапка.Выбрать();
Пока ВыборкаСтроки.Следующий() Цикл
// Работаем с товарами
КонецЦикла;
КонецЦикла;
Еще один способ структурировать данные — использование предложения ИТОГИ в 1С или ROLLUP в SQL. Это позволяет добавить в результирующий набор данных промежуточные строки, которые агрегируют информацию. Разберем по шагам, чем это отличается от вложенных таблиц:
В MS SQL Server конструкция GROUP BY WITH ROLLUP позволяет получить итоговые значения, но она редко используется для формирования именно «шапки и строк» в том понимании, которое нужно программисту 1С. Скорее, это инструмент для аналитических отчетов.
Разберем продвинутые возможности современных СУБД, которые позволяют максимально приблизиться к понятию вложенности. Если вы пишете внешний запрос к базе данных, отличным решением будет упаковка табличной части в формат JSON (доступно в MS SQL Server 2016+ и PostgreSQL).
Посмотрим на пример для MS SQL Server:
SELECT
Doc.Number,
(SELECT ItemName, Quantity
FROM Document_Sales_Items AS Items
WHERE Items.RefID = Doc.ID
FOR JSON PATH) AS ItemsJSON
FROM
Document_Sales AS Doc
В этом случае поле ItemsJSON будет содержать полноценный массив объектов. Это позволяет получить одну строку на один документ, сохранив всю детальную информацию внутри одного поля. Аналогично в PostgreSQL можно использовать функцию json_agg(), которая агрегирует связанные строки в JSON-массив.
Иногда задача стоит проще: не нужно получать полноценную таблицу, достаточно вывести список товаров через запятую в одной колонке. Для этого в SQL используются функции агрегации строк. Разберем, как это работает:
В MS SQL Server это функция STRING_AGG, а в более старых версиях — хитрая конструкция с FOR XML PATH(''). В PostgreSQL — функция string_agg(field, delimiter). Это визуально имитирует вложенность, позволяя отчету оставаться компактным и информативным без создания сложных структур.
Рассмотрим специфический прием, который иногда применяется для формирования сложных печатных форм. Мы можем объединить данные шапки и данные строк в один набор, добавив техническое поле для сортировки.
SELECT Number AS Поле1, Date AS Поле2, 1 AS ТипСтроки, ID AS Ключ FROM Document_Sales
UNION ALL
SELECT ItemName, CAST(Quantity AS VARCHAR), 2, RefID FROM Document_Sales_Items
ORDER BY Ключ, ТипСтроки
В таком запросе при сортировке по ключу (ID документа) и типу строки сначала будет идти «шапка», а затем все связанные с ней «строки». Это позволяет имитировать структуру документа в одном плоском результате, что удобно для последовательного вывода в текстовый файл или лог.
Подводя итог нашему анализу, можно выделить следующие правила выбора архитектуры запроса:
Мы проанализировали основные подходы и выяснили, что хотя «чистый» классический SQL не поддерживает вложенные таблицы в том виде, к которому привыкли разработчики 1С, современные расширения и правильное использование соединений позволяют эффективно решать эту задачу.