Как связать наборы данных в СКД по условию «ИЛИ»?

← На главную

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

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

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

Почему стандартная настройка связей в СКД не решает задачу?

Прежде чем перейти к решениям, разберем типичные ошибки и ограничения платформы, с которыми сталкиваются разработчики при попытке реализовать такую связь напрямую:

Использование нескольких строк связи. Если добавить в таблицу «Связи наборов данных» две строки (первая: ЗаказКлиента = Договор, вторая: ЗаказКлиента = Ссылка), СКД транслирует это в SQL-запрос или внутренний алгоритм объединения как Условие1 И Условие2. Результат всегда будет пустым, так как одно поле не может быть одновременно и ссылкой на справочник договоров, и ссылкой на документ.
Ограничения в колонке «Условие связи». Попытка прописать в выражении связи или условии связи проверку типов (например, ТИПЗНАЧЕНИЯ(ЗаказКлиента) = ТИП(Документ.РеализацияТоваровУслуг)) часто приводит к ошибкам компиляции СКД. Платформа накладывает жесткое ограничение: в выражении связи можно использовать только поля источника (левой таблицы). Обращаться к полям приемника (правой таблицы) внутри сложных логических условий нельзя.

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

Решение 1. Создание синтетического поля связи через ОБЪЕДИНИТЬ ВСЕ (Рекомендуемое)

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

Для этого в запросе Набора 2 мы развернем альтернативные условия связи в строки с помощью оператора ОБЪЕДИНИТЬ ВСЕ. Мы создадим виртуальное поле связи, в которое поместим и договоры, и документы.

Посмотрим на пример построения такого запроса для Набора 2:


ВЫБРАТЬ
    Реализация.Ссылка КАК ПолеСвязи,
    Реализация.Ссылка КАК ДокументСсылка,
    Реализация.ДоговорКонтрагента КАК Договор,
    Реализация.СуммаДокумента КАК СуммаОплаты
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Реализация.ДоговорКонтрагента КАК ПолеСвязи,
    Реализация.Ссылка КАК ДокументСсылка,
    Реализация.ДоговорКонтрагента КАК Договор,
    Реализация.СуммаДокумента КАК СуммаОплаты
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация

Что мы сделали в этом запросе? Мы получили дублирующиеся строки для каждой реализации: в первой строке в поле ПолеСвязи находится ссылка на сам документ, во второй строке — ссылка на договор. Теперь в настройках связей наборов данных СКД нам достаточно указать всего одну связь:

Источник связи: Набор 1
Приемник связи: Набор 2
Выражение источника: ЗаказКлиента
Выражение приемника: ПолеСвязи

Этот метод работает чрезвычайно быстро, не перегружает оперативную память сервера приложений 1С и полностью имитирует логику соединения по условию «ИЛИ».

Решение 2. Использование структуры «Набор данных - Объединение»

Если наборы данных имеют слишком сложную структуру, или выгружаются из разных СУБД (или внешних источников данных), и объединять их внутри одного SQL-запроса нецелесообразно, можно прибегнуть к возможностям самого компоновщика СКД. Разберем этот метод по шагам:

Создадим корневой элемент наборов данных с типом Набор данных - Объединение. Назовем его, к примеру, ОбъединенныйНабор.
Внутрь него поместим два дочерних набора-запроса: Набор_Запрос_Договоры и Набор_Запрос_Документы.
В первом запросе (Набор_Запрос_Договоры) выполним левое соединение таблиц по договору непосредственно на уровне СУБД.
Во втором запросе (Набор_Запрос_Документы) свяжем эти же таблицы по документу расчетов.
В настройках полей обоих наборов сопоставим одинаковые по смыслу результирующие поля, присвоив им одинаковый Путь к данным.

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

Решение 3. Добавление разреза связи в оба набора (Метод автора темы)

Рассмотрим вариант, который применил автор темы для быстрого решения своей проблемы. Суть метода заключается в том, чтобы «поднять» аналитику связи из подчиненных таблиц на уровень первого набора данных.

Если изначально в Наборе 1 поле ЗаказКлиента было составного типа и мы не знали заранее, что в нем хранится, мы можем модифицировать запросы следующим образом:

В запрос Набора 1 добавляем явное определение типов или подтягиваем сопутствующую информацию:


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

В запрос Набора 2 выводим аналогичные поля связи. После этого на вкладке «Связи наборов данных» мы можем настроить связь по двум отдельным строкам, но уже с использованием параметров и условий, так как они будут четко разделены по типам данных. Либо, как сделал автор, мы можем перенести логику определения связи непосредственно во второй набор, чтобы связать таблицы по одному простому ключу. Это исключает неопределенность при сопоставлении разнородных типов данных.

Решение 4. Использование декартова произведения и фильтрации (Для небольших таблиц)

Иногда возникают ситуации, когда Набор 2 представляет собой внешнюю таблицу значений (например, загруженную из файла Excel или полученную через Web-сервис), структуру запроса которой мы не можем изменить на стороне СУБД. В этом случае применим метод виртуального связывания через константу:

В запросе Набора 1 (или в его вычисляемых полях) создаем синтетическое поле: 1 КАК КлючСвязи.
Во внешнем Наборе 2 (в таблице значений) также программно добавляем колонку КлючСвязи со значением 1 для всех строк.
Связываем два набора в СКД по полю КлючСвязи. Это действие заставит СКД соединить каждую строку первой таблицы с каждой строкой второй таблицы в оперативной памяти (аналог SQL-оператора CROSS JOIN).
Чтобы убрать лишние строки, в настройках компоновщика данных (вкладка «Настройки» -> «Отбор» для конкретной группировки) накладываем системный фильтр: ВычисляемоеПолеСовпадения = Истина.

Вычисляемое поле в СКД при этом настраивается следующим выражением:


Выбор 
    Когда ЗаказКлиента = Договор Или ЗаказКлиента = СсылкаДокумента 
        Тогда Истина 
    Иначе Ложь 
Конец

Важное предупреждение: Этот метод является крайне ресурсоемким. Если в вашем первом наборе будет 10 000 строк, и во втором наборе тоже 10 000 строк, в памяти сервера будет создана таблица объемом в 100 000 000 (сто миллионов) строк перед тем, как сработает фильтр СКД. Используйте этот обходной путь исключительно для работы с небольшими объемами данных (до нескольких сотен строк).

Почему СКД корректно рассчитывает итоги по ресурсам при связях наборов?

Вернемся к вопросу, затронутому в обсуждении на форуме: почему при связи двух наборов данных в СКД ресурсы рассчитываются корректно, а в случае обычного левого соединения в одном пакете запросов суммы начинают «двоиться»?

Разберем этот механизм изнутри:

При классическом SQL-соединении (LEFT JOIN): СУБД физически размножает строки левой таблицы, если в правой таблице нашлось несколько соответствий. Когда вы накладываете функцию СУММА() на поле из левой таблицы, СУБД послушно складывает все дубли, искажая итоговый финансовый результат.
При связи наборов данных в СКД: Процессор компоновки данных обрабатывает наборы изолированно. СКД сначала рассчитывает итоги и агрегирует показатели для Набора 1 на уровне его собственных группировок, и только затем сопоставляет (подтягивает) детальные строки из Набора 2. Благодаря этому показатели первого набора гарантированно защищены от дублирования и искажения, независимо от того, сколько связанных строк будет найдено во второй таблице.

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

← На главную