Filter Condition snippet
Description
The Filter Condition snippet is a useful tool to organize and maintain the required Filter Conditions field. It is usually used when you need to add filtering conditions to execute database statements. Database related UDFs include ajDBCountRecord, ajDBDeleteRecord etc. For more related UDFs, please refer to Reference.
To insert a Filter Condition snippet, select the Cell B3 and ensure that there are 4 extra empty rows below Cell B3 to prevent the snippet from overwriting the values below.
Go to AlchemyJ ribbon and click Insert Snippet. Under the dropdown, select Filter Condition (Filter Type 1) or Filter Condition (Filter Type 2) accordingly.
Column definition
Filter Type 1
The range defines the filter condition in the Kendo grid style. Click Insert Snippet\Filter Condition (Filter Type 1) to add the preset required format. The first row defines the header. They are Data Name, Operator 1, Search Criteria 1, And / Or, Operator 2, Search Criteria 2, Or Group.
Column Name | Description |
---|---|
Data Name (mandatory) | This column is for the data field name. |
Operator 1 (mandatory) | The following operators are supported:
|
Search Criteria 1 (mandatory) | This is to state the search criteria value. |
And / Or (optional) | If the input is AND, it will need to pass Search Criteria 1 and Search Criteria 2. If the input is OR, it will pass if it passes either Search Criteria 1 or Search Criteria 2. |
Operator 2 (optional) | The following operators are supported:
|
Search Criteria 2 (optional) | This is to state the search criteria value. |
Or Group (optional) | This is to group the list of data name filter conditions together. If Or is keyed, the filter conditions will be constructed by Or operation. If it is empty, the filter conditions will be constructed by And operation. |
Notes:
An error will occur if the range value is not in the format of the corresponding filter type.
For filter_type = 1, if the operator is available but operand is empty, it will be treated as '' (i.e. empty string).
The default number of rows is 3, you can fill more than 3 rows, it will automatically adjust the range.
Filter Type 2
The range defines a filter condition similar to the format used in MS Query. Click Insert Snippet\Filter Condition (Filter Type 2) to add the preset required format. Two columns are used to define one filter condition. So the number of columns should always be a multiple of 2. e.g. 2, 4, 6. The first column defines the operator and the second consider defines the value. For example, < 10.
Column Name | Description |
---|---|
Column Name 1 (mandatory) | This column is for the operator, fill the data name in table header, and select the operator under the column name. The following operators are supported:
|
Value 1 (mandatory) | This column is for the value. |
Column Name 2 (optional) | This column is for the operator, fill the data name in table header, and select the operator under the column name. The following operators are supported:
|
Value 2 (optional) | This column is for the value. |
Column Name 3 (optional) | This column is for the operator, fill the data name in table header, and select the operator under the column name. The following operators are supported:
|
Value 3 (optional) | This column is for the value. |
Notes:
- An error will occur if the range value is not in the format of the corresponding filter type.
- For filter_type = 2, if the operator is available but operand is empty, it will be treated as '' (i.e. empty string). It will also validate the correctness of Column Header and DataType against database schema.
- The default number of rows and columns is 3 and 6, you can fill more than 3 rows or 6 columns, it will automatically adjust the range.
Example
These examples use table "filter_test1" in database "aj_sample_mysql80". The following records exist in the table.
Click Add Component button in AlchemyJ ribbon, select DB Schema to add the DBSchema worksheet. It will prompt a window to input the table name, input 'filter_test1', and click OK. Columns information will be loaded to DBSchema worksheet. The value defined in the Data Name column could be used in the filter condition as well. Refer to example 3 as an example, comment is used instead of the column name REMARK.
Example 1 - Filter Type 1 without "Or Group"
Below is the example of the ajDBReadRecord function with Filter Type 1.
The Filter Condition as below:
SQL statement as below:
select `ID`,`NAME`,`AGE`,`ISDELETE`,`REMARK` from `filter_test1`
where `AGE` >= 23 AND `AGE` < 30;
In this example, the default value of Or Group is And . The fields condition in Filter Condition snippet is equal to where condition in SQL statement, total 4 records match in table filter_test1.
Note: For some operators, they don't need to fill the value in Search Criteria, the operator include isempty, isnotempty, isnull, isnotnull.
Example 2 - Filter Type 2 with "Or Group"
Now, you want to filter the NAME is Chai, Plain, Tomato, and AGE greater than 20.
The Filter Condition as below:
SQL statement as below:
select `ID`,`NAME`,`AGE`,`ISDELETE`,`REMARK` from `filter_test1`
where (`NAME`='Chai' AND `AGE` > 20) OR (`NAME`='Plain' AND `AGE` > 20) OR (`NAME`='Tomato' AND `AGE` > 20);
The above scene, in AlchemyJ 5.0, can use operator in to simplify, the Filter Condition as below:
SQL statement as below:
select `ID`,`NAME`,`AGE`,`ISDELETE`,`REMARK` from `filter_test1`
where (`NAME` in ('Chai','Plain','Tomato')) AND (`AGE` > 20) ;
In this example, the operator is in , you need to use the delimiter -%%- to separate multiple values, because the value of parameter-multiple-values-separator is -%%- in %%AppConfig sheet. you can modify the separator you want, such as $$.
Example 3 - Filter Type 2
Below is the example of the ajDBReadRecord function with Filter Type 2.
DBSchema worksheet as below, the value defined in the Data Name column, it could be used in the filter condition.
Case 1 - Filter condition with different column name
The Filter Condition as below:
SQL statement as below:
select `ID`,`NAME`,`AGE`,`ISDELETE`,`REMARK` from `filter_test1`
where `REMARK`='Filter Condition' AND `AGE` < 30;
In case 1, the value defined in the Data Name column could be used in the filter condition, comment is used instead of the column name REMARK.
Case 2 - Filter condition for same column name and the logic is "Or"
The Filter Condition as below:
SQL statement as below:
select `ID`,`NAME`,`AGE`,`ISDELETE`,`REMARK` from `filter_test1`
where (`NAME` = 'Chai' AND `AGE` > 20 ) OR `NAME` = 'Tofu' ;
In case 2, fill the value in B75 and C75, the operator of connecting multiple rows is Or.
When referencing the Filter Condition table, remember to include the column headers as part of the cell range as shown in Cell B82 for the formula to work.