Как в 1С найти номенклатуру по строгому соответствию набору свойств и их значений?

← На главную

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

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

В чем заключается проблема простых соединений

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

Если же мы попытаемся наложить простое условие в секции ГДЕ, мы отсечем строки, но не сможем проверить, что у номенклатуры нет других, лишних свойств, которые не были переданы в фильтре.

Пошаговый алгоритм решения через агрегатные функции

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

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

Реализация пакетного запроса в коде 1С

Посмотрим на практическую реализацию данного алгоритма на встроенном языке 1С:


// ПараметрыЗапроса - ТаблицаЗначений с колонками "Свойство" и "ЗначениеСвойства"
КоличествоЗаданныхПараметров = ПараметрыЗапроса.Количество();

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ПараметрыЗапроса", ПараметрыЗапроса);
Запрос.УстановитьПараметр("КоличествоЗаданныхПараметров", КоличествоЗаданныхПараметров);

Если КоличествоЗаданныхПараметров = 0 Тогда
    // Если параметры не заданы, отбираем номенклатуру вообще без свойств
    Запрос.Текст =
        "ВЫБРАТЬ
        |   СпрНоменклатура.Ссылка КАК Ссылка
        |ИЗ
        |   Справочник.Номенклатура КАК СпрНоменклатура
        |   ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
        |       ПО (СпрНоменклатура.Ссылка = ЗначенияСвойствОбъектов.Объект)
        |       И (НЕ СпрНоменклатура.ЭтоГруппа)
        |ГДЕ
        |   СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
        |
        |СГРУППИРОВАТЬ ПО
        |   СпрНоменклатура.Ссылка
        |
        |ИМЕЮЩИЕ
        |   МИНИМУМ(ВЫБОР
        |           КОГДА ЗначенияСвойствОбъектов.Свойство ЕСТЬ NULL
        |               ТОГДА 1
        |           ИНАЧЕ 0
        |       КОНЕЦ) = 1";
Иначе
    Запрос.Текст =
        "ВЫБРАТЬ
        |   ПараметрыЗапроса.Свойство КАК Свойство,
        |   ПараметрыЗапроса.ЗначениеСвойства КАК ЗначениеСвойства
        |ПОМЕСТИТЬ ВТПараметры
        |ИЗ
        |   &ПараметрыЗапроса КАК ПараметрыЗапроса
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |   СпрНоменклатура.Ссылка КАК Ссылка,
        |   ЗначенияСвойствОбъектов.Свойство КАК Свойство,
        |   ЗначенияСвойствОбъектов.Значение КАК ЗначениеСвойства
        |ПОМЕСТИТЬ ИсходнаяТаблицаДанных
        |ИЗ
        |   Справочник.Номенклатура КАК СпрНоменклатура
        |   ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
        |       ПО (СпрНоменклатура.Ссылка = ЗначенияСвойствОбъектов.Объект)
        |ГДЕ
        |   СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
        |;
        |
        |////////////////////////////////////////////////////////////////////////////////
        |ВЫБРАТЬ
        |   ВложенныйЗапрос.Ссылка КАК Ссылка
        |ИЗ
        |   (ВЫБРАТЬ
        |       ИсходнаяТаблицаДанных.Ссылка КАК Ссылка,
        |       СУММА(ВЫБОР
        |               КОГДА ВТПараметры.ЗначениеСвойства ЕСТЬ NULL
        |                   ТОГДА -1
        |               ИНАЧЕ 1
        |           КОНЕЦ) КАК АнализПараметров
        |   ИЗ
        |       ИсходнаяТаблицаДанных КАК ИсходнаяТаблицаДанных
        |       ЛЕВОЕ СОЕДИНЕНИЕ ВТПараметры КАК ВТПараметры
        |           ПО ИсходнаяТаблицаДанных.Свойство = ВТПараметры.Свойство
        |           И ИсходнаяТаблицаДанных.ЗначениеСвойства = ВТПараметры.ЗначениеСвойства
        |   СГРУППИРОВАТЬ ПО
        |       ИсходнаяТаблицаДанных.Ссылка) КАК ВложенныйЗапрос
        |ГДЕ
        |   ВложенныйЗапрос.АнализПараметров = &КоличествоЗаданныхПараметров";
КонецЕсли;

Результат = Запрос.Выполнить().Выгрузить();

Оптимизация производительности запроса

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

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