Condition Tool

In the last article, “A game with lists of conditions,” I showed how to build queries. As an example, I took several conditions written in SQL and used them in the query.

But the user cannot write to SQL, the user cannot be allowed to write queries. But what if you really need? I propose to give the user a simple tool, the results of which are already transformed into SQL.

Throw a list of questions:
')
1. What does the user need?
2. What concepts can the user operate on?
3. How do we convert these concepts into a query?
4. How can we check that the condition created by the user covers the entire set of records?
5. How do we make a non-disposable instrument?

In my practice, I went through several stages: from simple enumerations in queries to building lists in tables, but sometimes there were tasks where there was little simple list. And then I came up with the following mechanism:

Groups of different types of elements are created (if required). All elements in such a group are combined by OR.
One condition includes one or several groups uniting in an AND and, possibly, one or several groups denying. Like that:
(a1 or a2 or a3) and (b1 or b2) and not (c1 or c2) and not (d1)

Now about the elements:

1. One element - the name of the field and its value. (Note that everything described below will need to fasten the user interface, and there you need to use clear symbols: not "pa.city", but "City of delivery", ...
2. You can make elements - constants (just written by a programmer SQL-expression). The positive condition (1 = 1) is very useful as constants. With it, you can select all the lines, build a lot of additions, block conditions.
3. Element - a link to another condition. This is a very important and strong element.
Example: Imp1 = (a1 or a2 or a3), then Exp2 = (1 = 1) and not (Exp1) is the complement of the set Exp1.
Both of these conditions overlap the entire set of entries, and when the service is changed, the service is automatically changed.

In my last article I gave examples of some conditions:

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' 

In the light of this article, I propose to build the following table (for a full-scale project, more tables):

 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) --       ) 

If you rewrite the text of the conditions in the forehead, it should turn out somewhere

 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, '<SQL>' , '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' 

- and it is possible and in another way

 --        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, '<CONDITION>', '1' insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 2, 2, 'ppc.Name', 'Byke' --    ,      () insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, 1, '<CONDITION>', '1' insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 3, -1, '<CONDITION>', '2' --         insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 4, 1, '<SQL>', '1=1' insert #ConditionAtoms(ConditionID, GroupNumber, Field, Value) select 4, -1, '<CONDITION>', '1' 

So as not to overload the text with the SQL code, I’ll stop for a few moments:

1. As can be seen from the last code, to build Condition No. 3, you need to build all the previous ones, and for this you first need to find Conditions that do not contain nested conditions, the next step is to build only those conditions for which all nested conditions and .d
2. Protect against looping.
3. Protect from remote conditions
4. For optimization, for some types of field values ​​can be combined into "IN".
5. To build queries, it is very convenient to use the WITH instruction and window functions.

Here is the function itself for building the condition (note that I used a constant instead of a temporary table).

 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 = '<SQL>' then value when Field = '<CONDITION>' 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 

The function itself is a bit simplified. With another recursion, OR can be combined over a single field into an IN section. You can add processing not only lists, but also ranges, comparisons with numbers, etc. (how much fantasy is enough)

Example of the function:

 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))