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

← На главную

Каждый разработчик 1С рано или поздно сталкивается с классической задачей: необходимо построить запрос, в котором один или несколько фильтров могут быть не заполнены пользователем — для этого есть конструктор запросов с произвольными фильтрами. В стандартном поведении, если мы добавляем условие ГДЕ Таблица.Организация = &Организация и передаем туда пустую ссылку, СУБД честно пытается найти записи с пустой организацией. В результате пользователь получает пустой отчет, хотя ожидал увидеть документы по всем организациям без ограничения. Наша цель — сделать этот параметр «отключаемым».

В этой статье мы подробно разберем все возможные подходы к решению данной задачи: от самых простых до наиболее профессиональных и оптимизированных с точки зрения производительности СУБД. Мы проанализируем сильные и слабые стороны каждого метода, выясним причины потенциального замедления базы данных и научимся писать действительно качественный код — поможет отладка кода и вычисление выражений в 1С.

Вариант 1. Использование логических условий в тексте запроса (ИЛИ / ВЫБОР)

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


ВЫБРАТЬ
    РеализацияТоваровУслуг.Ссылка КАК Реализация
ИЗ
    Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
    РеализацияТоваровУслуг.Дата >= &ДатаНачала
    И РеализацияТоваровУслуг.Дата <= &ДатаОкончания
    И (РеализацияТоваровУслуг.Организация = &Организация
        ИЛИ &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка))
УПОРЯДОЧИТЬ ПО
    РеализацияТоваровУслуг.Дата УБЫВ

Разберем по шагам, как работает эта конструкция. Если в параметр &Организация передана конкретная заполненная ссылка, то вторая часть условия &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка) возвращает ложь. СУБД вычисляет первую часть выражения: РеализацияТоваровУслуг.Организация = &Организация, производя отбор по конкретному значению. Если же в параметр передана пустая ссылка, то вторая часть выражения становится истинной. Благодаря оператору ИЛИ, все выражение целиком превращается в ИСТИНА, и фильтрация по организации фактически отключается.

Аналогичным образом можно записать это условие с помощью конструкции ВЫБОР КОГДА:


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

Критически важный нюанс производительности! Несмотря на кажущуюся простоту и читабельность, данный подход таит в себе серьезную опасность для высоконагруженных систем и больших баз данных. С точки зрения СУБД (MS SQL, PostgreSQL), использование оператора ИЛИ или конструкции ВЫБОР внутри секции ГДЕ часто превращается в антипаттерн.

Выясним причину этой проблемы. Оптимизатор СУБД строит план выполнения запроса (поможет анализ производительности и планов выполнения запросов) до того, как получит конкретные значения параметров. Ему необходимо создать универсальный план, который будет одинаково хорошо работать как для заполненного параметра, так и для пустого. Наличие условий типа ИЛИ сбивает оптимизатор с толку. Вместо быстрого поиска по индексу (Index Seek) СУБД выбирает безопасный, но крайне медленный вариант — полное сканирование таблицы (Table Scan или Index Scan). На таблице с сотнями тысяч документов это гарантированно приведет к зависанию сервера, избыточному потреблению оперативной памяти и дисковой активности, а также к блокировкам таблиц. Использовать этот вариант можно только на небольших справочниках или таблицах.

Вариант 2. Динамическое изменение текста запроса во встроенном языке

Чтобы избежать проблем с планами запросов в СУБД, профессиональные разработчики стараются убрать лишние условия из текста запроса еще до его отправки на сервер. Если параметр пустой, условие отбора по этому полю вообще не должно присутствовать в SQL-коде.

Посмотрим на пример простой программной сборки текста запроса с помощью функции СтрЗаменить():


// Формируем базовый текст запроса с условием-заглушкой
ТекстЗапроса = 
"ВЫБРАТЬ
|   РеализацияТоваровУслуг.Ссылка КАК Реализация
|ИЗ
|   Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
|ГДЕ
|   РеализацияТоваровУслуг.Дата >= &ДатаНачала
|   И РеализацияТоваровУслуг.Дата <= &ДатаОкончания
|   {ФильтрОрганизация}";

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ДатаНачала", ДатаНачала);
Запрос.УстановитьПараметр("ДатаОкончания", ДатаОкончания);

Если ЗначениеЗаполнено(Организация) Тогда
    // Если параметр заполнен, заменяем заглушку на реальное условие
    ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "{ФильтрОрганизация}", "И РеализацияТоваровУслуг.Организация = &Организация");
    Запрос.УстановитьПараметр("Организация", Организация);
Иначе
    // Если параметр пуст, просто удаляем заглушку из текста
    ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "{ФильтрОрганизация}", "");
КонецЕсли;

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

Проанализируем ситуацию. Этот метод полностью решает проблему производительности СУБД. В итоговом запросе будет только то условие, которое действительно нужно в данный момент. База данных сможет легко использовать индекс по полю Организация, если параметр передан, и индекс по полю Дата, если параметр не заполнен — для этого есть анализ объема данных и состояния индексов.

Тем не менее, у текстовой замены есть свои существенные минусы:

Низкая устойчивость кода к изменениям. Если другой разработчик решит отредактировать запрос в конструкторе запросов 1С, он может случайно стереть или изменить маркер-заглушку {ФильтрОрганизация}. Программа продолжит компилироваться, но логика работы отборов сломается во время выполнения.
Сложность поддержки при большом количестве параметров. Если в отчете или обработке используется 5–10 необязательных параметров, код их динамической замены превратится в громоздкое дерево условий Если... ИначеЕсли, в котором легко запутаться и совершить ошибку.

Вариант 3. Использование объекта СхемаЗапроса

Для тех случаев, когда динамическое формирование запроса неизбежно, но хочется избежать ненадежной работы со строками, платформа 1С предлагает мощный инструмент — объект СхемаЗапроса. Он позволяет программно разбирать, анализировать и модифицировать структуру запроса как полноценный объектный класс.

Рассмотрим, как можно безопасно управлять условиями отбора с помощью этого механизма:


Схема = Новый СхемаЗапроса;
Схема.УстановитьТекстЗапроса(БазовыйТекстЗапроса);

// Получаем первый пакет и первый оператор выбора в схеме
Пакет = Схема.ПакетЗапросов[0];
Оператор = Пакет.Операторы[0];

Если ЗначениеЗаполнено(Организация) Тогда
    // Программно добавляем условие в коллекцию условий отбора
    Оператор.Отбор.Добавить("РеализацияТоваровУслуг.Организация = &Организация");
    Запрос.УстановитьПараметр("Организация", Организация);
КонецЕсли;

// Превращаем схему обратно в готовый текст запроса
Запрос.Текст = Схема.ПолучитьТекстЗапроса();

Этот способ объединяет в себе преимущества высокой производительности (поскольку итоговый SQL-код чист и оптимизирован) и исключительной надежности разработки. Мы больше не рискуем сломать синтаксис запроса из-за пропущенного пробела или опечатки в названии заменяемой строки.

Вариант 4. Золотой стандарт разработки — Система компоновки данных (СКД)

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

Для реализации этой логики используется расширение языка запросов 1С — фигурные скобки. Проанализируем пример такого запроса:


ВЫБРАТЬ
    Реализация.Ссылка КАК Реализация,
    Реализация.Дата КАК Дата,
    Реализация.Организация КАК Организация
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Дата >= &ДатаНачала
    И Реализация.Дата <= &ДатаОкончания
    {ГДЕ Реализация.Организация = &Организация}

Разберем по шагам логику работы фигурных скобок в СКД:

Когда СКД генерирует финальный запрос к базе данных, она анализирует настройки компоновки (отборы, параметры, выбранные поля).
Если пользователь установил значение параметра &Организация в настройках отчета, СКД автоматически преобразует текст, уберет фигурные скобки и выполнит полноценный отбор.
Если же параметр остался незаполненным или флаг его использования был снят пользователем, СКД полностью проигнорирует конструкцию внутри фигурных скобок и «вырежет» её из результирующего SQL-запроса.

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

Нюансы работы с массивами и оператором «В ИЕРАРХИИ»

В процессе обсуждения разработчики часто предлагают использовать оператор В или В ИЕРАРХИИ, чтобы иметь возможность передавать не одну организацию, а целый список. Однако здесь кроется еще одна подводная ловушка.

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

Чтобы избежать этой ошибки при работе со списками и массивами в динамических запросах, обязательно выполняйте проверку на заполненность коллекции перед установкой параметров:


Если МассивОрганизаций.Количество() > 0 Тогда
    ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "{ОтборОрганизации}", "И Реализация.Организация В (&МассивОрганизаций)");
    Запрос.УстановитьПараметр("МассивОрганизаций", МассивОрганизаций);
Иначе
    ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "{ОтборОрганизации}", "");
КонецЕсли;

Обработка составных типов данных и значения Неопределено

Иногда реквизиты документов или параметры могут иметь составной тип данных. В этом случае пустая ссылка конкретного справочника и пустое значение параметра (которое может быть передано как Неопределено) — это абсолютно разные сущности с точки зрения платформы 1С.

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


ГДЕ
    (&Организация = НЕОПРЕДЕЛЕНО
        ИЛИ &Организация = ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)
        ИЛИ Реализация.Организация = &Организация)

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