Как соединить регистр «Работники» с регистром «ЗначенияСвойствОбъектов» и вывести несколько свойств параллельно без потери данных?

Программист 1С v8.3 (Обычные формы) 1С:Зарплата и Управление Персоналом Управленческий учет IT и автоматизация бизнеса

При разработке отчетов в 1С программисты часто сталкиваются с необходимостью дополнить основные данные объектов (например, сотрудников, документов или контрагентов) дополнительными реквизитами из регистра сведений ЗначенияСвойствОбъектов. Ситуация усложняется, когда нужно получить не одно, а сразу несколько свойств параллельно (например, «Размер оклада» и «Департамент»).

В этой статье мы подробно разберем типичную ошибку, из-за которой при соединении таблиц «исчезают» строки отчета, выясним причину этого поведения СУБД и рассмотрим четыре эффективных способа решения этой задачи в зависимости от архитектуры вашего решения.

Причина проблемы: «Ловушка NULL» в левых соединениях

Рассмотрим ситуацию, с которой столкнулся автор темы. Ему требовалось соединить таблицу работников по полю Регистратор (документ приема или перемещения) с регистром ЗначенияСвойствОбъектов, чтобы вывести одновременно два свойства. Исходный запрос выглядел следующим образом:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Работники.Регистратор,
    ЕСТЬNULL(ЗначенияСвойствОбъектовЗП.Значение, 0) КАК ЗП,
    ЕСТЬNULL(ЗначенияСвойствОбъектовДоплата.Значение, 0) КАК Доплата
ИЗ
    РегистрСведений.Работники КАК Работники
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектовЗП
        ПО Работники.Регистратор = ЗначенияСвойствОбъектовЗП.Объект
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектовДоплата
        ПО Работники.Регистратор = ЗначенияСвойствОбъектовДоплата.Объект
ГДЕ
    ЗначенияСвойствОбъектовДоплата.Свойство = &Доплата
    И ЗначенияСвойствОбъектовЗП.Свойство = &ЗП

Почему этот запрос работает некорректно? При таком написании в результат отчета попадает только одна запись — та, в которой у документа заполнены оба свойства сразу. Если у сотрудника заполнено только свойство «ЗП», а «Доплата» не заполнена (или наоборот), то строка вообще исключается из результата запроса.

Проанализируем ситуацию по шагам:

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

Теперь разберем, как решить эту проблему правильно несколькими способами.

Решение 1. Перенос условий фильтрации в секцию связи ПО (Классический метод)

Самый простой и быстрый способ исправить логику запроса — перенести условия отбора конкретных свойств из секции ГДЕ непосредственно в условия связи ПО каждого левого соединения.

В этом случае СУБД сначала отфильтрует записи правых таблиц по конкретным свойствам, а уже затем выполнит левое соединение. Строки левой таблицы (работники) гарантированно останутся в результирующем наборе данных, независимо от наличия у них свойств.

Посмотрим на правильный вариант запроса:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Работники.Регистратор,
    ЕСТЬNULL(ЗначенияСвойствОбъектовЗП.Значение, 0) КАК ЗП,
    ЕСТЬNULL(ЗначенияСвойствОбъектовДоплата.Значение, 0) КАК Доплата
ИЗ
    РегистрСведений.Работники КАК Работники
        
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектовЗП
        ПО Работники.Регистратор = ЗначенияСвойствОбъектовЗП.Объект
            И ЗначенияСвойствОбъектовЗП.Свойство = &ЗП
            
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектовДоплата
        ПО Работники.Регистратор = ЗначенияСвойствОбъектовДоплата.Объект
            И ЗначенияСвойствОбъектовДоплата.Свойство = &Доплата

Важное замечание: Обратите внимание, что из секции ГДЕ полностью удалены условия на свойства. Теперь связь работает корректно, и если у регистратора заполнено только одно свойство, второе просто выведется как 0 (благодаря функции ЕСТЬNULL).

Решение 2. Разворот строк в колонки через группировку (Оптимальный по производительности метод)

Предыдущий метод прост в реализации, однако у него есть существенный недостаток. Если вам понадобится выводить параллельно не 2, а, к примеру, 10 или 15 свойств, вам придется написать 10-15 левых соединений с одной и той же таблицей ЗначенияСвойствОбъектов. Для СУБД это выльется в крайне тяжелый и неоптимальный план запроса, что приведет к зависанию отчета на крупных базах данных.

Чтобы избежать многократного обращения к одной таблице, мы можем использовать метод разворота строк (pivoting) с помощью одного соединения, группировки и конструкции ВЫБОР. Разберем этот алгоритм:

Делаем всего одно левое соединение с регистром свойств.
В условии соединения указываем список всех необходимых нам свойств через оператор В.
С помощью конструкции ВЫБОР распределяем значения по соответствующим колонкам.
Группируем результат по ключевым полям сотрудника и агрегируем значения функцией МАКСИМУМ (поскольку для каждого свойства у одного объекта может быть только одна запись, функция вернет именно её).

Посмотрим на пример практической реализации этого подхода:


ВЫБРАТЬ
    Работники.Регистратор КАК Регистратор,
    МАКСИМУМ(ВЫБОР 
        КОГДА ЗначенияСвойств.Свойство = &СвойствоЗП 
        ТОГДА ЗначенияСвойств.Значение 
        ИНАЧЕ 0 
    КОНЕЦ) КАК ЗП,
    МАКСИМУМ(ВЫБОР 
        КОГДА ЗначенияСвойств.Свойство = &СвойствоДоплата 
        ТОГДА ЗначенияСвойств.Значение 
        ИНАЧЕ 0 
    КОНЕЦ) КАК Доплата
ИЗ
    РегистрСведений.Работники КАК Работники
    
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойств
        ПО Работники.Регистратор = ЗначенияСвойств.Объект
        И ЗначенияСвойств.Свойство В (&СписокСвойств)
СГРУППИРОВАТЬ ПО
    Работники.Регистратор

В параметр &СписокСвойств мы передаем массив, содержащий элементы &СвойствоЗП и &СвойствоДоплата. Такой запрос выполнится значительно быстрее, так как СУБД выполнит сканирование таблицы свойств всего один раз.

Решение 3. Использование механизма характеристик в СКД

В тексте сообщения автора темы присутствует блок описания характеристик ({ХАРАКТЕРИСТИКИ...}). Это указывает на то, что отчет разрабатывается в Системе компоновки данных (СКД).

Если вы используете СКД, то ручное прописывание левых соединений в тексте запроса вообще является плохой практикой (антипаттерном). Платформа 1С умеет делать это автоматически, динамически и максимально оптимизировано под конкретные действия пользователя.

Рассмотрим, как правильно настроить свойства через механизм характеристик СКД:

В схеме компоновки данных перейдите на вкладку «Наборы данных».
В таблице наборов данных выберите ваш запрос. Внизу появится таблица настройки характеристик объекта.
Нажмите кнопку «Добавить» и укажите тип объекта (например, ДокументСсылка.ПриемНаРаботуВОрганизацию или СправочникСсылка.ФизическиеЛица).
Заполните источники для видов характеристик и их значений:
- Виды характеристик: План видов характеристик СвойстваОбъектов.
- Значения характеристик: Регистр сведений ЗначенияСвойствОбъектов.
В самом тексте запроса выберите только основные данные работников, удалив все левые соединения с регистром свойств.

После такой настройки свойства автоматически появятся в доступных полях СКД в виде подчиненных полей объекта (например, Регистратор.[ЗП] или Регистратор.[Департамент]).

Главное преимущество метода характеристик: СКД добавит левые соединения к результирующему SQL-запросу «на лету» и только для тех свойств, которые пользователь реально выбрал на вкладке «Выбранные поля». Если пользователю в текущий момент не нужна «Доплата», система не будет нагружать сервер лишними соединениями.

Решение 4. Оптимизация через Временные Таблицы (Для сложных запросов)

Если логика вашего отчета слишком запутана (как в сообщении №10 автора темы, где соединяются срезы последних, ответственные лица и плановые начисления), хорошим тоном является предварительное разделение логики с использованием временных таблиц. Это позволяет СУБД строить более точные индексы и изолирует выборку свойств.

Рассмотрим по шагам схему построения такого запроса:

Создаем временную таблицу ВТ_СвойстваЗП, в которую выбираем только свойства ЗП. Обязательно индексируем поле связи.
Создаем временную таблицу ВТ_СвойстваДепартамент аналогичным образом.
В основном запросе соединяем таблицу сотрудников с подготовленными легкими временными таблицами.

Посмотрим на пример реализации первого пакета запроса:


// Шаг 1. Получаем свойства ЗП во временную таблицу
ВЫБРАТЬ
    ЗначенияСвойств.Объект КАК Объект,
    ЗначенияСвойств.Значение КАК ЗначениеЗП
ПОМЕСТИТЬ ВТ_СвойстваЗП
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойств
ГДЕ
    ЗначенияСвойств.Свойство = &СвойствоЗП
ИНДЕКСИРОВАТЬ ПО
    Объект
;

// Шаг 2. Получаем свойства Департамента во временную таблицу
ВЫБРАТЬ
    ЗначенияСвойств.Объект КАК Объект,
    ЗначенияСвойств.Значение КАК ЗначениеДепартамент
ПОМЕСТИТЬ ВТ_СвойстваДепартамент
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойств
ГДЕ
    ЗначенияСвойств.Свойство = &СвойствоДепартамент
ИНДЕКСИРОВАТЬ ПО
    Объект
;

// Шаг 3. Соединяем в основном запросе
ВЫБРАТЬ
    Работники.Регистратор КАК Регистратор,
    ЕСТЬNULL(ВТ_СвойстваЗП.ЗначениеЗП, 0) КАК ЗП,
    ЕСТЬNULL(ВТ_СвойстваДепартамент.ЗначениеДепартамент, 0) КАК Департамент
ИЗ
    РегистрСведений.Работники КАК Работники
    
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СвойстваЗП КАК ВТ_СвойстваЗП
        ПО Работники.Регистратор = ВТ_СвойстваЗП.Объект
        
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СвойстваДепартамент КАК ВТ_СвойстваДепартамент
        ПО Работники.Регистратор = ВТ_СвойстваДепартамент.Объект

Этот подход гарантирует высокую скорость выполнения запроса, так как временные таблицы содержат только необходимый СУБД узкий набор данных и предварительно проиндексированы по полю связи Объект.

Резюме

Для решения проблемы параллельного вывода свойств объектов без потери основных записей придерживайтесь следующих правил:

Никогда не накладывайте отборы на поля таблиц левого соединения в секции ГДЕ. Это превращает запрос во внутреннее соединение.
Если используете левые соединения, переносите условия фильтрации по конкретным свойствам в секцию связи ПО.
Для вывода большого количества свойств (более 3-4) используйте метод группировки с конструкцией ВЫБОР во избежание множественных левых соединений.
Для отчетов на СКД всегда отдавайте предпочтение штатному механизму характеристик — он работает наиболее эффективно и динамично.

← На главную