При разработке отчетов и обработок на платформе 1С:Предприятие 8.3, особенно с использованием Системы Компоновки Данных (СКД), часто возникает потребность в создании динамических запросов. Одной из таких задач является реализация необязательного соединения таблиц, которое будет выполняться только в том случае, если пользователь установил определенный отбор или заполнил параметр. Рассмотрим, как это можно реализовать, проанализируем типовые проблемы и предложим различные подходы к решению.
Представьте ситуацию: нам необходимо выбрать список номенклатуры. В большинстве случаев нас интересует вся номенклатура, но иногда требуется ограничить выборку только теми позициями, которые фигурируют в определенном документе (например, в документе "Перемещение товаров"). Если мы просто добавим жесткое соединение с табличной частью документа, запрос всегда будет выполняться дольше, даже когда отбор по документу не нужен. Кроме того, при отсутствии номенклатуры в указанном документе, такой запрос может не вернуть ничего, что не всегда соответствует логике.
Для решения этой проблемы в СКД предусмотрен механизм необязательных фрагментов запроса, обозначаемых фигурными скобками {}. Посмотрим, как использовать этот механизм и какие нюансы возникают при работе с ним, особенно при использовании вложенных запросов.
Механизм расширения языка запросов 1С для СКД позволяет заключать в фигурные скобки {} необязательные фрагменты запроса. Это могут быть поля, условия, секции ГДЕ или даже целые соединения. Если соответствующий параметр (или отбор) не заполнен или отключен пользователем, то заключенный в скобки фрагмент будет исключен из итогового текста запроса, отправляемого в базу данных.
Рассмотрим базовый пример необязательного соединения:
ВЫБРАТЬ
СпрНоменклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
{ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
ПО СпрНоменклатура.Ссылка = ПеремещениеТоваровТовары.Номенклатура}
{ГДЕ
ПеремещениеТоваровТовары.Ссылка.*}
В этом примере мы попытались сделать необязательным как само соединение, так и условие отбора по ссылке на документ. Однако, как показывает практика, для корректной работы СКД с динамическими условиями, особенно при использовании ссылочных полей, критически важно назначать псевдонимы полям, к которым применяются отборы.
Назначение псевдонима: Мы назначаем псевдоним полю ПеремещениеТоваровТовары.Ссылка.*, которое представляет собой ссылку на документ. Например, ПеремещениеТоваровТовары.Ссылка.* КАК ДокументПеремещения. Это позволяет СКД правильно сопоставить пользовательский отбор с соответствующим полем в запросе.
Привязка отбора: При установке пользователем отбора на поле "ДокументПеремещения" (настроенного в СКД как поле отчета, соответствующее псевдониму), СКД динамически добавляет соединение и условие ГДЕ в запрос. Если отбор не установлен, соединение и условие будут исключены.
Корректный вариант запроса с псевдонимом будет выглядеть так:
ВЫБРАТЬ
СпрНоменклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
{ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
ПО СпрНоменклатура.Ссылка = ПеремещениеТоваровТовары.Номенклатура}
{ГДЕ
ПеремещениеТоваровТовары.Ссылка.* КАК ДокументПеремещения}
В этом случае, если пользователь установит отбор по полю "ДокументПеремещения" (например, выберет конкретный документ), соединение с табличной частью документа будет добавлено, и будет произведен отбор по номенклатуре из этого документа. Если отбор не установлен, СКД сформирует запрос без этого соединения, что значительно ускорит его выполнение.
Ситуация усложняется, когда мы пытаемся использовать необязательное соединение с вложенным запросом. Изначально, можно было бы предположить, что условие ГДЕ внутри вложенного запроса будет аналогично работать с необязательным параметром. Рассмотрим пример такой конструкции:
ВЫБРАТЬ
СпрНоменклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
{ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ПеремещениеТоваровТовары.Номенклатура КАК Номенклатура
ИЗ
Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
{ГДЕ
(ПеремещениеТоваровТовары.Ссылка.*) КАК Документ}) КАК ВложенныйЗапрос
ПО СпрНоменклатура.Ссылка = ВложенныйЗапрос.Номенклатура}
В данном случае, как выяснилось на практике, условие {ГДЕ (ПеремещениеТоваровТовары.Ссылка.*) КАК Документ}, определенное внутри вложенного запроса, не всегда обрабатывается СКД как необязательное для внешнего соединения. Независимо от того, установлен ли отбор по полю "Документ", соединение с вложенным запросом может все равно выполняться (или, наоборот, условие отбора внутри вложенного запроса игнорируется), что приводит к некорректным результатам или замедлению работы.
Это связано с особенностями оптимизатора СКД и тем, как он преобразует запросы, особенно содержащие вложенные конструкции. Оптимизатору СУБД может быть сложно определить объем данных, возвращаемых вложенным запросом, что затрудняет выбор оптимального алгоритма соединения.
Для корректной обработки необязательного соединения с вложенным запросом в СКД, в некоторых случаях необходимо продублировать условие во внешней части запроса. Это позволяет СКД гарантированно учесть динамический отбор при формировании окончательного текста запроса.
Рассмотрим модифицированный запрос с дублированным условием:
ВЫБРАТЬ ПЕРВЫЕ 100
СпрНоменклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
{ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ПеремещениеТоваровТовары.Номенклатура КАК Номенклатура
ИЗ
Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
{ГДЕ
(ПеремещениеТоваровТовары.Ссылка.*) КАК Документ}) КАК ВложенныйЗапрос
ПО СпрНоменклатура.Ссылка = ВложенныйЗапрос.Номенклатура}
{ГДЕ
(ПеремещениеТоваровТовары.Ссылка.*) КАК Документ}
В этом решении мы добавили идентичное условие {ГДЕ (ПеремещениеТоваровТовары.Ссылка.*) КАК Документ} снаружи вложенного запроса, уже после основной секции ИЗ и СОЕДИНЕНИЕ. При таком подходе, когда пользователь устанавливает отбор на поле "Документ", СКД видит это условие как вложенным, так и внешним, что позволяет ей правильно активировать необязательное соединение и отфильтровать данные.
Почему это работает? Можно предположить, что СКД, при обработке необязательных фрагментов, в первую очередь анализирует условия, которые находятся на верхнем уровне запроса. Если условие, связанное с отбором, присутствует только внутри вложенного запроса, СКД может неверно интерпретировать его как необязательное для всего соединения или может проигнорировать его при оптимизации. Дублирование условия явно указывает СКД, что отбор по этому полю должен влиять на всю выборку.
Помимо прямого использования необязательных соединений, существуют и другие, часто более надежные или удобные способы реализации подобной логики, особенно когда стандартные средства СКД ведут себя непредсказуемо.
ГДЕ с оператором В (IN)Одним из наиболее часто используемых и надежных способов является применение секции ГДЕ с оператором В (IN), где список значений для отбора формируется в отдельном подзапросе. Этот подход позволяет явно контролировать отбор и обходить сложности с динамическим изменением соединений.
Рассмотрим пример:
ВЫБРАТЬ
СпрНоменклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
{ГДЕ
СпрНоменклатура.Ссылка В
(ВЫБРАТЬ
ПеремещениеТоваровТовары.Номенклатура
ИЗ
Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
{ГДЕ
ПеремещениеТоваровТовары.Ссылка.* КАК ДокументПеремещения})}
В этом варианте, если пользователь указывает отбор по "ДокументПеремещения", то подзапрос в секции ГДЕ формирует список номенклатуры, содержащейся в указанном документе. Основной запрос затем отбирает только ту номенклатуру, которая входит в этот список. Если отбор по документу не установлен, СКД исключает подзапрос и условие В, и выбирается вся номенклатура.
Преимущества:
ГДЕ.В (Выбрать ...) является стандартной и понятной.Этот метод является более сложным, но очень гибким, особенно когда требуется реализовать более сложную логику условного соединения или когда стандартные средства СКД не дают желаемого результата. Он предполагает использование временных таблиц и флага, который программно управляет применением отбора. Для ознакомления с реализацией такого подхода можно использовать пример отчета с фильтрацией данных через временные таблицы в запросе.
Рассмотрим по шагам:
Создание временной таблицы с отбираемой номенклатурой: В первом блоке запроса мы создаем временную таблицу (ВТОтбор), которая содержит номенклатуру из документа, если параметр отбора установлен.
ВЫБРАТЬ РАЗЛИЧНЫЕ
Товары.Номенклатура КАК Номенклатура,
ИСТИНА КАК Использование
ПОМЕСТИТЬ ВтОтбор
ИЗ
Документ.ПеремещениеТоваров.Товары КАК Товары
{ГДЕ
Товары.Ссылка.* КАК ДокументПеремещения}
;
Обратите внимание на поле ИСТИНА КАК Использование. Это флаг, который мы будем использовать далее.
Основной запрос с соединением с временной таблицей: Во втором блоке запроса мы соединяемся с этой временной таблицей.
////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
{ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтОтбор КАК ВтОтбор
ПО СпрНоменклатура.Ссылка = ВтОтбор.Номенклатура}
{ГДЕ
ВтОтбор.Использование КАК ИспользоватьОтборПоПеремещению}
Здесь мы используем необязательное условие {ГДЕ ВтОтбор.Использование КАК ИспользоватьОтборПоПеремещению}. Если мы установим отбор по полю "ИспользоватьОтборПоПеремещению" и присвоим ему значение "Истина", то будет произведено соединение с ВтОтбор и отфильтрована номенклатура.
Программное управление отбором: Этот подход предполагает, что вы можете программно управлять отбором в СКД. Например, в обработчике события формирования отчета или при изменении параметров вы можете проверять, установлен ли отбор по "ДокументПеремещения". Если он установлен, то вы программно устанавливаете отбор по полю "ИспользоватьОтборПоПеремещению" со значением "Истина". Если отбор по "ДокументПеремещения" не установлен, вы очищаете отбор по "ИспользоватьОтборПоПеремещению".
Преимущества:
Недостатки:
Если данные, которые нужно соединить, находятся в разных источниках или их логика формирования достаточно сложна, можно использовать несколько наборов данных и настроить связи между ними на закладке "Связи наборов данных" схемы компоновки данных. По умолчанию, если связи наборов данных явно не указаны, они отсутствуют, и наборы данных работают независимо. Тип соединения наборов данных в СКД по умолчанию - левое внешнее.
Этот подход полезен, когда логику формирования "основных" и "дополнительных" данных лучше разделить, а затем "склеить" их средствами СКД, попутно решая задачу скрытия столбцов, когда нет данных. Для такой задачи подойдёт готовое решение для скрытия столбцов в СКД при отсутствии данных.
Важно помнить, что СКД имеет свой внутренний оптимизатор запросов, который может значительно изменять исходный текст запроса. Иногда это приводит к неожиданным результатам, например, к исключению целой таблицы из запроса при определенных отборах или к игнорированию части условий.
Если ваш запрос работает корректно в консоли запросов (где нет оптимизатора СКД), но дает сбои или не работает должным образом в СКД, это может быть связано именно с особенностями оптимизации или обработки запросов системой компоновки данных. В таких случаях стоит попробовать альтернативные подходы, описанные выше, или экспериментировать с дублированием условий, как в решении для вложенных запросов.
Необязательные параметры в запросе СКД – это переменные, которые можно опустить при формировании запроса. Они служат для дополнительной фильтрации данных или изменения логики работы запроса. При построении отчетов в СКД, если значение параметра не задано, а он является обязательным, может быть выдана ошибка. Мы рекомендуем избегать использования параметров отчета там, где можно обойтись "обычным" управлением элементами отбора, предоставляя пользователю возможность управлять отбором напрямую через настройки отчета.
В заключение, реализация необязательных соединений в запросах СКД — это мощный инструмент для создания гибких и производительных отчетов. Освоив использование фигурных скобок, псевдонимов и зная особенности работы с вложенными запросами (и необходимость дублирования условий), вы сможете значительно расширить возможности ваших решений на 1С:Предприятие.