Как отключить автоматический отбор по организации во второй таблице при левом соединении в СКД?

← На главную

При разработке отчетов на базе Системы компоновки данных (СКД) разработчики часто сталкиваются с неочевидным поведением платформы. Вы настраиваете левое соединение (LEFT JOIN) между двумя таблицами — например, таблицей продаж и таблицей затрат на выпуск продукции. В режиме разработчика без отборов все работает идеально: система выводит продажи и подтягивает к ним соответствующие затраты. Однако стоит пользователю установить глобальный отбор по полю Организация, как данные по затратам других организаций полностью исчезают. Левое соединение фактически превращается во внутреннее (INNER JOIN).

В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем поведение оптимизатора СКД и рассмотрим четыре профессиональных способа решения данной задачи.

Выясняем причину: почему СКД «ломает» левое соединение?

Главный виновник такого поведения — встроенный механизм оптимизации СКД, который работает при включенном свойстве «Автозаполнение». Когда СКД анализирует схему компоновки данных, она пытается максимально снизить нагрузку на СУБД и оптимизировать SQL-запросы.

Если пользователь устанавливает в настройках отчета отбор по полю Организация, СКД автоматически транслирует это условие во все виртуальные таблицы запроса, где обнаруживает измерение или реквизит с аналогичным именем или ролью. В результате отбор накладывается на параметры виртуальной таблицы затрат еще до момента физического соединения таблиц в СУБД. Затраты по другим организациям отсекаются на уровне СУБД, и левое соединение соединяет только те строки, которые соответствуют выбранной организации. Наша задача — объяснить СКД, к какой именно таблице должен применяться пользовательский отбор, а какую таблицу трогать не нужно.

Способ 1: Отключение автозаполнения и ручная настройка отборов с помощью фигурных скобок `{}`

Это самый правильный и гибкий способ решения проблемы с точки зрения архитектуры СКД. Мы полностью отключаем «магию» автоматического наложения условий и берем управление отборами в свои руки.

Рассмотрим этот метод по шагам:

Откройте схему компоновки данных и перейдите на вкладку «Наборы данных».
Для вашего набора данных (запроса) снимите флаг «Автозаполнение».
Перейдите в конструктор запроса. Теперь нам нужно явно указать, к какой таблице должен применяться пользовательский отбор. Для этого используется расширение языка запросов СКД — фигурные скобки.
В параметрах виртуальной таблицы, к которой отбор должен применяться (например, продажи), пропишите условие в фигурных скобках. В секции параметров виртуальной таблицы затрат (где отбор не нужен) фигурные скобки для организации оставляйте пустыми.

Посмотрим на пример текста запроса до и после изменений:

Было (с включенным автозаполнением):


ВЫБРАТЬ
    ПродажиОбороты.Организация КАК Организация,
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    ПродажиОбороты.СтоимостьОборот КАК СуммаПродаж,
    ЗатратыОбороты.СтоимостьОборот КАК СуммаЗатрат
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК ПродажиОбороты
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ЗатратыНаВыпуск.Обороты(&НачалоПериода, &КонецПериода) КАК ЗатратыОбороты
        ПО ПродажиОбороты.Номенклатура = ЗатратыОбороты.Номенклатура
        И ПродажиОбороты.Организация = ЗатратыОбороты.Организация

Стало (с отключенным автозаполнением и явным указанием отборов):


ВЫБРАТЬ
    ПродажиОбороты.Организация КАК Организация,
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    ПродажиОбороты.СтоимостьОборот КАК СуммаПродаж,
    ЗатратыОбороты.СтоимостьОборот КАК СуммаЗатрат
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , {Организация.* КАК Организация}) КАК ПродажиОбороты
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ЗатратыНаВыпуск.Обороты(&НачалоПериода, &КонецПериода) КАК ЗатратыОбороты
        ПО ПродажиОбороты.Номенклатура = ЗатратыОбороты.Номенклатура

После этого на вкладке «Компоновка данных» в нижней части конструктора запроса (или вручную на вкладке «Поля» схемы СКД) необходимо добавить доступные поля для выбора, группировки и отборов, так как при отключенном автозаполнении СКД больше не будет делать это автоматически.

Способ 2: Использование псевдонимов полей (переименование)

Если вы не хотите отключать автозаполнение и детально настраивать расширение языка запросов, можно прибегнуть к простому, но эффективному обходному маневру. Мы сделаем так, чтобы СКД физически не видела связи между полем отбора организации в настройках и организацией во второй таблице.

Выполним следующие шаги:

В тексте запроса для таблицы затрат переименуйте поле Организация, назначив ему уникальный псевдоним — например, ОрганизацияЗатрат.
Для первой таблицы поле оставьте с классическим именем Организация.
В схеме компоновки данных перейдите на вкладку «Наборы данных». Найдите в списке полей поле ОрганизацияЗатрат.
В колонке «Ограничения» для поля ОрганизацияЗатрат установите флаг «Ограничение отбора» (запретите использовать это поле в отборах).

Проанализируем ситуацию: теперь, когда пользователь накладывает отбор по полю Организация, компоновщик видит совпадение по имени только в первой таблице (продажи). Во второй таблице поля с таким именем больше нет, а поле ОрганизацияЗатрат защищено от наложения условий. Левое соединение отработает корректно.

Способ 3: Связывание двух независимых наборов данных в СКД

Еще один профессиональный подход, который позволяет полностью уйти от левых соединений внутри одного тяжелого SQL-запроса — это перенос логики соединения на уровень самой платформы 1С.

Разберем этот метод по шагам:

Создайте два отдельных набора данных типа «Запрос» вместо одного общего.
В НаборДанных1 поместите запрос по таблице продаж.
В НаборДанных2 поместите запрос по таблице затрат на выпуск.
Перейдите на вкладку «Связи наборов данных».
Добавьте новую связь, где источником связи будет НаборДанных1, а приемником — НаборДанных2. В качестве условий связи укажите ключевые поля (например, Номенклатура и Организация).

При таком подходе СКД работает следующим образом: она выполняет первый запрос (продажи) с учетом пользовательского отбора по организации. Затем она выполняет второй запрос (затраты) без этого ограничения (или передавая параметры связи порционно), после чего соединяет результаты в оперативной памяти сервера приложений 1С. Это гарантирует сохранение всех строк из левой таблицы.

Способ 4: Наложение отбора на уровне группировок

Если отчет относительно легкий и объем обрабатываемых данных невелик, можно решить задачу без изменения структуры схемы компоновки данных.

Для этого достаточно изменить логику применения отбора пользователем:

Не выводите отбор по организации в шапку отчета как «Быстрый доступ» для общих настроек (где он применяется глобально ко всему запросу).
Настройте этот отбор на уровне конкретной группировки (например, в группировке «Номенклатура» или «Детальные записи»).

Выясним причину, почему это помогает. Когда отбор установлен на уровне группировки, оптимизатор запросов СКД не транслирует его в параметры виртуальных таблиц на уровне СУБД. База данных возвращает полные данные по обеим таблицам, левое соединение отрабатывает без искажений, а сам отбор по организации накладывается процессором компоновки уже в памяти сервера приложений при выводе итогового результата.

Важное примечание: Используйте этот метод с осторожностью на крупных базах данных, так как выборка избыточных данных из СУБД может негативно сказаться на производительности отчета.