EntitySchemaQuery filters handling
Glossary Item Box
A filter is a set of conditions, applied to query data display. According to SQL terms, a filter is a separate predicate (condition) of the WHERE operator.
Creation and application of filters in EntitySchemaQuery
To create simple filter in EntitySchemaQuery use CreateFilter() method that returns created Terrasoft.CompareFilter filter object. In addition to simple filters, methods for special filter types are implemented in EntitySchemaQuery (Table 1).
Table 1. — EntitySchemaQuery methods for creation of filters
| Filter creation method | |||||||||
|---|---|---|---|---|---|---|---|---|---|
|
createFilter(comparisonType, leftColumnPath, rightColumnPath)
Creates instance of Terrasoft.comparefilter class filter for comparing values of two columns.
|
|||||||||
|
createInFilter(leftExpression, rightExpressions)
Creates In-filter instance.
|
|||||||||
|
createBetweenFilter(leftExpression, rightLessExpression, rightGreaterExpression)
Creates Between-filter instance.
|
|||||||||
|
createCompareFilter(comparisonType, leftExpression, rightExpression)
Creates Compare-filter instance.
|
|||||||||
|
createExistsFilter(columnPath)
Creates Exists-filter instance for comparison of [Exists on set condition] types and sets value of expression of column, located on set path, as verified value.
|
|||||||||
|
createIsNotNullFilter(leftExpression)
Creates IsNull-filter instance.
|
|||||||||
|
createIsNullFilter(leftExpression)
Creates IsNull-filter instance.
|
|||||||||
|
createNotExistsFilter(columnPath)
Creates Exists-filter instance for comparison [Out of set condition] and set expression of the column, located in set path, as verified value.
|
|||||||||
|
createColumnFilterWithParameter(comparisonType, columnPath, paramValue)
Creates Compare-filter instance for comparison of the column with set value.
|
|||||||||
|
createColumnInFilterWithParameters(columnPath, paramValues)
Creates In0filter instance for verification of coincidence of set column value with the value of one of parameters.
|
|||||||||
|
createColumnBetweenFilterWithParameters(columnPath, lessParamValue, greaterParamValue)
Creates Between-filter instance that verifies whether the column is within set range.
|
|||||||||
|
createColumnIsNotNullFilter(columnPath)
Creates IsNull-filter for verification of set column.
|
|||||||||
|
createColumnIsNullFilter(columnPath)
Creates IsNull-filter instance for verification of set column.
|
|||||||||
|
createPrimaryDisplayColumnFilterWithParameter(comparisonType, paramValue)
Creates filter object for comparison of primary column for the purpose of displaying with parameter value.
|
The EntitySchemaQuery instance has a filter property that is a collection of filters of a given query. The filter property is the Terrasoft.FilterGroup class instance that, in its turn, is a collection of Terrasoft.BaseFilter items. To add a filter to a query, take the following actions:
- create filter instance for given query (createFilter method (), methods for creation of special type filters);
- add created filter instance or query filters collection (add() method of collection).
All filters added to the Filters collection are interconnected by the AND logical operation. With the LogicalOperation property of the filters collection, the user can specify a logical operation by which filters should be joined. The property takes the following values from Terrasoft.core.enums.LogicalOperatorType list:
- AND
- OR
The possibility for controlling filters, used in building of a resultant data set, is implemented in EntitySchemaQuery. Each filter collection item has the isEnabled property that determines whether this item takes part in building of resultant queries (true means that it takes part and false means that it doesn't take part). Similarly, the isEnabled property is also determined for the entire filter collection. Set this property to false to deactivate filtration for a query. The collection of query filters will remain unchanged. If a query filter collection is created initially, you can use different combinations for filtering queries in the future while not introducing changes directly into the collection.
Example of control of filters in query is shown below (example 1).
Example 1
// Creation of query instance with "Contact" root schema. var esq = Ext.create("Terrasoft.EntitySchemaQuery", { rootSchemaName: "Contact" }); esq.addColumn("Name"); esq.addColumn("Country.Name", "CountryName"); // Creation of the first filter instance. var esqFirstFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Country.Name", "Mexico"); // Creation of the second filter instance. var esqSecondFilter = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, "Country.Name", "USA"); // Filters will be updated by OR logical operator in query filters collection. esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.OR; // Adding created filters to collection. esq.filters.add("esqFirstFilter", esqFirstFilter); esq.filters.add("esqSecondFilter", esqSecondFilter); // This collection will include objects, i.e. query results, filtered by two filters. esq.getEntityCollection(function (result) { if (result.success) { result.collection.each(function (item) { // Processing element collection. }); } }, this); // It is indicated that the second filter will be used in building of resultant query. // This filter is not deleted from query filters collection. esqSecondFilter.isEnabled = false; // This collection will include objects, i.e. query results, filtered only by the first filter. esq.getEntityCollection(function (result) { if (result.success) { result.collection.each(function (item) { // Processing of collection items. }); } }, this);
Column paths are built in the EntitySchemaQuery filters in accordance with common rules for building paths to columns relative to root schema (described in article Building of paths to columns relative to root schema).
Examples of the use of other methods for creating filters are represented below.
Example 2
// Creation of query instance with "Contact" root schema. var esq = Ext.create("Terrasoft.EntitySchemaQuery", { rootSchemaName: "Contact" }); esq.addColumn("Name"); esq.addColumn("Country.Name", "CountryName"); // Select all contacts where country is not specified. var esqFirstFilter = esq.createColumnIsNullFilter("Country"); // Select all contacts, date of birth of which fall at the period from 1.01.1970 to 1.01.1980. var dateFrom = new Date(1970, 0, 1, 0, 0, 0, 0); var dateTo = new Date(1980, 0, 1, 0, 0, 0, 0); var esqSecondFilter = esq.createColumnBetweenFilterWithParameters("BirthDate", dateFrom, dateTo); // Add created filters to query collection. esq.filters.add("esqFirstFilter", esqFirstFilter); esq.filters.add("esqSecondFilter", esqSecondFilter); // This collection will include objects, i.e. query results, filtered by two filters. esq.getEntityCollection(function (result) { if (result.success) { result.collection.each(function (item) { // Processing of collection items. }); } }, this);
