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