Creatio development guide
PDF
This documentation is valid for Creatio version 7.15.0. We recommend using the newest version of Creatio documentation.

The EntitySchemaQuery class. 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.

comparisonType Terrasoft.ComparisonType Is a comparison operation type.
leftColumnPath String Is a path to verified column relative to root schema rootSchema.
rightColumnPath String Is a path to column filter relative to root schema rootSchema.
createInFilter(leftExpression, rightExpressions)

Creates In-filter instance.

leftExpression Terrasoft.BaseExpression Is an expression, verified in a filter.
rightExpressions Terrasoft.BaseExpression[] Is an array of expressions that will be compared with left expresion.
createBetweenFilter(leftExpression, rightLessExpression, rightGreaterExpression)

Creates Between-filter instance.

leftExpression Terrasoft.BaseExpression Is an expression, verified in a filter.
rightLessExpression Terrasoft.BaseExpression Is an initial expression of filtration range.
rightGreaterExpression Terrasoft.BaseExpression Is a final expression of filtration range.
createCompareFilter(comparisonType, leftExpression, rightExpression)

Creates Compare-filter instance.

comparisonType Terrasoft.ComparisonType Is a type of comparison operation.
leftExpression Terrasoft.BaseExpression Is an expression, verified in a filter.
rightExpression Terrasoft.BaseExpression Is a filtration expression.
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.

columnPath String Path to column, for the expression of which the filter is built.
createIsNotNullFilter(leftExpression)

Creates IsNull-filter instance.

leftExpression Terrasoft.BaseExpression Is an expression that is verified on IS NOT NULL condition.
createIsNullFilter(leftExpression)

Creates IsNull-filter instance.

leftExpression Terrasoft.BaseExpression Is an expression that is verified on IS NULL condition.
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.

columnPath String Is a path to the verified column, for expression of which the filter is built.
createColumnFilterWithParameter(comparisonType, columnPath, paramValue)

Creates Compare-filter instance for comparison of the column with set value.

comparisonType Terrasoft.ComparisonType Is a type of comparison operation.
columnPath String Is a path to the verified column relative to root schema rootSchema.
paramValue Mixed Is a parameter value.
createColumnInFilterWithParameters(columnPath, paramValues)

Creates In0filter instance for verification of coincidence of set column value with the value of one of parameters.

columnPath String Is a path to the verified column relative to root schema rootSchema.
paramValues Array Is a parameter value array.
createColumnBetweenFilterWithParameters(columnPath, lessParamValue, greaterParamValue)

Creates Between-filter instance that verifies whether the column is within set range.

columnPath String Is a path to the verified column relative to root schema rootSchema.
lessParamValue Mixed Initial value of the filter.
greaterParamValue Mixed Final value of the filter.
createColumnIsNotNullFilter(columnPath)

Creates IsNull-filter for verification of set column.

columnPath String Is a path to the verified column relative to root schema rootSchema.
createColumnIsNullFilter(columnPath)

Creates IsNull-filter instance for verification of set column.

columnPath String Is a path to verified column relative to root schema rootSchema.
createPrimaryDisplayColumnFilterWithParameter(comparisonType, paramValue)

Creates filter object for comparison of primary column for the purpose of displaying with parameter value.

comparisonType Terrasoft.ComparisonType Comparison type.
paramValue Mixed 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 The EntitySchemaQuery class. Building of paths to columns).

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

See Also

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?