Инструмент для построения условий
В прошлой статье «Игра со списками условий» я показал, как строить запросы. В качестве примера я взял несколько условий, написанных на SQL, и использовал их в запросе.
Но пользователь не умеет писать на SQL, пользователя нельзя подпускать к написанию запросов. Но как же быть, если очень надо? Я предлагаю дать пользователю простой инструмент, результаты которого уже трансформировать в SQL.
Накидаем список вопросов:
1. Что нужно пользователю?
2. Какими понятиями может оперировать пользователь?
3. Как нам преобразовать эти понятия в запрос?
4. Как нам проверить, что условие составленное пользователем охватывает все множество записей?
5. Как нам сделать не одноразовый инструмент?
В своей практике я прошел через несколько этапов: от простых перечислений в запросах, до построения списков в таблицах, но иногда возникали задачи, где простых списком мало. И тогда я придумал следующий механизм:
Создаются группы из разнотипных элементов (если требуется). Все элементы в такой группе объединяются по ИЛИ.
В одно условие входит одна или несколько групп объединяющихся по И и, возможно, одна или несколько групп отрицающих. Примерно так:
(a1 or a2 or a3) and (b1 or b2) and not (c1 or c2) and not (d1)
Теперь об элементах:
1. Один элемент — имя поля и его значение. (Учтите, что ко всему описанному ниже нужно будет прикрутить пользовательский интерфейс, а там нужно использовать понятные обозначения: не «pa.city», а «Город доставки», …
2. Можно сделать элементы — константы (просто прописывается программистом SQL-выражение). В качестве констант очень полезна положительное условие (1=1). С ее помощью можно выбрать все строки, построить множество дополнений, блокировать условия.
3. Элемент — ссылка на другое условие. Это очень важный и сильный элемент.
Пример: Усл1 = (a1 or a2 or a3), тогда Усл2 = (1=1) and not (Усл1) является дополнением множества Усл1.
Оба эти условия перекрывают все множество записей, и при изменении Усл1 автоматически меняется Усл2
В прошлой своей статье я приводил примеры некоторых условий:
if OBJECT_ID('tempdb..#Conditions') is not null drop table #Conditions
create table #Conditions (
ConditionID int identity(1,1) primary key,
Name varchar(100),
[Text] varchar(200),
[Value] varchar(200)
)
insert #Conditions(Name, [Text], [Value]) select 'Доставка в Берлин и Бонн байков', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name in (''Byke'')' , 'sd.OrderQty * pp.Weight'
insert #Conditions(Name, [Text], [Value]) select 'Доставка в Берлин и Бонн других товаров', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name not in (''Byke'')', 'sd.OrderQty * pp.Weight'
insert #Conditions(Name, [Text], [Value]) select 'Доставка в другие города', 'pa.city not in (''Berlin'', ''Bonn'')', 'sd.OrderQty * pp.Weight'
В свете этой статьи предлагаю построить следующую таблицу (для полномасштабного проекта больше таблиц):
if OBJECT_ID('tempdb..#ConditionAtoms') is not null drop table #ConditionAtoms
create table #ConditionAtoms (
AtomID int identity(1,1) primary key,
ConditionID int,
GroupNumber int, -- номер группы, Номер > 0 группа положительных условий, Номер < 0 - группа отрицания
Field varchar(50), -- Немного упрощаю
Value varchar(1000) -- Здесь могут хранится значения разных типов
)
Если переписать текст условий в лоб, то должно получиться где-то так
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Berlin'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Bonn'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 2, 'ppc.Name', 'Byke'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 1, 'pa.city', 'Berlin'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 1, 'pa.city', 'Bonn'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, -1, 'ppc.Name', 'Byke'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, 1, '' , '1=1'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, 'pa.city', 'Berlin'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, 'pa.city', 'Bonn'
—, а можно и по-другому
-- Объединяем Берлин и Бонн в первое условие
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Berlin'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 1, 1, 'pa.city', 'Bonn'
-- Ограничиваем первое условие только байками
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 1, '', '1'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 2, 'ppc.Name', 'Byke'
-- Исключаем из первого условия, то что попало во второе (байки)
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, 1, '', '1'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, '', '2'
-- А все остальное и есть наше последнее условие
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 4, 1, '', '1=1'
insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 4, -1, '', '1'
Чтоб не перегружать текст SQL-кодом остановлюсь не нескольких моментах:
1. Как видно из последнего кода, для построения Условия № 3, нужно построить все предыдущие, а для этого нужно найти сначала Условия, которые не содержат вложенных условий, на следующем шаге нужно строить только те условия, для которых известны все вложенные условия и т.д.
2. Защититесь от зацикливания
3. Защититесь от удаленных условий
4. Для оптимизации для некоторых типов полей значения можно объединять в «IN».
5. Для построения запросов очень удобно пользоваться инструкцией WITH и оконными функциями.
Вот и сама функция для построения условия (обратите внимание, что я вместо временной таблицы, использовал постоянную).
create function test.ConditionSQL( @ConditionID int , @Lvl int = 0 )
returns varchar(max)
begin
declare @Ret varchar(max)
if @Lvl < 100
with Data as (
select *
,[SQL] = '( ' + case
when Field = '' then value
when Field = '' then test.ConditionSQL( try_convert(int, value), @Lvl+1 )
else Field + ' = ''' + Replace( Value, '''', '''''') + ''''
end + ')'
,[Row] = ROW_NUMBER() over(partition by GroupNumber order by Field )
from ConditionAtoms ca where ConditionID = @ConditionID
),
Build as (
select GroupNumber, [Row], [Sql] = convert(varchar(max), [SQL]), MaxRow = (select max([ROW]) from data d where d.GroupNumber = data.GroupNumber ) from Data where [Row] = 1
union all
select Build.GroupNumber, Data.Row, Build.SQL + ' or ' + data.SQL , Build.MaxRow
from Build
join Data on Build.GroupNumber = data.GroupNumber and Build.Row + 1 = Data.Row
)
select @Ret = iif( @Ret is null, '', @Ret + ' and ' )
+ iif( GroupNumber < 0 , ' not ', '' )
+ ' (' + [SQL] + ') '
from Build where [Row] = [MaxRow]
return IsNull( @Ret, 'Error ConditionID = ' + format(@ConditionID, '0') )
end
Сама функция немного упрощена. С помощью еще одной рекурсии можно OR по одному полю объединить в секцию IN. Можно добавить обработку не только списков, но и диапазонов, сравнения с числами и т.д. (на сколько хватит фантазии)
Пример работы функции:
print dbo.ConditionSQL(1,0)
print dbo.ConditionSQL(2,0)
print dbo.ConditionSQL(3,0)
print dbo.ConditionSQL(4,0)
---------------------------------------
(( pa.city = 'Berlin') or ( pa.city = 'Bonn'))
(( (( pa.city = 'Berlin') or ( pa.city = 'Bonn')) )) and (( ppc.Name = 'Byke'))
not (( (( (( pa.city = 'Berlin') or ( pa.city = 'Bonn')) )) and (( ppc.Name = 'Byke')) )) and (( (( pa.city = 'Berlin') or ( pa.city = 'Bonn')) ))
not (( (( pa.city = 'Berlin') or ( pa.city = 'Bonn')) )) and (( 1=1))
