Complex filtering expressions

This section describes the functionality of the utility class FilterExpressionUtils, that provides a set of methods and utilities for building complex filtering expressions used in OntimizeWeb.

Filtering expression

A filtering expression is an object that defines an operation between two operands. The interface Expression contains the basic structure of a filtering expression. The second operand may be omitted in some expressions, for example, filtering with a NULL value.

The filtering expression structure is the following;

{
  /** The left operand. */
  lop: string | Expression

  /** The operator. */
  op: string

  /** The right operand. */
  rop?: string | Expression
}

Operators

OntimizeWeb defines a set of operators used as the op parametter of Expression for building filtering expressions. This operators cover most important operations defined by every database management system and they are the following:

Operators
AND	`TRUE` if both logical expressions are `TRUE`
EQUAL	equal to a value
LESS	less than a value
LESS EQUAL	less than or equal to a value
LIKE	`TRUE` if the operand matches a pattern
MORE	more than a value
MORE EQUAL	more than or equal to a value
NOT EQUAL	not equal to a value
NOT LIKE	`TRUE` if the operand not matches a pattern
NULL	is `NULL`
NOT NULL	is not `NULL`
OR	`TRUE` if either logical expression is `TRUE`
IN	`TRUE` if the operand is contained in the provided values

All this operator are defined statically in FilterExpressionUtils class with the operator prefix OP_ (OP_AND, OP_EQUAL, etc.).

Filter building methods

OntimizeWeb defines a set of methods for building filtering expressions automatically. This methods are the following:

buildArrayExpressionLike
Builds an `Expression` instance for filtering the provided keys LIKE the value provided.
Parameters
keys any[]	the keys
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionEquals
Builds an `Expression` instance for filtering the provided key EQUAL to the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionIsNotNull
Builds an `Expression` instance for filtering the provided key with a NOT NULL value.
Parameters
key string	the key
Returns
`Expression`	the `Expression`

buildExpressionIsNull
Builds an `Expression` instance for filtering the provided key with a NULL value.
Parameters
key string	the key
Returns
`Expression`	the `Expression`

buildExpressionLess
Builds an `Expression` instance for filtering the provided key LESS than the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionLessEqual
Builds an `Expression` instance for filtering the provided key LESS OR EQUAL to the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionLike
Builds an `Expression` instance for filtering the provided key LIKE the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionLikeEnd
Builds an `Expression` instance for filtering the provided key ENDS LIKE the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionLikeStart
Builds an `Expression` instance for filtering the provided key STARTS LIKE the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionMore
Builds an `Expression` instance for filtering the provided key MORE than the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionMoreEqual
Builds an `Expression` instance for filtering the provided key MORE OR EQUAL to the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionNotEquals
Builds an `Expression` instance for filtering the provided key NOT EQUAL to the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionNotLike
Builds an `Expression` instance for filtering the provided key NOT LIKE to the provided value.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionNullAndValue
Builds an `Expression` instance for filtering the provided key with two conditions: * The first filter the provided key with a NULL value. * The second filter the provided key EQUAL to the provided value. Both expressions are joined using the provided operator.
Parameters
key string	the key
value any	the value
Returns
`Expression`	the `Expression`

buildExpressionIn
Builds an `Expression` instance for filtering the provided key IN the provided values.
Parameters
key string	the key
values any	the values
Returns
`Expression`	the `Expression`

Utility methods

OntimizeWeb also defines a set of utility methos to help you building complex filtering expressions. This methods are the following:

buildBasicExpression
Builds a `IBasicExpression` instance from the filtering expression (`Expression`) provided.
Parameters
exp `Expression`	the filtering expression
Returns
`BasicExpression`	the basic expression

buildComplexExpression
Builds a complex `Expression` instance joining two expressions with the provided operator.
Parameters
expr1 `Expression`	the first filtering expression to join
expr2 `Expression`	the second filtering expression to join
op string	the joining operator
Returns
`Expression`	the complex filtering expression

buildExpressionFromObject
Builds an `Expression` instance from the provided object.
Parameters
obj any	the object
Returns
`Expression`	the `Expression`

buildFilterExpression
Builds an `IFilterExpression` instance from the filtering expression (`Expression`) provided.
Parameters
exp `Expression`	the filtering expression
Returns
`IFilterExpression`	the `IFilterExpression`

instanceofBasicExpression
Evaluates if the the expression provided is an instance of `IBasicExpression`.
Parameters
exp `Expression`	the filtering expression
Returns
boolean	`true` if the provided expression is an instance of `IBasicExpression`, `false` otherwise

instanceofExpression
Evaluates if an expresion is instance of `Expression`.
Parameters
exp any	the expression to evaluate
Returns
boolean	`true` if the provided expression is an instance of `Expression`, `false` otherwise

instanceofFilterExpression
Evaluates if an expresion is instance of `IFilterExpression`.
Parameters
exp any	the expression to evaluate
Returns
boolean	`true` if the provided expression is an instance of `IFilterExpression`, `false` otherwise

Examples

The following example shows how to buid a Basic expression for querying a table using the FilterExpressionUtils class. The expressions passed to the method buildBasicExpression or buildFilterExpression can be as complex as you need, in this case it is a simple like expression.

const filterExpr = FilterExpressionUtils.buildExpressionLike('EMPLOYEENAME', 'Caroline');
const basicExpr = FilterExpressionUtils.buildBasicExpression(filterExpr);

this.table.queryData(basicExpr);

The *filter sent to the backend results like the following:

"filter": {
  "@basic_expression": {
    "lop": "EMPLOYEENAME",
    "op": "LIKE",
    "rop": "%Caroline%"
  }
}

It is possible to include additional filters that doesn’t belong to the Basic expression:

const filterExpr = FilterExpressionUtils.buildExpressionLike('EMPLOYEENAME', 'Caroline');
const basicExpr = FilterExpressionUtils.buildBasicExpression(filterExpr);

basicExpr['EMPLOYEETYPEID'] = 1;

this.table.queryData(basicExpr);

"filter": {
  "@basic_expression": {
    "lop": "EMPLOYEENAME",
    "op": "LIKE",
    "rop": "%Caroline%"
  },
  "EMPLOYEETYPEID": 1
}