Active Query Builder support area

How to best handle mandatory conditions

Andrew Kennard

9 years ago
updated 8 years ago
Completed

I doing the ground work for a CustomExpression builder

Forgetting that for the moment using the Criteria and Or columns how would you handle what I would call mandatory conditions

Simple example

You have table with fields for Total Sales, Area, Category

You want to ask of the database return me all the Total computer and TV sales that are above £100 for the following areas B1 B2 G1 G2

so in SQL

WHERE (TotalSales>100 and (Category='Computers' or Category='TV')) AND (Area='B1' or Area='B2' or Area='G1' or Area='G2')

This is an over simple example and yes I could have used the IN operator but I wanted to emphasize the mix of and's and or's

I assume all of this would have to go in the criteria cell because the Or columns are only ever Or'd with the Criteria and each other ?

If that is the case then perhaps an option to turn off the Or columns when using the custom expression builder as they probably have limited use ?

Replies 16

Oldest first
Newest first Oldest first

Andrey Zavyalov, PM

9 years ago

Hello,

Please note that implementing a custom expression editor you'll be able to modify a single cell of the Query Columns Grid. And if you'll try to assign such condition string to a single cell of the Query Columns Grid, the component wouldn't be able to act the way you expect. So, I'm afraid that this task can not be solved by means of the Custom Expression builder event.

What's worse is that you can't be sure if a mandatory condition is still present in the query or it's already been modified or removed by the end-user.

What you can do is to use the QueryTransformer API to seamlessly add these conditions after the query has been built by end-user. You can provide your own UI if you want to let end-users to handle these mandatory conditions.

Reply Link

Ah !

I had not quite realised until now that the columns grid in your component and those in MSSQL Query Designer are only really for setting simple 'Excel' style filters on columns

That's perhaps a little unfair because it obviously handles this like group ing / having etc

Taking the QueryTransformer route I assume I am going to have to show the current SQL statement elsewhere on the screen otherwise information from the above clause would not be displayed anywhere else ?

Also are you saying if I were to set the SQL property to something like the above, add some other condition to it and then get the FormattedSQL back again I could loose some of the above WHERE clause ? ie I have to take the 'simple' query generated from the columns grid and then add other conditions to it ?

I've never used them before but perhaps CTE would also be another choice here ?

Finally I've tried looking at the MSSQL query designer as I assume yours accepts similar expressions, but haven't found anything that gives a definitive list of the condtions/operators that can be used in the criteria column

Thanks as always for all of your help

Actually having tried my above pseudo example it does seem to work in terms of the SQL produced. Entering criteria in the three columns gave me this which happens to work but yes I can see if things got more complex it may not

TotalSales > 100 And

(Category Like 'S%' Or Category Like 'f%') And

(Area = 'B1' Or Area='B2')

In typing this I guess it boils down to you can do quite complex queries until you want to say some thing in column1 OR something in column2 you would not be able to do it

The above request for a list of possible conditions for the criteria column would I think help me decide on the best next stage

Actually trying a few things it is better than I thought ! and you can get most combinations

I think all that might be needed is an option to make the "relationship" between the Criteria column and the Or columns and AND instead of an OR

eg you could have a property like UseCriteriaColumnAsMandatory True/False and change the column title from Criteria to Mandatory. A bit like you have the UseMSStyleGrouping now

So it would produce (Mandatory AND (OrColumn OR OrColumn OR OrColumn)) etc
instead of at the moment (Criteria OR (OrColumn OR OrColumn OR OrColumn))

Do you think this would be a good idea ? I think this simple change would greatly increase the possible selection combinations that could be achieved

I've just had a play in MS SSMS Query Designer and pasted in the above type of mandatory query and they seem to cope with the situation by allowing duplicate column names that are not output

Actually you don't have to enter criteria in excel-style. You can type any valid boolean SQL expression to any Criteria or Or column cell. Active Query Builder just performs additional processing of the entered text: if it founds a binary comparison operator without the left-side expression, it assumes that left-side expression is omitted and puts the expression from Expression column of this row in result SQL text.

Your right, working with the QueryTransformer API you won't see your changes in the text, but I thought that you're going to build a custom expression builder, i.e. an end-user interface to add mandatory conditions. So, I supposed that users should be able to see them in your cusom experssion builder UI.

Sorry, but we aren't going to add any custom columns to the Query Columns Grid.

Thanks for the feedback, it will help me shape my approach.

In terms of "valid boolean SQL expression" are all of these valid ?

https://msdn.microsoft.com/en-US/library/ms188074(v=sql.90).aspx Comparison operators

https://msdn.microsoft.com/en-US/library/ms189773(v=sql.90).aspx Logical operators

Are there any addtional ones I've missed ?

To start with I have implemented a simple dialog for the custom expression builder for the Expression/Field column

I load the dialog with the contents of the CompletionsList

When I return one of these entries as a string and set it using NewExpression the Output column checkbox is not ticked as it would have been had I not been using the CustomExpressionBuilder and chosen it from the drop down combo

This is fine but what method should I use to tick the output column for the current row ?

Thanks

Yes, all of the mentioned operators are valid.

There is no way to tick output column programmatically. We will make it ticked automatically for new rows.