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