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