В процессе разработки на платформе 1С довольно часто возникает задача фильтрации данных по принципу исключения. Типичный пример: нам необходимо выбрать все характеристики номенклатуры, которые обладают свойством «Размер», но при этом из этой выборки нужно убрать те объекты, у которых заполнено свойство «Рост». На первый взгляд задача кажется простой, но специфика хранения данных в регистрах сведений со свойствами (модель Entity-Attribute-Value) накладывает свои ограничения. Разберем подробно, как правильно строить такие запросы, проанализируем ошибки и найдем оптимальные решения.
Прежде чем переходить к коду, проанализируем структуру данных. В типовых конфигурациях (например, УТ 10.3, как в примере автора) дополнительные реквизиты хранятся в регистре сведений ЗначенияСвойствОбъектов. Каждая запись в этом регистре — это отдельная строка, связывающая Объект (характеристику), Свойство и Значение.
Рассмотрим ситуацию на примере:
Если мы напишем простое условие ГДЕ Свойство <> &СвойствоРост, система просто исключит вторую строку из результата, но Объект «Х1» все равно попадет в выборку, так как для него существует первая строка. Наша же задача — полностью исключить Объект «Х1», так как у него в принципе существует запись со свойством «Рост».
Рассмотрим наиболее понятный и логичный способ, к которому в итоге пришел автор темы. Мы выбираем все объекты с нужным размером и в условии ГДЕ указываем, что эти объекты не должны входить в список тех, у кого есть рост.
Проанализируем пример кода:
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект КАК ХарактеристикаНоменклатуры
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
ЗначенияСвойствОбъектов.Свойство = &СвойствоРазмер
И ЗначенияСвойствОбъектов.Значение = &ЗначениеРазмер
И ЗначенияСвойствОбъектов.Объект ССЫЛКА Справочник.ХарактеристикиНоменклатуры
И НЕ ЗначенияСвойствОбъектов.Объект В
(ВЫБРАТЬ
ВложенныйРегистр.Объект
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ВложенныйРегистр
ГДЕ
ВложенныйРегистр.Свойство = &СвойствоРост)
В данном примере мы сначала получаем все характеристики с заданным размером. Затем с помощью конструкции НЕ В мы «отсеиваем» те характеристики, которые встречаются в подзапросе по свойству «Рост». Это надежный метод, который дает корректный результат.
Использование ЛЕВОГО СОЕДИНЕНИЯ считается более производительным методом в SQL-системах, особенно на больших объемах данных. Однако здесь важно правильно настроить условие соединения и фильтрацию по NULL. Посмотрим, как это реализовать правильно, чтобы избежать ошибок, с которыми столкнулся автор.
Рассмотрим по шагам:
ЕСТЬ NULL).
// Помещаем отобранные по размеру объекты во временную таблицу
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект КАК Объект
ПОМЕСТИТЬ ВТ_ОтобранныеПоРазмеру
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
ЗначенияСвойствОбъектов.Свойство = &СвойствоРазмер
И ЗначенияСвойствОбъектов.Значение = &ЗначениеРазмер;
////////////////////////////////////////////////////////////////////////////////
// Исключаем те, у которых есть свойство Рост
ВЫБРАТЬ
ВТ.Объект КАК ХарактеристикаНоменклатуры
ИЗ
ВТ_ОтобранныеПоРазмеру КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК СвойстваРоста
ПО ВТ.Объект = СвойстваРоста.Объект
И СвойстваРоста.Свойство = &СвойствоРост
ГДЕ
СвойстваРоста.Объект ЕСТЬ NULL
Важный нюанс: условие СвойстваРоста.Свойство = &СвойствоРост должно находиться именно в секции ПО (условие связи), а не в секции ГДЕ. Если вынести его в ГДЕ, левое соединение превратится во внутреннее, и логика исключения перестанет работать.
Еще один профессиональный подход заключается в использовании агрегатных функций. Мы можем выбрать все записи, где свойство является либо размером, либо ростом, сгруппировать их по объекту и проверить состав этих свойств для каждого объекта.
Проанализируем ситуацию:
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект КАК ХарактеристикаНоменклатуры
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
ЗначенияСвойствОбъектов.Свойство В (&СвойствоРазмер, &СвойствоРост)
ГРУППИРОВАТЬ ПО
ЗначенияСвойствОбъектов.Объект
ИМЕЮЩИЕ
МАКСИМУМ(ВЫБОР
КОГДА ЗначенияСвойствОбъектов.Свойство = &СвойствоРазмер
И ЗначенияСвойствОбъектов.Значение = &ЗначениеРазмер
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ) = 1
И МАКСИМУМ(ВЫБОР
КОГДА ЗначенияСвойствОбъектов.Свойство = &СвойствоРост
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ) = 0
Этот метод хорош тем, что он выполняет всего один проход по таблице регистра, что может быть значительно быстрее при наличии сотен тысяч записей. Мы буквально говорим системе: «Дай мне объекты, у которых точно есть запись с Размером, но максимальный индикатор наличия Роста равен 0».
При работе с подобными запросами следует помнить о нескольких критически важных моментах, которые помогут избежать медленной работы программы:
Свойство.Наименование = "Размер". Это плохая практика. Сравнение строк в запросах работает медленно, а обращение «через точку» (Свойство.Наименование) порождает неявное соединение таблиц. Всегда передавайте в параметры запроса ссылки на элементы Плана Видов Характеристик: &СвойствоРазмер.ИНДЕКСИРОВАТЬ ПО для поля Объект. Это ускорит операцию соединения.Объект имеет составной тип. Если вы знаете, что работаете только с характеристиками, используйте условие ВЫРАЗИТЬ(Объект КАК Справочник.ХарактеристикиНоменклатуры) или фильтр по ссылке, как это сделано в примерах выше.Таким образом, мы выяснили причину неудач автора (попытка фильтровать свойства в рамках одной строки вместо анализа всей совокупности свойств объекта) и разобрали три способа решения задачи: через НЕ В, через ЛЕВОЕ СОЕДИНЕНИЕ и через ГРУППИРОВКУ. Выбор конкретного метода зависит от объема ваших данных и требований к производительности кода.