В процессе работы с 1С 7.7 часто возникает задача найти номенклатуру, которая обладает полным набором определенных характеристик. Например, нам нужно найти все товары, у которых одновременно есть свойство "Цвет" со значением "Красный", свойство "Материал" со значением "Дерево" и свойство "Производитель" со значением "Фабрика X" (подобные фильтры часто содержит отчет по остаткам ТМЦ со сравнением цен). Проблема в том, что все эти характеристики хранятся в подчиненном справочнике СвойстваНоменклатуры в виде отдельных записей, и простой запрос с условиями через И не даст нужного результата.
Рассмотрим, как решить эту задачу с помощью прямых SQL-запросов через компоненту 1С++, поскольку стандартный язык запросов 1С 7.7 не обладает необходимой гибкостью для таких операций.
Первая мысль, которая приходит в голову — написать запрос с перечислением нужных видов свойств и значений в условии WHERE. Проанализируем пример такого запроса:
SELECT
СпрН.ID AS [Аналог $Справочник.Номенклатура]
FROM
$Справочник.Номенклатура AS СпрН
LEFT JOIN
$Справочник.СвойстваНоменклатуры AS СпрСвойстваНоменкл ON СпрСвойстваНоменкл.ParentExt = СпрН.ID
WHERE
$СпрСвойстваНоменкл.ВидСвойства IN (SELECT val FROM #TempTableSvojstva)
AND $СпрСвойстваНоменкл.ЗначениеСвойства IN (SELECT val FROM #TempTableZnachen)
Этот запрос отберет номенклатуру, у которой есть хотя бы одно свойство из списка #TempTableSvojstva и хотя бы одно значение из списка #TempTableZnachen. Но он не гарантирует, что для конкретного товара будут найдены именно нужные пары "свойство-значение" и что будут присутствовать все искомые пары. Например, если мы ищем пару (Цвет, Красный) и (Материал, Дерево), этот запрос может ошибочно вернуть товар со свойствами (Цвет, Синий) и (Материал, Дерево), так как он удовлетворяет условиям по отдельности (в случае расхождения наименований пригодится программа для нечеткого сравнения строк FuzzyStringComparison).
Для корректного решения задачи нужно убедиться, что у одной номенклатуры есть несколько записей в подчиненном справочнике, и каждая из них соответствует одному из наших условий. Существует два основных подхода для этого.
Это наиболее гибкий и масштабируемый способ. Его логика заключается в том, чтобы сначала найти все подходящие записи свойств для каждой номенклатуры, а затем посчитать их количество. Если количество совпадений равно числу искомых свойств, значит, мы нашли то, что нужно.
Разберем по шагам:
ParentExt). Это позволит нам работать со всеми найденными свойствами для каждого товара как с единой группой.HAVING. Мы посчитаем количество записей в каждой группе (COUNT(*)) и сравним его с общим количеством искомых свойств. Если они равны, значит у данной номенклатуры есть все нужные нам свойства.Посмотрим на пример запроса. Допустим, мы ищем номенклатуру, у которой есть ровно 3 заданных свойства.
-- Создаем временную таблицу с искомыми парами "Свойство-Значение"
-- В реальном коде 1С эта таблица будет создаваться и заполняться программно.
-- Здесь мы используем CTE (WITH) для наглядности.
WITH vseUsloviya AS (
SELECT
t1.val AS [Svoystvo],
t2.val AS [Znachenie]
FROM
#TempTableSvojstva AS t1
CROSS JOIN -- или другой способ сопоставления, если у вас одна таблица пар
#TempTableZnachen AS t2
)
-- Основной запрос
SELECT
Svojstva.ParentExt AS [NomenklaturaID $Справочник.Номенклатура]
FROM
$Справочник.СвойстваНоменклатуры AS Svojstva WITH (NOLOCK)
-- Соединяемся с таблицей условий
INNER JOIN
vseUsloviya ON Svojstva.ВидСвойства = vseUsloviya.Svoystvo
AND Svojstva.ЗначениеСвойства = vseUsloviya.Znachenie
-- Группируем по номенклатуре
GROUP BY
Svojstva.ParentExt
-- Отбираем только те номенклатуры, у которых нашлось ровно 3 совпадения
HAVING
COUNT(*) = 3
Важный момент: число `3` в условии HAVING COUNT(*) = 3 должно быть динамическим. В коде 1С оно должно быть равно количеству записей в вашей таблице условий. Чтобы вызвать код извне, подойдет универсальный вызов отчетов, обработок - внутренних и внешних - в 1С 7.7. Этот подход легко адаптируется для поиска по любому количеству свойств, не требуя изменения структуры самого SQL-запроса.
Этот метод более "прямолинейный" и может быть интуитивно понятнее. Идея состоит в том, чтобы присоединить таблицу свойств к таблице номенклатуры столько раз, сколько свойств мы ищем. Каждое соединение будет проверять наличие одной конкретной пары "свойство-значение".
Поскольку мы используем INNER JOIN, в итоговую выборку попадет только та номенклатура, для которой удалось успешно выполнить все соединения, то есть найти все заданные свойства.
Посмотрим на пример для трех свойств:
SELECT
СпрН.ID AS [Аналог $Справочник.Номенклатура]
FROM
$Справочник.Номенклатура AS СпрН WITH (NOLOCK)
-- Проверяем наличие первого свойства
INNER JOIN
$Справочник.СвойстваНоменклатуры AS СпрСвойстваНоменкл1 WITH (NOLOCK)
ON СпрСвойстваНоменкл1.ParentExt = СпрН.ID
AND СпрСвойстваНоменкл1.ВидСвойства = &ВидСвойства1
AND СпрСвойстваНоменкл1.ЗначениеСвойства = &ЗначениеСвойства1
-- Проверяем наличие второго свойства
INNER JOIN
$Справочник.СвойстваНоменклатуры AS СпрСвойстваНоменкл2 WITH (NOLOCK)
ON СпрСвойстваНоменкл2.ParentExt = СпрН.ID
AND СпрСвойстваНоменкл2.ВидСвойства = &ВидСвойства2
AND СпрСвойстваНоменкл2.ЗначениеСвойства = &ЗначениеСвойства2
-- Проверяем наличие третьего свойства
INNER JOIN
$Справочник.СвойстваНоменклатуры AS СпрСвойстваНоменкл3 WITH (NOLOCK)
ON СпрСвойстваНоменкл3.ParentExt = СпрН.ID
AND СпрСвойстваНоменкл3.ВидСвойства = &ВидСвойства3
AND СпрСвойстваНоменкл3.ЗначениеСвойства = &ЗначениеСвойства3
Плюсы и минусы данного подхода:
INNER JOIN. Это сложнее и более подвержено ошибкам, чем простая передача параметра в метод с GROUP BY.В итоге, оба способа решают поставленную задачу, но метод с GROUP BY и HAVING является более профессиональным, гибким и предпочтительным для разработки универсальных механизмов фильтрации.