В процессе разработки на платформе 1С:Предприятие программисты часто сталкиваются с необходимостью фильтрации динамически сформированных данных. Использование объекта ПостроительЗапроса для наложения отборов на объект ТаблицаЗначений — стандартный и очень удобный прием. Однако попытка наложить отбор по списку значений на колонку, которая сама по себе хранит коллекции (например, вложенные списки или массивы), гарантированно приводит к тому, что фильтр просто игнорируется или система возвращает пустой результат. Кроме того, при работе со строковыми полями разработчики часто наступают на грабли «неограниченной длины», получая системные ошибки СУБД.
В этой статье мы подробно разберем, почему возникают эти ограничения, как устроен механизм сравнения на уровне СУБД и какие архитектурные шаблоны помогут нам элегантно и быстро решить эту задачу.
Выясним первопричину поведения платформы. Когда мы передаем объект ТаблицаЗначений в качестве источника данных для ПостроительЗапроса через ОписаниеИсточникаДанных, система пытается построить внутреннюю реляционную модель этой таблицы для выполнения SQL-подобных операций.
Если одна из колонок таблицы имеет тип СписокЗначений, СУБД и внутренний движок запросов 1С сталкиваются со следующими проблемами:
Произвольный.ВидСравнения.ВСписке система ожидает, что значение в проверяемой ячейке будет сопоставляться с элементами из переданного набора отбора. То есть проверяется условие: ЗначениеЯчейки = ЭлементСпискаОтбора1 ИЛИ ЗначениеЯчейки = ЭлементСпискаОтбора2. Поскольку в ячейке находится сложный объект (ссылка на экземпляр СписокЗначений в оперативной памяти), он никогда не будет равен примитивному типу (числу, строке или ссылке на элемент справочника) из отбора.Именно поэтому код, в котором мы пытаемся напрямую сопоставить колонку-список со списком-отбором, не выдает синтаксических ошибок, но при этом возвращает абсолютно все строки исходной таблицы значений.
Прежде чем перейти к решению, рассмотрим еще одну важную смежную проблему, которая часто возникает при попытке обойти это ограничение путем преобразования списков в строки.
Пытаясь обойти ограничение коллекций, разработчики часто конвертируют список значений в строку (например, соединяя элементы через точку с запятой: «1; 2; 3»). Однако здесь возникает другая критическая ошибка — Недопустимый тип сравнения при установке вида сравнения ВидСравнения.Равно.
Проанализируем причину. Если при добавлении колонки в таблицу значений мы пишем:
ТаблицаКлиентов.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка"));
Мы создаем колонку с типом строка неограниченной длины. На уровне СУБД такие поля позиционируются как тяжелые типы (типа MEMO, CLOB или NTEXT). Большинство систем управления базами данных принципиально запрещают операцию точного сравнения (оператор =) для таких типов по соображениям производительности. Допускается только поиск подстроки через операторы поиска (в терминах 1С это виды сравнения Содержит, Не Содержит, Подобно).
Если мы попытаемся применить к такой колонке отбор с видом сравнения ВидСравнения.Равно или ВидСравнения.ВСписке, платформа 1С выбросит исключение. Чтобы этого избежать, всегда явно ограничивайте длину строковых колонок при описании типов:
КвалификаторСтроки = Новый КвалификаторыСтроки(150);
ОписаниеТипаСтрока = Новый ОписаниеТипов("Строка", , КвалификаторСтроки);
ТаблицаКлиентов.Колонки.Добавить("Наименование", ОписаниеТипаСтрока);
Теперь, когда мы детально изучили ограничения типов данных, перейдем к архитектурно правильным сценариям решения нашей основной задачи.
Вместо того чтобы хранить список секторов («1», «2») внутри одной ячейки клиента («Иван»), нам необходимо денормализовать структуру данных. В реляционных базах данных связь «многие-ко-многим» всегда реализуется через промежуточную таблицу связей. Каждому сектору клиента должна соответствовать отдельная строка в таблице значений.
Рассмотрим пример реализации. Перейдем от структуры «Один клиент — Список секторов» к плоской структуре «Клиент — Сектор»:
// 1. Создаем правильное описание типов с квалификаторами
КвалификаторСтроки = Новый КвалификаторыСтроки(150);
ТипСтрока = Новый ОписаниеТипов("Строка", , КвалификаторСтроки);
ТипЧисло = Новый ОписаниеТипов("Число");
// 2. Инициализируем плоскую таблицу связей
ТаблицаСвязей = Новый ТаблицаЗначений;
ТаблицаСвязей.Колонки.Добавить("Клиент", ТипСтрока);
ТаблицаСвязей.Колонки.Добавить("Сектор", ТипЧисло);
// 3. Заполняем плоскую таблицу (разворачиваем списки в строки)
// Допустим, у нас есть клиент "Иван" со списком секторов [1, 2]
НоваяСтрока = ТаблицаСвязей.Добавить();
НоваяСтрока.Клиент = "Иван";
НоваяСтрока.Сектор = 1;
НоваяСтрока = ТаблицаСвязей.Добавить();
НоваяСтрока.Клиент = "Иван";
НоваяСтрока.Сектор = 2;
// Добавим еще клиента "Петр" с сектором 3
НоваяСтрока = ТаблицаСвязей.Добавить();
НоваяСтрока.Клиент = "Петр";
НоваяСтрока.Сектор = 3;
Теперь настроим ПостроительЗапроса для работы с этой нормализованной таблицей. Наша цель — отобрать клиентов, чьи сектора входят в искомый список (например, сектора 2 и 3):
// Подготовим список отбора
СектораОтбор = Новый СписокЗначений;
СектораОтбор.Добавить(2);
СектораОтбор.Добавить(3);
// Инициализируем построитель запроса
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТаблицаСвязей);
// Включаем доступность поля "Сектор" для отбора
Построитель.ДоступныеПоля.Сектор.Отбор = Истина;
// Добавляем отбор
НовыйОтбор = Построитель.Отбор.Добавить("Сектор");
НовыйОтбор.ВидСравнения = ВидСравнения.ВСписке;
НовыйОтбор.Значение = СектораОтбор;
НовыйОтбор.Использование = Истина;
// Выполняем построитель и выгружаем результат
Построитель.Выполнить();
РезультатОтбора = Построитель.Результат.Выгрузить();
Поскольку мы развернули данные, один и тот же клиент может встретиться в результате несколько раз, если у него совпало более одного сектора из отбора. Чтобы вернуть уникальный список клиентов, применим метод свертки:
РезультатОтбора.Свернуть("Клиент");
// Проверим результат
Для Каждого СтрокаРезультата Из РезультатОтбора Цикл
Сообщить("Найден клиент по отбору: " + СтрокаРезультата.Клиент);
КонецЦикла;
Этот способ является самым быстрым и производительным на больших объемах данных, так как СУБД мгновенно обрабатывает простые типы данных при наложении фильтров.
Если плоская таблица является лишь промежуточным звеном и результаты отбора нужно соединять с реальными таблицами базы данных, лучшим решением будет использование полноценного запроса с временными таблицами. Мы передаем плоскую таблицу связей в запрос в качестве параметра, индексируем ее и выполняем внутреннее соединение с таблицей искомых секторов.
Посмотрим на пример такого запроса:
// Создаем запрос
Запрос = Новый Запрос;
// Передаем нашу плоскую таблицу связей в параметры запроса
Запрос.УстановитьПараметр("ТаблицаСвязей", ТаблицаСвязей);
Запрос.УстановитьПараметр("ИскомыеСектора", СектораОтбор);
Запрос.Текст =
"ВЫБРАТЬ
| ТЗ.Клиент КАК Клиент,
| ТЗ.Сектор КАК Сектор
|ПОМЕСТИТЬ ВТ_СвязиКлиентов
|ИЗ
| &ТаблицаСвязей КАК ТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ РАЗЛИЧНЫЕ
| ВТ.Клиент КАК Клиент
|ИЗ
| ВТ_СвязиКлиентов КАК ВТ
|ГДЕ
| ВТ.Сектор В (&ИскомыеСектора)";
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
Этот метод гарантирует максимальную гибкость. Мы можем легко расширить его логику: например, выбирать только тех клиентов, у которых в наличии есть абсолютно все сектора из искомого списка, используя группировку по клиенту и условие ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Сектор) = &КоличествоИскомыхСекторов.
Если ваша исходная таблица содержит небольшое количество строк (в пределах 5 000 – 10 000 записей), использование тяжеловесных механизмов вроде ПостроительЗапроса или менеджера временных таблиц может быть избыточным. Проще и быстрее выполнить фильтрацию простым программным перебором в памяти.
Разберем, как сделать это максимально эффективно, избегая лишних итераций:
// Создаем пустую таблицу-результат с аналогичной структурой
ТаблицаРезультат = ТаблицаКлиентов.СкопироватьКолонки();
// Наш целевой набор секторов для отбора
СектораОтбор = Новый СписокЗначений;
СектораОтбор.Добавить(3);
СектораОтбор.Добавить(4);
// Алгоритм ручного поиска пересечений коллекций
Для Каждого СтрокаКлиента Из ТаблицаКлиентов Цикл
// Если колонка пустая или содержит не СписокЗначений, пропускаем строку
Если ТипЗнч(СтрокаКлиента.Сектора) <> Тип("СписокЗначений") Тогда
Продолжить;
КонецЕсли;
ЕстьСовпадение = Ложь;
// Перебираем элементы списка секторов текущего клиента
Для Каждого ЭлементСписка Из СтрокаКлиента.Сектора Цикл
// Метод НайтиПоЗначению работает чрезвычайно быстро
Если СектораОтбор.НайтиПоЗначению(ЭлементСписка.Значение) <> Неопределено Тогда
ЕстьСовпадение = Истина;
Прервать; // Нам достаточно хотя бы одного совпадения для успешного отбора
КонецЕсли;
КонецЦикла;
// Если пересечение множеств найдено, копируем строку в результат
Если ЕстьСовпадение Тогда
ЗаполнитьЗначенияСвойств(ТаблицаРезультат.Добавить(), СтрокаКлиента);
КонецЕсли;
КонецЦикла;
Сообщить("Программный отбор завершен. Найдено строк: " + ТаблицаРезультат.Количество());
Благодаря использованию встроенного метода НайтиПоЗначению() и оператора прерывания цикла Прервать, этот алгоритм мгновенно отработает на любых локальных пользовательских данных без лишней нагрузки на сервер СУБД.
Подводя итоги нашего анализа, выделим ключевые правила работы с отборами в 1С:
СписокЗначений, Массив, Структура) в ячейках таблиц значений, если планируете накладывать на эти колонки отборы через ПостроительЗапроса или КомпоновщикНастроекКомпоновкиДанных.Равно или ВСписке по строковым полям, всегда явно задавайте квалификаторы длины строк при объявлении колонок в таблице значений.