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