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