EntityQuery Language (EQL)

Apart from building an EntityQuery in code, one can be specified using the EntityQuery Language (EQL). EQL provides an SQL-like syntax for building queries. Some examples could be:

  • name = 'john'

  • order by birthday asc

  • name = 'john' order by birthday desc, registrationDate asc

  • name = 'john' and birthday >= '1980-01-01'

  • (name in ('john', 'jane') and birthday = today()) or birthday is EMPTY order by name asc

  • children contains 'john' or children is EMPTY

A single clause of a query consists of a field, followed by an operator, followed by one or more values or functions. Multiple values for one field are grouped together inside parentheses. Clauses can be combined using either and or or. Operator precedence can be enforced by using parentheses around a clause.

A field of a query clause usually corresponds with a single property you want to query.

A SQL-like order by clause specifying one or more properties is also supported. An EQL statement with only an ordering clause will select all items in that order.

Special characters in string literals should be escaped using the \\ (backslash) character. In case of like conditions, they should be double-escaped.

Supported operators

Equality operators

Usable on most property types.

Operator Description

=

equals

!=

not equals

IN/NOT IN

Usable on most property types. These are the only operators that take a group of values as argument.

Operator Description

IN

equal to any of the argument values

NOT IN

not equal to any of the argument values

Comparing operators

Usable on numeric and date property types.

> greater than

>=

greater than or equal to

<

less than

⇐

less than or equal to

LIKE operators

Usable on string property types. The argument specifies the pattern that property value should match. The pattern can contain simple wildcard specified using %.

Operator Description

LIKE

should match the pattern specified as argument

NOT LIKE

should not match the pattern specified as argument

ILIKE

case insensitive match of the pattern specified as argument

NOT ILIKE

should not match the pattern specified as argument (case insensitive)

NOTE Special characters in LIKE statements should be double-escaped using the \ (backslash) character. This includes the literal % (percentage) character.

Case insensitive property matching When using ILIKE conditions, a case insensitive lookup will be performed in the datastore. If you want to force a property to always have a case insensitive lookup, you can do so by configuring a EntityQueryConditionTranslator attribute on that property.

configuration
 .withType( Group.class )
 .properties(
    props -> props.property( "name" ).attribute( EntityQueryConditionTranslator.class, EntityQueryConditionTranslator.ignoreCase() )
 )

This will convert any equality or like operator to the equivalent case insensitive lookup.

WARNING Whenever possible, it is probably best to use collation settings of your datastore to ensure case insensitive querying of properties. Configuring it on a datastore level will almost certainly give you much better performance. If collation is not possible, investigate the option of using function indices on the relevant columns.

CONTAINS/NOT CONTAINS

Usable on collection or text property types. In case of text the contains statement is translated to a like statement with wildcards before and after.

Operator Description

CONTAINS

argument should be present in the collection or string should be present in the text

NOT CONTAINS

argument should not be present in the collection or string should not be present in the text

IS NULL/IS NOT NULL

Usable on single value properties only. These operators to not take any additional arguments.

Operator Description

IS NULL

property should not be set (null)

IS NOT NULL

property should be set (not null)

IS EMPTY/IS NOT EMPTY

Preferred for collection type properties, altough usually will work as an alternative for IS NULL/IS NOT NULL on single value properties. These operators to not take any additional arguments.

Operator Description

IS EMPTY

property should not have any members (in case of collection) or should not be set (if single value property)

IS NOT EMPTY

property should have at least one member (in case of collection) or should be set (if single value property)

Default EQL functions

Security related functions

Function Description

currentUser()

returns the name of the current authenticated principal

Date and time functions

Function Description

now()

returns current timestamp

today()

returns date of today

EntityQueryParser

The EntityQueryParser is responsible for converting an EQL statement into a valid EntityQuery. Any entity configuration with an EntityQueryExecutor registered will have an EntityQueryParser created automatically.

The parser will validate the EQL statement and convert it to a strongly typed EntityQuery. The default EntityQueryParser uses the entity related EntityPropertyRegistry to validate the query clauses.

« EntityQuery infrastructure EntityQuery Language (EQL) Extending EQL »