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): As of v7.0.0 it's possible to build expressions in OOP way with
Espo\ORM\QueryParams\Parts\Expression class. See here.
In this article:
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.
CONCAT:(firstName, ' ', lastName)
IF:(EQUAL:(status, 'Complete'), 'Complete', 'Not Complete')`
IF:(a, '1', '0') – if 'a' is true then return '1'; return '0' otherwise.
LIKE:(name, 'Cloud%') will give true for 'name' equal to 'Cloud Basic' and 'Cloud Enterprise'.
Negation of LIKE.
Check whether two arguments are equal.
EQUAL:(status, 'Complete') corresponds to SQL statement
status = 'Complete'.
Negation of equal.
Check whether a > b.
GREATER_THAN:(amountConverted, 1000.00) corresponds to SQL statement
amountConverted > 100.00.
Check whether a < b.
Check whether a >= b.
Check whether a <= b.
Check whether value is NULL.
IS_NULL:(sicCode) – checks whether Sic Code field is not set.
Negation of IS_NULL.
Check whether a value is within a set of values.
IN:(stage, 'Closed Won', 'Closed Lost') will give true is stage is 'Closed Won' or 'Closed Lost'.
Negation of IN.
Returns the first non-NULL value in the list.
COALESCE:(dateStart, createdAt) will return value of createdAt if dateStart is not set.
If the first argument is not NULL, it returns it; otherwise it returns the second argument.
IFNULL:(name, '') will return empty string if name is NULL.
If the first argument equals the second, returns NULL, otherwise returns the first argument.
NULLIF:(name, '') will return NULL if name is empty string.
Date & time¶
Month number. From 1 to 12.
Week number. Sunday is a first day of week.
Week number. Monday is a first day of week.
Number from 1 to 7.
Number from 1 to 31.
Fiscal year number, where X is a number (between 1 and 12) of the fiscal year start. E.g.
Number from 0 to 23.
Number from 0 to 59.
Month number w/ year. E.g.
Quarter number w/ year. E.g.
Fiscal quarter number, where X is a number (between 1 and 12) of the fiscal year start. E.g.
Week number w/ year.
2018/4. Sunday is a first day of week.
Week number w/ year. Monday as a first day of week.
Current date and time.
Time zone converion.
TZ:(createdAt, -10.5) will apply -10:30 offset.
UNIX timestamp of a date-time argument or a current timestamp if no argument passed.
Difference between two days in years.
Difference between two days in months.
Difference between two days in weeks.
Difference between two days in days.
Difference between two days in hours.
Difference between two days in minutes.
Difference between two days in seconds. Available since v6.0.0.
Returns a date part of a date-time value. Available since v6.0.0.
CONCAT:(firstName, ' ', lastName).
Returns a specified number of characters from the left of the string. E.g.
LEFT:(someTextColumn, 1000). As of v6.1.0.
Convert to lower case.
Convert to upper case.
Remove leading and trailing spaces.
Number of characters in string.
Converts a value to a binary string.
EQUAL:('test', 'Test') returns true.
EQUAL:(BINARY:('test'), 'Test') returns false.
Avalilable since v5.7.0.
Replaces all the occurrences of a substring within a string.
REPLACE:('haystack', 'needle', 'replaceWith')
Available since v6.0.0.
ADD:(2, 2) corresponds to
2 + 2.
SUB:(2, 1) corresponds to
2 - 1.
MUL:(2, 2) corresponds to
2 * 2.
DIV:(2, 4) corresponds to
2 / 4.
Returns remainder of a number divided by another number. E.g.
MOD:(6, 4) corresponds to
6 % 4.
The largest integer value not greater than the argument.
The smallest integer value greater than or equal to the argument.
Round a number to a specified number of decimal places. E.g.
OR:(a, b, c) corresponds to SQL statement
a OR b OR c.
AND:(a, b, c) corresponds to SQL statement
a AND b AND c.
NOT:(a) corresponds to SQL statement
As of v7.0.8 it's possible to add custom functions. An implementation class name for a custom function should be defined in metadata by the path app > orm > functionConverterClassNameMap_Mysql. The class should implement
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.