Как правильно передать Таблицу Значений в параметры запроса 1С и настроить поиск по подстроке?

← На главную

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

Однако при реализации этого механизма начинающие разработчики регулярно сталкиваются со стандартными техническими сложностями: ошибками приведения типов, неверным синтаксисом экранирования кавычек при поиске по подстроке (оператор ПОДОБНО), а также некорректной типизацией колонок ТЗ. В этой статье мы подробно разберем, как избежать этих ошибок, правильно составить запрос и оптимизировать иерархические отборы.

Почему возникали ошибки в исходном коде автора темы?

Давайте проанализируем ситуацию и выясним первопричину сбоев в работе алгоритма. В самом первом сообщении автора присутствовали две критические ошибки:

Неверная установка параметра строки поиска: Строка Запрос.УстановитьПараметр("Элемент", "%" + ТЗ + "%"); вызвала ошибку "Преобразование значения к типу Число не может быть выполнено". Это произошло из-за того, что платформа попыталась применить оператор конкатенации (сложения) к строковому литералу и сложному объекту типа ТаблицаЗначений. Платформа 1С не умеет неявно преобразовывать таблицу значений в строку.
Отсутствие жесткой типизации колонок ТЗ: Инициализация колонки выполнялась с помощью конструкции ТЗ.Колонки.Добавить("Элемент", Новый ОписаниеТипов("Строка"));. По умолчанию такое объявление создает строковое поле неограниченной длины. В СУБД (MS SQL Server, PostgreSQL) поля неограниченной длины (типа BLOB/TEXT) не могут участвовать в операциях соединения таблиц (ПО) или индексироваться. Это приводит к аппаратным ошибкам СУБД или аварийному завершению запроса.

Шаг 1. Правильное создание и жесткая типизация Таблицы Значений

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

Рассмотрим пример правильной подготовки Таблицы Значений перед передачей в запрос:


// Определяем квалификаторы строки (например, длиной 100 символов, допустимая длина зависит от ваших нужд)
КвалификаторСтроки = Новый КвалификаторыСтроки(100);
МассивТипов = Новый Массив;
МассивТипов.Добавить(Тип("Строка"));

// Создаем описание типов с учетом ограничений длины
ОписаниеСтроки = Новый ОписаниеТипов(МассивТипов, , КвалификаторСтроки);

// Инициализируем Таблицу Значений и добавляем строго типизированную колонку
ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Элемент", ОписаниеСтроки);

// Заполняем ТЗ данными из исходного массива
Для Каждого СтрокаМассива Из НашМассив Цикл
    НоваяСтрока = ТЗ.Добавить();
    НоваяСтрока.Элемент = СтрокаМассива;
КонецЦикла;

Шаг 2. Инициализация Менеджера временных таблиц

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

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


Запрос = Новый Запрос;
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;

Шаг 3. Реализация поиска по подстроке с использованием оператора ПОДОБНО

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

Рассмотрим правильное написание текста запроса с соединением таблиц:


Запрос.Текст = 
    "ВЫБРАТЬ
    |    ВременнаяТЗ.Элемент КАК Элемент
    |ПОМЕСТИТЬ ВТ_ДанныеПользователя
    |ИЗ
    |    &ТЗ КАК ВременнаяТЗ
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |    ВТ_ДанныеПользователя.Элемент КАК ИскомыйЭлемент,
    |    МТР.Ссылка КАК Ссылка,
    |    МТР.Наименование КАК Наименование
    |ИЗ
    |    Справочник.МТР КАК МТР
    |        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДанныеПользователя КАК ВТ_ДанныеПользователя
    |        ПО (МТР.Наименование ПОДОБНО ""%"" + ВТ_ДанныеПользователя.Элемент + ""%"")";

В этом примере конструкция ""%"" + ВТ_ДанныеПользователя.Элемент + ""%"" на уровне транслятора SQL превратится в операцию конкатенации строки со спецсимволами, обеспечивая корректное сопоставление по подстроке.

Шаг 4. Оптимизация иерархического отбора по родителю

Во второй части обсуждения на форуме автор пытался наложить отбор на группу (родителя) справочника, используя текстовое поле: МТР.Родитель.Наименование = &Родитель. Это крайне неоптимальное решение по следующим причинам:

Поиск по наименованию родителя работает медленно, так как текстовые поля не всегда проиндексированы должным образом.
Если искомые элементы находятся не на первом уровне вложенности выбранной группы (например, лежат в подгруппе "Расходники" внутри основной группы "Запчасти"), прямое сопоставление МТР.Родитель вернет ложь.

Правильное решение: передавать в параметры запроса непосредственную ссылку на группу справочника и использовать оператор В ИЕРАРХИИ. Он автоматически учитывает любую глубину вложенности групп и задействует системные индексы СУБД.

Модернизируем наш запрос с учетом иерархического отбора:


Запрос.Текст = 
    "ВЫБРАТЬ
    |    ВременнаяТЗ.Элемент КАК Элемент
    |ПОМЕСТИТЬ ВТ_ДанныеПользователя
    |ИЗ
    |    &ТЗ КАК ВременнаяТЗ
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |    ВТ_ДанныеПользователя.Элемент КАК ИскомыйЭлемент,
    |    МТР.Ссылка КАК Ссылка,
    |    МТР.Наименование КАК Наименование
    |ИЗ
    |    Справочник.МТР КАК МТР
    |        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДанныеПользователя КАК ВТ_ДанныеПользователя
    |        ПО (МТР.Наименование ПОДОБНО ""%"" + ВТ_ДанныеПользователя.Элемент + ""%"")
    |ГДЕ
    |    МТР.Ссылка В ИЕРАРХИИ(&РодительГруппа)";

В качестве параметра &РодительГруппа мы передаем ссылку на группу (например, элемент типа СправочникСсылка.МТР), выбранную пользователем в поле ввода на форме.

Важное предупреждение о производительности СУБД

Хотя предложенное решение полностью рабочее, важно понимать архитектурные особенности выполнения таких запросов. Использование оператора ПОДОБНО в условии соединения таблиц (ПО) заставляет СУБД выполнять операцию Nested Loops (вложенные циклы) с полным сканированием таблицы справочника (Table Scan / Index Scan).

СУБД не может применить обычные индексы по наименованию, если строка поиска начинается со спецсимвола %. Если справочник МТР содержит сотни тысяч элементов, а ваша ТЗ — более 20–30 строк, такой запрос может привести к серьезным задержкам и блокировкам СУБД.

Если вы столкнулись с падением производительности при больших объемах данных, рассмотрите следующие альтернативы:

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