Skip to content

Complex expressions

Complex expressions are used to define SQL expressions. Complex expressions are converted into SQL code in run-time.

Where complex expressions can be utilized:

  • in report filters;
  • when working with ORM to avoid writing raw SQL (for developers)

Note for developers

It's possible to build expressions with Espo\ORM\Query\Part\Expression class. See here.

In this article:

Functions

Function names should be in UPPER_CASE. You need to append the trailing colon character to a function name. There should not be any whitespace between function name and parentheses.

Examples

CONCAT:(firstName, ' ', lastName)
IF:(EQUAL:(status, 'Complete'), 'Complete', 'Not Complete')`

Functions:

Conditional

IF

E.g. IF:(a, '1', '0') – if 'a' is true then return '1'; return '0' otherwise.

SWITCH

As of v7.4.

An equivalent of 'CASE WHEN ... THEN ... ELSE ... END'. Even arguments define 'WHEN' conditions, following odd arguments define 'THEN' values. The last unmatched argument defines 'ELSE'.

E.g. SWITCH:(EQUAL:(monthNumber, 1), 'Jan', EQUAL:(monthNumber, 2), 'Feb', EQUAL:(monthNumber, 3), 'Mar', 'Other').

MAP

As of v7.4.

An equivalent of 'CASE ... WHEN ... THEN ... ELSE ... END'. Odd arguments define keys, following even arguments define mapped values. The last unmatched argument defines 'ELSE'.

E.g. MAP:(monthNumber, 1, 'Jan', 2, 'Feb', 3, 'Mar', 'Other').

Comparison

LIKE

Pattern matching.

E.g. LIKE:(name, 'Cloud%') will give true for 'name' equal to 'Cloud Basic' and 'Cloud Enterprise'.

NOT_LIKE

Negation of LIKE.

EQUAL

Check whether two arguments are equal.

E.g. EQUAL:(status, 'Complete') corresponds to SQL statement status = 'Complete'.

NOT_EQUAL

Negation of equal.

GREATER_THAN

Check whether a > b.

E.g. GREATER_THAN:(amountConverted, 1000.00) corresponds to SQL statement amountConverted > 100.00.

LESS_THAN

Check whether a < b.

GREATER_THAN_OR_EQUAL

Check whether a >= b.

LESS_THAN_OR_EQUAL

Check whether a <= b.

IS_NULL

Check whether value is NULL.

E.g. IS_NULL:(sicCode) – checks whether Sic Code field is not set.

IS_NOT_NULL

Negation of IS_NULL.

IN

Check whether a value is within a set of values.

E.g. IN:(stage, 'Closed Won', 'Closed Lost') will give true is stage is 'Closed Won' or 'Closed Lost'.

NOT_IN

Negation of IN.

COALESCE

Returns the first non-NULL value in the list.

E.g. COALESCE:(dateStart, createdAt) will return value of createdAt if dateStart is not set.

IFNULL

If the first argument is not NULL, it returns it; otherwise it returns the second argument.

E.g. IFNULL:(name, '') will return empty string if name is NULL.

NULLIF

If the first argument equals the second, returns NULL, otherwise returns the first argument.

E.g. NULLIF:(name, '') will return NULL if name is empty string.

GREATEST

As of v7.4.

Returns the max value from a list of expressions.

LEAST

As of v7.4.

Returns the min value from a list of expressions.

Date & time

MONTH_NUMBER

Month number. From 1 to 12.

WEEK_NUMBER_0

Week number. Sunday is a first day of week.

WEEK_NUMBER_1

Week number. Monday is a first day of week.

DAYOFWEEK

Number from 1 to 7.

DAYOFMONTH

Number from 1 to 31.

YEAR

Year number.

YEAR_X

Fiscal year number, where X is a number (between 1 and 12) of the fiscal year start. E.g. YEAR_3.

HOUR

Number from 0 to 23.

MINUTE

Number from 0 to 59.

MONTH

Month number w/ year. E.g. 2019-05.

QUARTER

Quarter number w/ year. E.g. 2019_1.

QUARTER_X

Fiscal quarter number, where X is a number (between 1 and 12) of the fiscal year start. E.g. QUARTER_3.

WEEK_0

Week number w/ a year. Sunday is a first day of week.

E.g. 2018/4.

WEEK_1

Week number w/ a year. Monday as a first day of week.

NOW

Current date and time.

TZ

Time zone converion.

E.g. TZ:(createdAt, -10.5) will apply -10:30 offset.

UNIX_TIMESTAMP

UNIX timestamp of a date-time argument or a current timestamp if no argument passed.

TIMESTAMPDIFF_YEAR

Difference between two days in years.

E.g. TIMESTAMPDIFF_YEAR:(createdAt, modifiedAt).

TIMESTAMPDIFF_MONTH

Difference between two days in months.

TIMESTAMPDIFF_WEEK

Difference between two days in weeks.

TIMESTAMPDIFF_DAY

Difference between two days in days.

TIMESTAMPDIFF_HOUR

Difference between two days in hours.

TIMESTAMPDIFF_MINUTE

Difference between two days in minutes.

TIMESTAMPDIFF_SECOND

Difference between two days in seconds. Available since v6.0.0.

DATE

Returns a date part of a date-time value. Available since v6.0.0.

String

CONCAT

Concatenate. E.g. CONCAT:(firstName, ' ', lastName).

LEFT

Returns a specified number of characters from the left of the string. E.g. LEFT:(someTextColumn, 1000). As of v6.1.0.

LOWER

Convert to lower case.

UPPER

Convert to upper case.

TRIM

Remove leading and trailing spaces.

CHAR_LENGTH

Number of characters in string.

BINARY

Converts a value to a binary string.

EQUAL:('test', 'Test') returns true.

EQUAL:(BINARY:('test'), 'Test') returns false.

Avalilable since v5.7.0.

REPLACE

Replaces all the occurrences of a substring within a string.

REPLACE:('haystack', 'needle', 'replaceWith')

Available since v6.0.0.

Math

ADD

Addition. E.g. ADD:(2, 2) corresponds to 2 + 2.

SUB

Subtraction. E.g. SUB:(2, 1) corresponds to 2 - 1.

MUL

Multiplication. E.g. MUL:(2, 2) corresponds to 2 * 2.

DIV

Division. E.g. DIV:(2, 4) corresponds to 2 / 4.

MOD

Returns remainder of a number divided by another number. E.g. MOD:(6, 4) corresponds to 6 % 4.

FLOOR

The largest integer value not greater than the argument.

CEIL

The smallest integer value greater than or equal to the argument.

ROUND

Round a number to a specified number of decimal places. E.g. ROUND:(value, 2).

Logical

OR

E.g. OR:(a, b, c) corresponds to SQL statement a OR b OR c.

AND

E.g. AND:(a, b, c) corresponds to SQL statement a AND b AND c.

NOT

E.g. NOT:(a) corresponds to SQL statement NOT a.

Custom functions

It is possible to add custom functions. An implementation class name for a custom function should be defined in metadata by the path app > orm > platforms > {platform} > functionConverterClassNameMap > {function}. The class should implement Espo\ORM\QueryComposer\Part\FunctionConverter interface.

Attribute names

Attribute names usually are same as a system field names. Fields are listed at Administration > Entity Manager > ... > Fields.

Attribute names should be in lowerCamelCase.

Field types having attributes named differently:

  • Link: fieldId, fieldName.
  • Link-Parent: fieldId, fieldType.
  • Currency: field, fieldCurrency.

Where field is the name of the field.

More info about attributes here.