Как связать один и тот же набор данных СКД несколько раз с основным по разным полям?

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

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

Давайте разберем типичную ситуацию, с которой столкнулся автор темы, и посмотрим, какие существуют эффективные решения для этой задачи.

Суть проблемы:

Представим, что у нас есть два набора данных:

  1. "Основной": Содержит поля Документ1 и Документ2.
  2. "Дополнительный": Содержит поля Документ и Стулья.

Наша цель — связать набор "Дополнительный" с "Основным" дважды: один раз по полю Основной.Документ1 = Дополнительный.Документ и второй раз по полю Основной.Документ2 = Дополнительный.Документ. При этом мы хотим иметь возможность в вычисляемых полях или ресурсах получать "массив стульев" (или просто поле Стулья) отдельно для Документа1 и отдельно для Документа2. Иными словами, нам нужен результат, аналогичный следующему SQL-запросу:


Основной КАК Основной
ЛЕВОЕ СОЕДИНЕНИЕ Дополнительный КАК Дополнительный1
ПО Основной.Документ1 = Дополнительный1.Документ
ЛЕВОЕ СОЕДИНЕНИЕ Дополнительный КАК Дополнительный2
ПО Основной.Документ2 = Дополнительный2.Документ

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

1. Решение через дублирование наборов данных в СКД (визуальный подход)

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

Как это реализовать:

  1. Переходим на вкладку "Наборы данных" в конструкторе СКД.
  2. Создаем два набора данных, каждый из которых будет представлять собой запрос к источнику "Дополнительный". Например, назовем их Дополнительный1 и Дополнительный2. Важно: несмотря на разные имена, их запросы будут абсолютно идентичны и будут выбирать одни и те же данные из одного и того же источника.
  3. Затем переходим на вкладку "Связи наборов данных".
  4. Создаем первую связь:
    • Источник: Основной
    • Приемник: Дополнительный1
    • Связываемые поля: Основной.Документ1 = Дополнительный1.Документ
    • Тип связи: Левое соединение (если вы хотите видеть строки из Основного набора, даже если нет соответствия в Дополнительном1)
  5. Создаем вторую связь:
    • Источник: Основной
    • Приемник: Дополнительный2
    • Связываемые поля: Основной.Документ2 = Дополнительный2.Документ
    • Тип связи: Левое соединение

Преимущества:

Недостатки:

2. Решение через единый сложный запрос (рекомендуемый подход)

Наиболее эффективным и правильным с точки зрения производительности и контроля является использование одного набора данных типа "Запрос", в тексте которого мы сами формируем все необходимые соединения. Этот подход полностью соответствует логике работы языка запросов SQL и позволяет избежать ограничений визуального конструктора СКД.

Как это реализовать:

  1. Удаляем все отдельные наборы данных ("Основной", "Дополнительный" и их дубликаты), если они были созданы.
  2. Создаем один единственный набор данных типа "Запрос".
  3. В текст запроса помещаем следующий код. Мы будем использовать псевдонимы (Дополнительный1 и Дополнительный2) для каждого экземпляра "Дополнительного" набора данных, который мы присоединяем к "Основному".

ВЫБРАТЬ
    Основной.Документ1,
    Основной.Документ2,
    Дополнительный1.Стулья КАК СтульяПоДокументу1,
    Дополнительный2.Стулья КАК СтульяПоДокументу2
ИЗ
    РегистрСведений.Основной КАК Основной // Предполагаем, что "Основной" это какой-либо регистр или таблица
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Дополнительный КАК Дополнительный1
        ПО Основной.Документ1 = Дополнительный1.Документ
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Дополнительный КАК Дополнительный2
        ПО Основной.Документ2 = Дополнительный2.Документ

Пояснения к запросу:

В результате выполнения этого запроса мы получим одну результирующую таблицу, где для каждой строки "Основного" набора будут присутствовать поля Документ1, Документ2, а также соответствующие им СтульяПоДокументу1 и СтульяПоДокументу2. Если для какого-либо документа не найдется соответствия в "Дополнительном" наборе, соответствующее поле СтульяПоДокументу1 или СтульяПоДокументу2 будет содержать NULL (или НЕОПРЕДЕЛЕНО в 1С).

Преимущества:

3. Подход с объединением запросов (UNION ALL)

На форуме прозвучала идея использовать "Объединить ВСЕ". Этот метод также может быть реализован в рамках одного набора-запроса, но он генерирует итоговую таблицу другой структуры. Он полезен, когда нам нужно получить данные в "плоском" виде, где каждая связь представлена отдельной строкой.

Как это реализовать:

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

ВЫБРАТЬ
    Дополнительный.Документ,
    Дополнительный.Стулья
ПОМЕСТИТЬ ВТ_Дополнительный // Помещаем данные из Дополнительного набора во временную таблицу
ИЗ
    РегистрСведений.Дополнительный КАК Дополнительный
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Основной.Ссылка КАК СсылкаНаОсновнуюЗапись,
    "Документ1" КАК ТипДокумента, // Добавляем поле, чтобы различать тип документа
    Основной.Документ1 КАК ДокументИзОсновного,
    ВТ_Дополнительный.Стулья КАК Стулья
ИЗ
    РегистрСведений.Основной КАК Основной
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Дополнительный КАК ВТ_Дополнительный
        ПО Основной.Документ1 = ВТ_Дополнительный.Документ

ОБЪЕДИНИТЬ ВСЕ // Объединяем результаты двух запросов

ВЫБРАТЬ
    Основной.Ссылка,
    "Документ2",
    Основной.Документ2,
    ВТ_Дополнительный.Стулья
ИЗ
    РегистрСведений.Основной КАК Основной
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Дополнительный КАК ВТ_Дополнительный
        ПО Основной.Документ2 = ВТ_Дополнительный.Документ

Результат этого запроса:

Если у нас есть одна запись в "Основном" наборе, и оба Документ1 и Документ2 имеют соответствия в "Дополнительном", то в результате мы получим две строки:

Преимущества:

Недостатки:

Важные технические моменты и потенциальные риски в СКД

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

1. Превращение ЛЕВОГО СОЕДИНЕНИЯ во ВНУТРЕННЕЕ СОЕДИНЕНИЕ (LEFT JOIN в INNER JOIN)

Это одна из самых частых "ловушек" в СКД при использовании стандартных связей наборов данных. Если мы устанавливаем Левое соединение между наборами, а затем в настройках отчета пользователь накладывает отбор на поле из подчиненного набора (приемника), СКД может автоматически преобразовать это левое соединение во внутреннее. Что это значит на практике? Если для какой-то записи основного набора нет соответствующей записи в подчиненном наборе, и мы отбираем по полю из этого подчиненного набора, то такая запись просто исчезнет из отчета.

Пример: У нас есть Основной набор с Документом1, и Дополнительный1, связанный с ним через Левое соединение. Если мы добавим отбор Дополнительный1.Стулья > 0, то все строки Основного набора, для которых нет соответствующей записи в Дополнительный1 (и, следовательно, Дополнительный1.Стулья равно NULL), пропадут из отчета. Это происходит потому, что NULL не может быть больше нуля, и СКД оптимизирует запрос, превращая ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ.

Как избежать:

  1. Использовать единый сложный запрос (рекомендуемый подход): В этом случае СКД не изменяет тип соединения, так как запрос уже сформирован нами. Мы полностью контролируем логику.
  2. Добавлять условие отбора на "ИЛИ NULL": Если все же используем связи наборов данных, и отбор по подчиненному полю критичен, то можно использовать условие отбора вида (Дополнительный1.Стулья > 0 ИЛИ Дополнительный1.Стулья ЕСТЬ NULL). Однако это не всегда удобно и может быть неинтуитивно для пользователя.
  3. Использовать вычисляемое поле: Можно создать вычисляемое поле, например, ЕстьСтулья1 типа Булево, со значением НЕ Дополнительный1.Стулья ЕСТЬ NULL, и уже по нему делать отбор.

2. Корректный расчет итогов при дублировании записей

Если в подчиненном наборе данных существует несколько записей, соответствующих одной записи основного набора, это может привести к дублированию строк в промежуточных результатах, что, в свою очередь, искажает расчет итогов (например, суммы, количества) по ресурсам.

Пример: Пусть один Основной.Документ1 связан с двумя записями в Дополнительный1. Если мы затем присоединяем Дополнительный2, то для одной записи Основного набора мы можем получить несколько строк результата, в которых данные по Дополнительный2.Стулья будут продублированы. При расчете итоговой суммы по полю СтульяПоДокументу2 эта сумма окажется завышенной.

Как решить:

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


// Пример использования ВычислитьВыражениеСГруппировкойМассив для ресурса "СуммаСтульев1"
Сумма(ВЫЧИСЛИТЬВЫРАЖЕНИЕСГРУППИРОВКОЙМАССИВ("СтульяПоДокументу1", "Документ1"))

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

Заключение

Хотя визуальные связи наборов данных в СКД предлагают простой способ объединения информации, их ограниченность при многократном связывании одного и того же источника по разным полям, а также потенциальные проблемы с производительностью и изменением типов соединений, делают их не всегда оптимальным выбором. Наиболее надежным и производительным решением является использование единого набора данных типа "Запрос", где все необходимые соединения с псевдонимами прописаны явно в SQL-коде. Этот подход дает нам полный контроль над логикой формирования данных и позволяет избежать большинства подводных камней СКД.

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

Читайте также

5 материалов по теме
  1. 01 Как настроить связь наборов данных в СКД по определенному типу документа?
  2. 02 Зачем использовать несколько наборов данных в СКД вместо обычного соединения в запросе?
  3. 03 Как связать наборы данных в СКД по условию «ИЛИ»?
  4. 04 Как реализовать условную связь между наборами данных в СКД 1С?
  5. 05 Как связать один набор данных несколько раз в СКД и оптимизировать работу отчета
← На главную