Как в запросе 1С сделать необязательный отбор по организации?

← На главную

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

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

Разбор типовой синтаксической ошибки

Рассмотрим пример кода, с которого началось обсуждение:


Запрос.Текст = 
    "ВЫБРАТЬ
    |    ЛицевыеСчетаРаботниковОрганизации.Организация,
    |    ЛицевыеСчетаРаботниковОрганизации.НомерЛицевогоСчета,
    |    ЛицевыеСчетаРаботниковОрганизации.ФизЛицо
    |ИЗ
    |    РегистрСведений.ЛицевыеСчетаРаботниковОрганизации КАК ЛицевыеСчетаРаботниковОрганизации
    |ГДЕ
    |    (&ВыбОрг = ЗНАЧЕНИЕ(РегистрСведений.ЛицевыеСчетаРаботниковОрганизации ЕСТЬ NULL)
    |        ИЛИ ЛицевыеСчетаРаботниковОрганизации.Организация = &ВыбОрг)";

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

Ограничения функции ЗНАЧЕНИЕ(): Данная функция языка запросов предназначена исключительно для явного указания литералов ссылочных типов конфигурации — предопределенных элементов справочников, значений перечислений, пустых ссылок (например, ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)) или системных перечислений. Внутрь конструкции ЗНАЧЕНИЕ() категорически нельзя помещать логические выражения, операторы ЕСТЬ NULL или имена таблиц регистров сведений.
Поведение невыбранного реквизита формы: Если на форме элемент управления ссылается на справочник Организации, то при незаполненном значении в переменную передается не NULL, а пустая ссылка соответствующего типа либо значение Неопределено. Проверка на NULL для входного параметра ссылки не имеет практического смысла.

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

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

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

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


ТекстЗапроса = 
    "ВЫБРАТЬ
    |    ЛицевыеСчета.Организация КАК Организация,
    |    ЛицевыеСчета.НомерЛицевогоСчета КАК НомерЛицевогоСчета,
    |    ЛицевыеСчета.ФизЛицо КАК ФизЛицо
    |ИЗ
    |    РегистрСведений.ЛицевыеСчетаРаботниковОрганизации КАК ЛицевыеСчета";

Если ЗначениеЗаполнено(ВыбОрг) Тогда
    ТекстЗапроса = ТекстЗапроса + "
    |ГДЕ
    |    ЛицевыеСчета.Организация = &ВыбОрг";
КонецЕсли;

Запрос = Новый Запрос;
Запрос.Текст = ТекстЗапроса;

Если ЗначениеЗаполнено(ВыбОрг) Тогда
    Запрос.УстановитьПараметр("ВыбОрг", ВыбОрг);
КонецЕсли;

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

Если условий отбора несколько, для предотвращения ошибок склейки строк удобно накапливать фрагменты условий в массиве и объединять их с помощью функции СтрСоединить():


МассивУсловий = Новый Массив;

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

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

Если МассивУсловий.Количество() > 0 Тогда
    ТекстЗапроса = ТекстЗапроса + " ГДЕ " + СтрСоединить(МассивУсловий, " И ");
КонецЕсли;

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

Способ 2. Использование логического флага в параметрах

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

Разберем реализацию этого метода:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    ЛицевыеСчета.Организация КАК Организация,
    |    ЛицевыеСчета.НомерЛицевогоСчета КАК НомерЛицевогоСчета,
    |    ЛицевыеСчета.ФизЛицо КАК ФизЛицо
    |ИЗ
    |    РегистрСведений.ЛицевыеСчетаРаботниковОрганизации КАК ЛицевыеСчета
    |ГДЕ
    |    (&ВыбиратьВсе = ИСТИНА
    |        ИЛИ ЛицевыеСчета.Организация = &ВыбОрг)";

ОтборПоВсем = Не ЗначениеЗаполнено(ВыбОрг);

Запрос.УстановитьПараметр("ВыбиратьВсе", ОтборПоВсем);
Запрос.УстановитьПараметр("ВыбОрг", ВыбОрг);

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

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

Особенности работы с периодическими регистрами сведений

Регистр ЛицевыеСчетаРаботниковОрганизации в типовых конфигурациях чаще всего является периодическим. Для получения актуального номера счета на определенную дату необходимо обращаться не к физической таблице регистра, а к его виртуальной таблице СрезПоследних.

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

Посмотрим на корректный пример с виртуальной таблицей:


ТекстОтбора = "";
Если ЗначениеЗаполнено(ВыбОрг) Тогда
    ТекстОтбора = "Организация = &ВыбОрг";
КонецЕсли;

Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    ЛицевыеСчета.Организация КАК Организация,
    |    ЛицевыеСчета.НомерЛицевогоСчета КАК НомерЛицевогоСчета,
    |    ЛицевыеСчета.ФизЛицо КАК ФизЛицо
    |ИЗ
    |    РегистрСведений.ЛицевыеСчетаРаботниковОрганизации.СрезПоследних(&Период, " + ТекстОтбора + ") КАК ЛицевыеСчета";

Запрос.УстановитьПараметр("Период", ТекущаяДата());
Если ЗначениеЗаполнено(ВыбОрг) Тогда
    Запрос.УстановитьПараметр("ВыбОрг", ВыбОрг);
КонецЕсли;

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

В случаях разработки сложных отчетов лучшей альтернативой ручному кодированию условий является использование Схемы компоновки данных (СКД) или объекта ПостроительЗапроса. СКД анализирует доступность параметров и автоматически исключает неиспользуемые фильтры из результирующего SQL-запроса без риска снижения производительности.