Как исключить группу справочника из результатов запроса в 1С:Решения и оптимизация

← На главную

При работе с запросами в системе «1С:Предприятие 8» разработчики часто сталкиваются с необходимостью построения сложных иерархических выборок. Типичная задача — выбрать данные по элементам справочника, входящим в одну большую группу, но при этом полностью исключить из результатов элементы из конкретной подгруппы (или сторонней группы). Для этого стандартно применяются операторы В ИЕРАРХИИ и НЕ В ИЕРАРХИИ.

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

1. Базовая реализация: совмещение условий «В ИЕРАРХИИ» и «НЕ В ИЕРАРХИИ»

Рассмотрим стандартный сценарий. Нам необходимо выбрать данные из регистра накопления (например, «Продажи») по контрагентам, которые входят в общую группу «Клиенты», но при этом исключить из выборки контрагентов из группы «Проблемные».

Для решения этой задачи в секции ГДЕ запроса совмещаются два условия через логический оператор И. Давайте посмотрим на классическую структуру такого запроса:


ВЫБРАТЬ
    ПродажиОбороты.Контрагент КАК Контрагент,
    ПродажиОбороты.СуммаОборот КАК СуммаОборот
ИЗ
    РегистрНакопления.Продажи.Обороты КАК ПродажиОбороты
ГДЕ
    ПродажиОбороты.Контрагент В ИЕРАРХИИ(&ГруппаВключаем)
    И НЕ ПродажиОбороты.Контрагент В ИЕРАРХИИ(&ГруппаИсключаем)

Важное логическое замечание: как справедливо отмечено в обсуждении на форуме, данное условие имеет практический смысл только тогда, когда исключаемая группа (&ГруппаИсключаем) физически входит в состав включающей группы (&ГруппаВключаем) либо когда вы хотите дополнительно подстраховаться от пересечения данных. Если эти группы никак не связаны и не пересекаются, второе условие будет избыточным.

2. Универсальность параметров в иерархических запросах

Часто у начинающих разработчиков возникает вопрос: обязательно ли в качестве параметров &ГруппаВключаем и &ГруппаИсключаем передавать именно группы (папки) справочника? Будет ли запрос корректно работать, если туда передать обычный элемент (не папку) или список значений?

Платформа «1С:Предприятие 8» обрабатывает оператор В ИЕРАРХИИ максимально гибко. В качестве значения параметра вы можете передавать:

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

3. Под капотом СУБД: почему «НЕ В ИЕРАРХИИ» может тормозить систему

Несмотря на простоту написания конструкции НЕ В ИЕРАРХИИ, использовать её на больших объемах данных (десятки и сотни тысяч записей в справочнике контрагентов) крайне не рекомендуется. Выясним причину этой проблемы.

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

Если мы добавляем отрицание НЕ перед оператором, для СУБД это превращается в команду исключения. В итоге оптимизатор запросов SQL-сервера часто оказывается не в состоянии использовать индексы таблиц и переходит к полному сканированию всей таблицы (Table Scan или Index Scan вместо быстрого Index Seek). На большой базе это гарантированно приведет к длительному ожиданию выполнения запроса и избыточным блокировкам.

4. Оптимальное решение через временные таблицы и левое соединение

Чтобы избежать падения производительности, мы можем переписать запрос, отказавшись от тяжелого отрицания НЕ В ИЕРАРХИИ в основном цикле фильтрации. Для этого разберем процесс оптимизации по шагам:

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

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


// Шаг 1: Собираем всех контрагентов, которых нужно исключить
ВЫБРАТЬ
    Контрагенты.Ссылка КАК Контрагент
ПОМЕСТИТЬ ВТ_Исключаемые
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ГДЕ
    Контрагенты.Ссылка В ИЕРАРХИИ(&ГруппаИсключаем)

ИНДЕКСИРОВАТЬ ПО
    Контрагент
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Основная выборка с использованием левого соединения
ВЫБРАТЬ
    ПродажиОбороты.Контрагент КАК Контрагент,
    ПродажиОбороты.СуммаОборот КАК СуммаОборот
ИЗ
    РегистрНакопления.Продажи.Обороты КАК ПродажиОбороты
    
    // Применяем левое соединение к проиндексированной временной таблице
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исключаемые КАК ВТ_Исключаемые
    ПО ПродажиОбороты.Contragent = ВТ_Исключаемые.Контрагент
ГДЕ
    ПродажиОбороты.Контрагент В ИЕРАРХИИ(&ГруппаВключаем)
    И ВТ_Исключаемые.Контрагент ЕСТЬ NULL // Исключаем совпадения

Этот подход работает значительно быстрее на базах данных любого масштаба, так как исключает операцию полного сканирования таблиц за счет предварительной индексации временной таблицы.

5. Ловушка пустых ссылок

Еще одна неочевидная проблема при работе с иерархией в запросах — передача пустой ссылки в качестве параметра. Если в параметр &ГруппаИсключаем передать значение Справочники.Контрагенты.ПустаяСсылка(), СУБД может начать сопоставление со всеми корневыми элементами справочника, у которых родитель также не заполнен (равен пустой ссылке). Это приведет к непредсказуемым результатам и колоссальной нагрузке на систему.

Чтобы этого избежать, всегда проверяйте параметры в коде перед выполнением запроса:


Если ЗначениеЗаполнено(ГруппаИсключаем) Тогда
    Запрос.УстановитьПараметр("ИспользоватьИсключение", Истина);
    Запрос.УстановитьПараметр("ГруппаИсключаем", ГруппаИсключаем);
Иначе
    Запрос.УстановитьПараметр("ИспользоватьИсключение", Ложь);
    Запрос.УстановитьПараметр("ГруппаИсключаем", Справочники.Контрагенты.ПустаяСсылка());
КонецЕсли;

В тексте запроса в таком случае используется дополнительное условие обхода:


ГДЕ
    ПродажиОбороты.Контрагент В ИЕРАРХИИ(&ГруппаВключаем)
    И (НЕ &ИспользоватьИсключение ИЛИ НЕ ПродажиОбороты.Contragent В ИЕРАРХИИ(&ГруппаИсключаем))

6. Исключение самих папок из выборки

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

Для того чтобы в результатах оставались исключительно конечные элементы справочника (карточки контрагентов), всегда добавляйте простую проверку на реквизит ЭтоГруппа:


ГДЕ
    Контрагенты.ЭтоГруппа = ЛОЖЬ

Поскольку поле ЭтоГруппа имеет булев тип данных, СУБД обрабатывает это условие мгновенно, не снижая общую скорость выполнения запроса.

7. Использование Сегментов (для УТ 11, ERP и КА)

Если вы ведете разработку в современных типовых конфигурациях фирмы «1С» на базе БСП (например, «Управление торговлей, ред. 11», «ERP Управление предприятием» или «Комплексная автоматизация»), то наиболее технологичным и производительным решением будет отказ от динамического расчета иерархии в пользу механизма Сегментов.

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