Как в запросе 1С сделать необязательный отбор по номенклатуре, если значение не указано?

← На главную

При разработке отчетов и обработок в среде 1С часто возникает типовая задача: на форме расположено поле отбора (например, номенклатура), и если пользователь выбрал конкретный элемент, запрос должен отфильтровать данные именно по нему. Однако если поле оставлено пустым, требуется вывести все записи без фильтрации. Рассмотрим, почему нельзя просто передать пустой список в условие, разберем популярные варианты решения с форума и выясним, какой подход является наиболее правильным с точки зрения стандартов разработки и производительности СУБД.

Ограничение оператора В при пустых значениях

Нередко у начинающих разработчиков возникает мысль использовать конструкцию ГДЕ Ном.Ссылка В (&Параметр) и передавать туда пустой массив или пустой список значений. Проанализируем, почему этот вариант не сработает для выборки всех данных. В платформе 1С передача пустой коллекции в условие В (&Список) всегда преобразуется в логическое значение ЛОЖЬ. В результате запрос вернет совершенно пустую таблицу, а не все записи. Поэтому для реализации необязательного отбора необходимо использовать другие подходы.

Вариант 1. Использование логического условия с оператором ИЛИ

Рассмотрим один из самых быстрых и простых способов, предложенных участниками обсуждения. Идея заключается в передаче дополнительного булевого флага, сигнализирующего об отсутствии отбора:


Запрос.УстановитьПараметр("Номенклатура", Номенклатура);
Запрос.УстановитьПараметр("НетОтбора", НЕ ЗначениеЗаполнено(Номенклатура));

// В тексте запроса:
// ГДЕ &НетОтбора ИЛИ Ном.Ссылка = &Номенклатура

Посмотрим на логику работы данного условия: если переменная Номенклатура не заполнена, параметр &НетОтбора равен значению ИСТИНА, и левая часть логического выражения делает все условие истинным для каждой строки, игнорируя проверку ссылки. Если же элемент выбран, флаг равен ЛОЖЬ, и СУБД начинает проверять совпадение поля Ном.Ссылка с переданным параметром.

Вариант 2. Конструкция ВЫБОР КОГДА в секции ГДЕ

Разберем еще один распространенный прием, основанный на ветвлении прямо внутри языка запросов 1С. В коде инициализируется булев параметр, определяющий необходимость отбора по всем элементам:


Запрос.УстановитьПараметр("Номенклатура", Номенклатура);
Если Не ЗначениеЗаполнено(Номенклатура) Тогда
    Запрос.УстановитьПараметр("ВсяНоменклатура", Истина);
Иначе
    Запрос.УстановитьПараметр("ВсяНоменклатура", Ложь);
КонецЕсли;

В тексте запроса секция отбора оформляется следующим образом:


ГДЕ
    ВЫБОР
        КОГДА &ВсяНоменклатура ТОГДА ИСТИНА
        ИНАЧЕ Ном.Ссылка = &Номенклатура
    КОНЕЦ

Встречаются и вариации с проверкой на пустую ссылку непосредственно в тексте запроса:


ГДЕ
    ВЫБОР
        КОГДА &Ссылка = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка) ТОГДА ИСТИНА
        ИНАЧЕ Ном.Ссылка = &Ссылка
    КОНЕЦ

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

Почему варианты с ИЛИ и ВЫБОР опасны для производительности?

Выясним причину, по которой опытные разработчики не рекомендуют использовать приведенные выше конструкции на больших информационных базах. Использование условий вида &Флаг ИЛИ Поле = &Параметр или конструкций ВЫБОР в секции ГДЕ нарушает принцип саргабельности (SARGable) запроса. Оптимизаторы современных СУБД (MS SQL Server, PostgreSQL) не могут заранее предугадать результат сложного логического выражения для каждой записи и построить эффективный план выполнения. Вместо быстрого точечного поиска по индексу (Index Seek) СУБД вынуждена выполнять полное сканирование таблицы или индекса (Table Scan / Index Scan), что при миллионах записей в справочнике приводит к существенным задержкам.

Вариант 3. Динамическое формирование текста запроса (Рекомендуемый подход)

Рассмотрим стандарт разработки 1С, обеспечивающий максимальную производительность. Наиболее правильным решением является динамическая сборка условий на встроенном языке перед выполнением запроса. В этом случае в СУБД передается чистый SQL-запрос без лишних ветвлений:


ТекстЗапроса = 
"ВЫБРАТЬ
|   Ном.Ссылка КАК НоменклатураСоСпец,
|   Спецификации.Ссылка КАК СпецификацияНоменклатуры,
|   Спецификации.Ссылка.ДатаУтверждения КАК ДатаУтверждения
|ИЗ
|   Справочник.СпецификацииНоменклатуры.ВыходныеИзделия КАК Спецификации
|   ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Ном
|       ПО Спецификации.Номенклатура = Ном.Ссылка
|ГДЕ
|   %УсловиеГДЕ%";

МассивУсловий = Новый Массив;
МассивУсловий.Добавить("Ном.ЭтоГруппа = ЛОЖЬ");

Запрос = Новый Запрос;

Если ЗначениеЗаполнено(Номенклатура) Тогда
    МассивУсловий.Добавить("Ном.Ссылка = &Ссылка");
    Запрос.УстановитьПараметр("Ссылка", Номенклатура);
КонецЕсли;

ТекстУсловий = СтрСоединить(МассивУсловий, " И ");
Запрос.Текст = СтрЗаменить(ТекстЗапроса, "%УсловиеГДЕ%", ТекстУсловий);

Результат = Запрос.Выполнить();

Посмотрим на преимущества такого подхода:

Оптимальный план запроса: если отбор не задан, СУБД даже не пытается фильтровать данные по ссылке. Если задан — используется прямой поиск по первичному ключу таблицы.
Гибкость масштабирования: в массив условий можно легко добавлять новые необязательные фильтры (по складам, контрагентам, датам).

Вариант 4. Использование Построителя запроса или СКД

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

ПостроительЗапроса: поддерживает синтаксис фигурных скобок. Условие, помещенное в фигурные скобки, например {ГДЕ Ном.Ссылка.* КАК Ссылка}, автоматически удаляется платформой из итогового текста, если пользователь не активировал соответствующий отбор в свойствах построителя.
Система компоновки данных (СКД): при создании отчетов рекомендуется использовать СКД. Компоновщик данных анализирует доступные настройки и включает в итоговый запрос к СУБД только те условия и параметры, которые фактически заполнены и включены пользователем.

Важное замечание о поле «ЭтоГруппа»

Обратим внимание на условие Ном.ЭтоГруппа = ЛОЖЬ. Использование этого реквизита допустимо только в тех справочниках, где в свойствах метаданных включена иерархия с видом «Иерархия групп и элементов». Если у справочника отключена иерархия или выбрана «Иерархия элементов», реквизит ЭтоГруппа отсутствует, и попытка выполнить запрос приведет к синтаксической ошибке платформы.