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