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 a 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'. Arguments on even-numbered positions define 'WHEN' conditions, while following arguments define corresponding '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'. Arguments on odd-numbered positions define keys, while following 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 when 'name' is equal to 'Cloud Basic' or '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 return true if 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 conversion.
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).
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.
REPLACE¶
Replaces all the occurrences of a substring within a string.
REPLACE:('haystack', 'needle', 'replaceWith')
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 system field names. Fields are listed under Administration > Entity Manager > {Entity Type} > 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.