При разработке отчетов и обработок в среде 1С:Предприятие программисты часто сталкиваются со следующей задачей: на форме отчета присутствует поле для выбора списка значений (например, видов номенклатуры, складов или контрагентов). Если пользователь заполнил этот список, запрос должен отфильтровать данные по выбранным элементам. Если же список остался пустым, то запрос должен проигнорировать этот отбор и вывести данные по всем элементам без ограничений.
В этой статье мы подробно разберем, почему простые и очевидные решения не работают напрямую в СУБД, а также детально рассмотрим несколько профессиональных и надежных способов реализации необязательного отбора в запросах.
Первое, что часто приходит в голову начинающим разработчикам — это использовать в секции ГДЕ конструкцию выбора или проверки параметра на равенство системным значениям:
ГДЕ
&ВидНоменклатуры ЕСТЬ NULL
ИЛИ СпрТовары.ВидНоменклатуры В (&ВидНоменклатуры)
Или такой вариант:
ГДЕ
ВЫБОР
КОГДА &ВидНоменклатуры = НЕОПРЕДЕЛЕНО ТОГДА ИСТИНА
ИНАЧЕ СпрТовары.ВидНоменклатуры В (&ВидНоменклатуры)
КОНЕЦ
Давайте выясним причину, почему такие конструкции приводят к ошибкам или к получению пустого результата запроса. Дело в том, что когда мы передаем в качестве параметра запроса объект типа СписокЗначений или Массив для оператора сравнения В, платформа 1С на уровне СУБД преобразует этот параметр во временный набор данных (табличную выборку). Если список пуст, то СУБД не может сопоставить пустое множество значений с одиночным типом вроде NULL или НЕОПРЕДЕЛЕНО. СУБД просто не понимает такое сравнение, из-за чего выполнение запроса завершается неудачей или возвращает пустую выборку.
Рассмотрим по шагам работающие и эффективные альтернативные методы решения этой задачи.
Если ваш запрос выполняется в программном коде модуля (а не внутри схемы компоновки данных), то самым производительным решением будет динамическая сборка текста запроса в зависимости от заполнения списка на форме.
С точки зрения СУБД этот метод идеален. В итоговый SQL-запрос не попадает никаких лишних условий и логических операторов ИЛИ, которые мешают оптимизатору СУБД правильно использовать индексы таблиц. Если отбор не нужен, текст запроса остается максимально простым и выполняется максимально быстро.
Проанализируем пример кода на встроенном языке:
// Описываем базовую часть запроса без условия отбора
ТекстЗапроса =
"ВЫБРАТЬ
| СпрТовары.Ссылка КАК Ссылка,
| СпрТовары.Артикул КАК Артикул
|ИЗ
| Справочник.Номенклатура КАК СпрТовары
|ГДЕ
| СпрТовары.ПометкаУдаления = ЛОЖЬ
| И СпрТовары.ЭтоГруппа = ЛОЖЬ";
// Проверяем заполненность списка значений
Если ЗначениеЗаполнено(СписокВидовНоменклатуры) И СписокВидовНоменклатуры.Количество() > 0 Тогда
// Динамически добавляем условие в текст запроса
ТекстЗапроса = ТекстЗапроса + "
| И СпрТовары.ВидНоменклатуры В (&СписокВидовНоменклатуры)";
КонецЕсли;
Запрос = Новый Запрос;
Запрос.Текст = ТекстЗапроса;
// Устанавливаем параметр только в том случае, если условие было добавлено
Если ЗначениеЗаполнено(СписокВидовНоменклатуры) И СписокВидовНоменклатуры.Количество() > 0 Тогда
Запрос.УстановитьПараметр("СписокВидовНоменклатуры", СписокВидовНоменклатуры);
КонецЕсли;
РезультатЗапроса = Запрос.Выполнить();
Этот способ прост, надежен и гарантирует высокую скорость выполнения запроса на СУБД любого типа.
Бывают ситуации, когда текст запроса очень большой, содержит множество пакетов, и динамическая склейка строк кода сильно ухудшает его читаемость и усложняет отладку. В таких случаях можно использовать статический текст запроса, добавив в параметры специальный булев флаг, который будет сообщать запросу, нужно ли применять отбор. При необходимости можно изучить пример использования свитчей и переключателей в условиях запросов.
Посмотрим на пример реализации такого условия в тексте запроса:
ВЫБРАТЬ
СпрТовары.Ссылка КАК Ссылка,
СпрТовары.Артикул КАК Артикул
ИЗ
Справочник.Номенклатура КАК СпрТовары
ГДЕ
СпрТовары.ПометкаУдаления = ЛОЖЬ
И (&ИгнорироватьОтбор ИЛИ СпрТовары.ВидНоменклатуры В (&СписокВидовНоменклатуры))
Теперь разберем, как правильно передать параметры из программного кода встроенного языка 1С:
Запрос = Новый Запрос(ТекстЗапроса);
// Определяем значение флага: если список пуст, мы должны ИГНОРИРОВАТЬ отбор (ИСТИНА)
ИгнорироватьОтбор = (СписокВидовНоменклатуры.Количество() = 0);
Запрос.УстановитьПараметр("ИгнорироватьОтбор", ИгнорироватьОтбор);
// Передаем сам список (даже если он пустой, ошибки не возникнет, так как сработает первая часть условия ИЛИ)
Запрос.УстановитьПараметр("СписокВидовНоменклатуры", СписокВидовНоменклатуры);
РезультатЗапроса = Запрос.Выполнить();
Важное примечание: Использование оператора ИЛИ в секции ГДЕ может негативно сказаться на производительности запроса при работе с таблицами большого объема (миллионы записей). СУБД может отказаться от использования индексов и выполнить полное сканирование таблицы. Применяйте этот метод осторожно и преимущественно на относительно небольших справочниках или таблицах документов.
Если вы разрабатываете отчет на базе СКД или настраиваете динамический список на управляемой форме, вам не нужно вручную склеивать строки или передавать флаги. В СКД встроен великолепный инструмент декларативного описания необязательных условий с помощью фигурных скобок {}.
Рассмотрим, как правильно записать такое условие в конструкторе запроса СКД:
ВЫБРАТЬ
СпрТовары.Ссылка КАК Ссылка,
СпрТовары.Артикул КАК Артикул
ИЗ
Справочник.Номенклатура КАК СпрТовары
ГДЕ
СпрТовары.ПометкаУдаления = ЛОЖЬ
{ГДЕ (СпрТовары.ВидНоменклатуры В (&ВидНоменклатуры))}
При генерации финального текста SQL-запроса компоновщик макета СКД анализирует, заполнил ли пользователь параметр ВидНоменклатуры на форме отчета:
И СпрТовары.ВидНоменклатуры В (&ВидНоменклатуры) в итоговый запрос.Рассмотрим еще один интересный способ, который позволяет обойтись без использования динамического текста и внешних булевых переменных. Его суть заключается в предварительной выгрузке списка во временную таблицу и последующей проверке наличия записей в этой таблице непосредственно в теле основного запроса. Для ознакомления с подобной механикой подойдёт базовый пример фильтрации данных с помощью временных таблиц в запросе.
Проанализируем структуру такого пакетного запроса:
// Шаг 1: Помещаем переданный список во временную таблицу ВТ_Отбор
ВЫБРАТЬ
ВидыНоменклатуры.Ссылка КАК Ссылка,
ИСТИНА КАК ЕстьОтбор
ПОМЕСТИТЬ ВТ_Отбор
ИЗ
Справочник.ВидыНоменклатуры КАК ВидыНоменклатуры
ГДЕ
ВидыНоменклатуры.Ссылка В (&СписокВидовНоменклатуры)
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем единичную запись с флагом наличия отбора
ВЫБРАТЬ
МАКСИМУМ(ВТ_Отбор.ЕстьОтбор) КАК ЕстьОтбор
ПОМЕСТИТЬ ВТ_ФлагОтбора
ИЗ
ВТ_Отбор КАК ВТ_Отбор
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Основной запрос с левым соединением к флагу отбора
ВЫБРАТЬ
СпрТовары.Ссылка КАК Ссылка,
СпрТовары.Артикул КАК Артикул
ИЗ
Справочник.Номенклатура КАК СпрТовары
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ФлагОтбора КАК ФлагОтбора ПО ИСТИНА
ГДЕ
СпрТовары.ПометкаУдаления = ЛОЖЬ
И (НЕ ЕСТЬNULL(ФлагОтбора.ЕстьОтбор, ЛОЖЬ)
ИЛИ СпрТовары.ВидНоменклатуры В (&СписокВидовНоменклатуры))
Если переданный список пуст, первая временная таблица ВТ_Отбор будет пустой. Соответственно, ВТ_ФлагОтбора вернет значение NULL в поле ЕстьОтбор. В основном запросе функция ЕСТЬNULL превратит его в значение ЛОЖЬ, условие НЕ ЛОЖЬ вернет ИСТИНА, и левая часть логического оператора ИЛИ позволит выбрать все элементы.
В ходе обсуждения на форуме участники нашли еще один хитрый обходной путь. Он основан на особенности работы оператора В ИЕРАРХИИ. Если в данный оператор передать ПустуюСсылку справочника, то условие отбора будет проигнорировано платформой и СУБД вернет все записи.
Разберем, как выглядит программная подготовка параметра:
Если СписокВидовНоменклатуры.Количество() = 0 Тогда
// Если список пуст, передаем вместо него пустую ссылку справочника
Запрос.УстановитьПараметр("ВидНоменклатуры", Справочники.ВидыНоменклатуры.ПустаяСсылка());
Иначе
Запрос.УстановитьПараметр("ВидНоменклатуры", СписокВидовНоменклатуры);
КонецЕсли;
А в самом запросе условие записывается следующим образом:
ГДЕ
СпрТовары.ПометкаУдаления = ЛОЖЬ
И СпрТовары.ВидНоменклатуры В ИЕРАРХИИ(&ВидНоменклатуры)
Внимание! Хотя этот метод привлекает своей краткостью, мы крайне не рекомендуем использовать его в реальных крупных проектах по следующим причинам:
В ИЕРАРХИИ при работе с пустой ссылкой является недокументированной особенностью платформы 1С. В новых версиях платформы или при смене СУБД (например, при переходе с MS SQL Server на PostgreSQL) логика работы может измениться, что приведет к неработоспособности вашего кода.В ИЕРАРХИИ само по себе является тяжелой операцией для СУБД, так как СУБД приходится строить дерево рекурсивных запросов. Если иерархия в справочнике отсутствует, данный метод приведет к бессмысленным потерям производительности.Мы подробно проанализировали различные варианты решения задачи с необязательными параметрами в запросах 1С. Для обычных программных запросов лучшим выбором по соотношению скорости работы и надежности кода является динамическая сборка текста запроса (Способ 1) или использование дополнительного булевого флага (Способ 2). При разработке отчетов обязательно задействуйте штатные механизмы СКД (Способ 3), так как они спроектированы специально для таких задач и обеспечивают максимальное быстродействие.