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 IExpression 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 | IExpression

  /** The operator. */
  op: string

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

Operators

OntimizeWeb defines a set of operators used as the op parametter of IExpression 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 IExpression instance for filtering the provided keys LIKE the value provided.

Parameters
keys

any[]

the keys

value

any

the value

Returns

IExpression

the IExpression

buildExpressionEquals

Builds an IExpression instance for filtering the provided key EQUAL to the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionIsNotNull

Builds an IExpression instance for filtering the provided key with a NOT NULL value.

Parameters
key

string

the key

Returns

IExpression

the IExpression

buildExpressionIsNull

Builds an IExpression instance for filtering the provided key with a NULL value.

Parameters
key

string

the key

Returns

IExpression

the IExpression

buildExpressionLess

Builds an IExpression instance for filtering the provided key LESS than the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionLessEqual

Builds an IExpression instance for filtering the provided key LESS OR EQUAL to the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionLike

Builds an IExpression instance for filtering the provided key LIKE the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionLikeEnd

Builds an IExpression instance for filtering the provided key ENDS LIKE the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionLikeStart

Builds an IExpression instance for filtering the provided key STARTS LIKE the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionMore

Builds an IExpression instance for filtering the provided key MORE than the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionMoreEqual

Builds an IExpression instance for filtering the provided key MORE OR EQUAL to the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionNotEquals

Builds an IExpression instance for filtering the provided key NOT EQUAL to the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionNotLike

Builds an IExpression instance for filtering the provided key NOT LIKE to the provided value.

Parameters
key

string

the key

value

any

the value

Returns

IExpression

the IExpression

buildExpressionNullAndValue

Builds an IExpression 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

IExpression

the IExpression

buildExpressionIn

Builds an IExpression instance for filtering the provided key IN the provided values.

Parameters
key

string

the key

values

any

the values

Returns

IExpression

the IExpression

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 (IExpression) provided.

Parameters
exp

IExpression

the filtering expression

Returns

BasicExpression

the basic expression

buildComplexExpression

Builds a complex IExpression instance joining two expressions with the provided operator.

Parameters
expr1

IExpression

the first filtering expression to join

expr2

IExpression

the second filtering expression to join

op

string

the joining operator

Returns

IExpression

the complex filtering expression

buildExpressionFromObject

Builds an IExpression instance from the provided object.

Parameters
obj

any

the object

Returns

IExpression

the IExpression

buildFilterExpression

Builds an IFilterExpression instance from the filtering expression (IExpression) provided.

Parameters
exp

IExpression

the filtering expression

Returns

IFilterExpression

the IFilterExpression

instanceofBasicExpression

Evaluates if the the expression provided is an instance of IBasicExpression.

Parameters
exp

IExpression

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 IExpression.

Parameters
exp

any

the expression to evaluate

Returns

boolean

true if the provided expression is an instance of IExpression, 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
}