Как написать запрос с условием к справочнику с табличной частью для поиска точного соответствия

← На главную

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

Что делать, если нам нужно найти элемент справочника, табличная часть которого точно совпадает с переданным массивом? То есть в ней должны присутствовать все элементы массива и при этом не должно быть никаких лишних строк. В этой статье мы подробно разберем, как решить эту задачу с помощью языка запросов 1С, рассмотрим различные варианты реализации, начиная от простых группировок и заканчивая оптимизированными методами для высоконагруженных систем.

Для наглядности примера представим, что у нас есть справочник Маршруты с табличной частью ТерриториальныеЗоны. В этой табличной части есть только один реквизит — ТерриториальнаяЗона. Нам необходимо найти элемент этого справочника, состав зон которого в точности равен переданному массиву &ВыбМассивТеррЗон.

Метод 1. Группировка и фильтрация через секцию ИМЕЮЩИЕ (HAVING)

Рассмотрим классический и наиболее изящный способ решения данной задачи в один запрос без создания временных таблиц. Идея заключается в том, чтобы сгруппировать записи табличной части по ссылке на справочник и применить к результату группировки сразу два условия в секции ИМЕЮЩИЕ.

Разберем по шагам логику этого подхода. Для того чтобы элемент справочника нам подошел, должны выполняться два условия:

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

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


ВЫБРАТЬ
    ТеррЗоныМаршрута.Ссылка КАК Маршрут
ИЗ
    Справочник.Маршруты.ТерриториальныеЗоны КАК ТеррЗоныМаршрута

СГРУППИРОВАТЬ ПО
    ТеррЗоныМаршрута.Ссылка

ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВЫБОР
            КОГДА ТеррЗоныМаршрута.ТерриториальнаяЗона В (&ВыбМассивТеррЗон)
                ТОГДА ТеррЗоныМаршрута.ТерриториальнаяЗона
        КОНЕЦ) = &ВыбКоличествоВМассиве
    И КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТеррЗоныМаршрута.ТерриториальнаяЗона) = &ВыбКоличествоВМассиве

Проанализируем ситуацию и разберем, как работает этот код:

Конструкция ВЫБОР КОГДА ... ТОГДА ... КОНЕЦ внутри функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) позволяет нам подсчитать только те уникальные зоны, которые входят в переданный массив. Если зона не входит в массив, СУБД возвращает значение NULL, которое агрегатная функция КОЛИЧЕСТВО просто игнорирует. Таким образом, первое условие проверяет, что в табличной части присутствуют все элементы из нашего массива.
Второе условие КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТеррЗоныМаршрута.ТерриториальнаяЗона) = &ВыбКоличествоВМассиве проверяет общее число уникальных элементов в табличной части конкретной ссылки. Если общее количество совпадает с размером массива, значит, никаких посторонних значений в ТЧ нет.

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

Метод 2. Использование Временных Таблиц и Внутреннего Соединения

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

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


// Шаг 1: Помещаем массив во временную таблицу и индексируем его
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ИсходныйМассив.Зона КАК Зона
ПОМЕСТИТЬ ВТ_ИскомыйМассив
ИЗ
    &ТаблицаМассива КАК ИсходныйМассив

ИНДЕКСИРОВАТЬ ПО
    Зона
;

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

СГРУППИРОВАТЬ ПО
    ТеррЗоныМаршрута.Ссылка
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Выбираем только те элементы, у которых совпали все количества
ВЫБРАТЬ
    Отбор.Маршрут
ИЗ
    ВТ_ПредварительныйОтбор КАК Отбор
ГДЕ
    Отбор.КолВоСовпадений = &РазмерМассива
    И Отбор.КолВоВсегоВТЧ = &РазмерМассива

В этом решении мы применили ЛЕВОЕ СОЕДИНЕНИЕ табличной части справочника с нашей временной таблицей. Если элемент ТЧ отсутствует в массиве, то поле ВТ_ИскомыйМассив.Зона примет значение NULL. В результате функция КОЛИЧЕСТВО(ВТ_ИскомыйМассив.Зона) не посчитает эту строку, и итоговое количество совпадений окажется меньше, чем общее количество строк в ТЧ (КОЛИЧЕСТВО(ТеррЗоныМаршрута.ТерриториальнаяЗона)).

Метод 3. Реляционное деление через двойное отрицание

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

Не существует ни одной строки в ТЧ, которой бы не было в искомом массиве.
Не существует ни одного элемента в массиве, которого бы не было в ТЧ этого элемента справочника.

Такой подход может работать быстрее на больших таблицах, так как СУБД способна оптимизировать логические условия исключения через операторы НЕ СУЩЕСТВУЕТ (NOT EXISTS) или левые соединения с проверкой на NULL.

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ИсходныйМассив.Зона КАК Зона
ПОМЕСТИТЬ ВТ_Массив
ИЗ
    &ТаблицаМассива КАК ИсходныйМассив
;

////////////////////////////////////////////////////////////////////////////////
// Находим ссылки, у которых есть "лишние" элементы (которых нет в массиве)
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ТЧ.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_СсылкиСЛишнимиЭлементами
ИЗ
    Справочник.Маршруты.ТерриториальныеЗоны КАК ТЧ
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Массив КАК ВТ_Массив
    ПО ТЧ.ТерриториальнаяЗона = ВТ_Массив.Зона
ГДЕ
    ВТ_Массив.Зона ЕСТЬ NULL
;

////////////////////////////////////////////////////////////////////////////////
// Находим ссылки, у которых наоборот "не хватает" элементов из массива
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ТЧ.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_СсылкиНеполные
ИЗ
    ВТ_Массив КАК ВТ_Массив
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Маршруты.ТерриториальныеЗоны КАК ТЧ
    ПО ВТ_Массив.Зона = ТЧ.ТерриториальнаяЗона
ГДЕ
    ТЧ.Ссылка ЕСТЬ NULL
;

////////////////////////////////////////////////////////////////////////////////
// Выбираем только те ссылки, которые не попали ни в один "черный список"
ВЫБРАТЬ
    Маршруты.Ссылка КАК Маршрут
ИЗ
    Справочник.Маршруты КАК Маршруты
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СсылкиСЛишнимиЭлементами КАК ИсключитьЛишние
    ПО Маршруты.Ссылка = ИсключитьЛишние.Ссылка
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СсылкиНеполные КАК ИсключитьНеполные
    ПО Маршруты.Ссылка = ИсключитьНеполные.Ссылка
ГДЕ
    ИсключитьЛишние.Ссылка ЕСТЬ NULL
    И ИсключитьНеполные.Ссылка ЕСТЬ NULL
    // Также отсекаем полностью пустые ТЧ, если искомый массив не пустой
    И Маршруты.Ссылка В (ВЫБРАТЬ РАЗЛИЧНЫЕ ТЧ.Ссылка ИЗ Справочник.Маршруты.ТерриториальныеЗоны КАК ТЧ)

Этот метод исключает сложные агрегатные группировки СГРУППИРОВАТЬ ПО и операции COUNT(DISTINCT), которые традиционно являются «тяжелыми» для СУБД. На терабайтных базах данных такой подход может показать существенный прирост производительности.

Проблема учета дубликатов (Мультимножества)

Все рассмотренные выше методы исходят из предположения, что в нашей табличной части и в массиве нет дублирующихся строк (или они несущественны, и их можно свернуть через РАЗЛИЧНЫЕ). Но что делать, если бизнес-логика вашей задачи допускает наличие дубликатов? Например, если в табличной части маршрута одна и та же зона может быть указана дважды, и искомый массив также содержит эту зону дважды.

В этом случае простого сравнения уникальных значений недостаточно. Нам необходимо сравнивать не просто состав, а пары «Значение — Количество повторений». Посмотрим, как это реализовать:


// Сворачиваем массив в ТЗ с подсчетом количества вхождений каждого элемента
// и передаем как параметр &ТаблицаМассива с колонками (Зона, Количество)

ВЫБРАТЬ
    Массив.Зона КАК Зона,
    Массив.Количество КАК Количество
ПОМЕСТИТЬ ВТ_МассивСКоличеством
ИЗ
    &ТаблицаМассива КАК Массив
;

////////////////////////////////////////////////////////////////////////////////
// Группируем ТЧ справочника для подсчета количества вхождений каждого элемента
ВЫБРАТЬ
    ТЧ.Ссылка КАК Ссылка,
    ТЧ.ТерриториальнаяЗона КАК Зона,
    КОЛИЧЕСТВО(ТЧ.НомерСтроки) КАК Количество
ПОМЕСТИТЬ ВТ_СвернутаяТЧ
ИЗ
    Справочник.Маршруты.ТерриториальныеЗоны КАК ТЧ
СГРУППИРОВАТЬ ПО
    ТЧ.Ссылка,
    ТЧ.ТерриториальнаяЗона
;

////////////////////////////////////////////////////////////////////////////////
// Сравниваем сгруппированную ТЧ и сгруппированный массив
ВЫБРАТЬ
    СвернутаяТЧ.Ссылка КАК Маршрут
ИЗ
    ВТ_СвернутаяТЧ КАК СвернутаяТЧ
    ПОЛНОЕ СОЕДИНЕНИЕ ВТ_МассивСКоличеством КАК ВТ_Массив
    ПО СвернутаяТЧ.Зона = ВТ_Массив.Зона
    И СвернутаяТЧ.Количество = ВТ_Массив.Количество

СГРУППИРОВАТЬ ПО
    СвернутаяТЧ.Ссылка

ИМЕЮЩИЕ
    // Количество совпавших строк должно быть равно уникальным строкам в искомом массиве
    КОЛИЧЕСТВО(ВТ_Массив.Зона) = &УникальныхЭлементовВМассиве
    И КОЛИЧЕСТВО(СвернутаяТЧ.Зона) = &УникальныхЭлементовВМассиве

Использование ПОЛНОГО СОЕДИНЕНИЯ по двум полям (значение и его количество) позволяет нам гарантировать, что совпали как сами элементы, так и кратность их вхождения в табличную часть.

Важные аспекты оптимизации работы запросов

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

Индексация реквизитов табличной части: Если по какому-то реквизиту ТЧ регулярно выполняется поиск (в нашем случае это ТерриториальнаяЗона), обязательно зайдите в свойства этого реквизита в Конфигураторе и установите значение свойства Индексировать в положение "Индексировать" или "Индексировать с дополнительным упорядочиванием". Без этого СУБД будет вынуждена делать полное сканирование таблицы ТЧ при каждом поиске (Full Scan).
Обработка пустого массива: Обязательно проверяйте переданный массив в коде 1С перед запуском запроса. Если массив пустой, логика "точного совпадения" должна вернуть элементы справочника с абсолютно пустой табличной частью. Описанные выше запросы при передаче пустого массива вернут пустой результат. Для обработки пустых значений лучше использовать отдельную ветку алгоритма:
```
Если МассивТеррЗон.Количество() = 0 Тогда
    // Запрос для поиска элементов с пустой ТЧ
    Запрос.Текст = 
        "ВЫБРАТЬ Ссылка ИЗ Справочник.Маршруты 
        |ГДЕ Ссылка НЕ В (ВЫБРАТЬ РАЗЛИЧНЫЕ Ссылка ИЗ Справочник.Маршруты.ТерриториальныеЗоны)";
КонецЕсли;
```
Хеширование состава табличной части (для Highload-систем): Если поиск по составу табличной части происходит постоянно и база данных содержит миллионы записей, ни один динамический запрос не обеспечит мгновенный отклик. Профессиональный стандарт решения этой проблемы — создание дополнительного индексируемого реквизита в самом справочнике (например, ХешСоставаТЧ с типом Строка, длина 32 или 40 символов). При записи элемента справочника в событии ПередЗаписью строки ТЧ сортируются, объединяются в единую строку через разделитель, и от полученной строки вычисляется хеш (например, MD5 или SHA-1). При поиске мы вычисляем хеш от искомого массива и выполняем простейший и быстрейший запрос: ГДЕ ХешСоставаТЧ = &ХешМассива.

Мы подробно разобрали все возможные способы решения этой классической задачи. Выбор конкретного метода зависит от объемов вашей информационной базы, требований к производительности и бизнес-логики учета дубликатов.