Query Language

Corteza implements an SQL-like syntax for querying and sorting records.

Querying

The general template for constructing a query looks like this:

FIELD OPERATOR VALUE [AND|OR FIELD OPERATOR VALUE [...]]

For the following examples, let us assume that we have a module named test with the following fields:

To query for records with zero quantity or zero price:

quantity = 0 or price = zero

To query all visible records starting with the letter "a":

visible AND name LIKE 'a%'

Equal

Not equal

Less than

Less than or equal to

Greater than

Greater than or equal to

Matches pattern

Does not match the pattern

When matching patterns, use _ to represent a single character; use % to represent zero, one, or multiple characters.

Addition

Subtraction

Multiplication

Division

Filters for multi-value fields look precisely the same as they would for regular fields.

To exemplify; let us consider a module with the following fields:

Field name: users, field type: user selector, multi value option checked.
Field name: selects, field type: Select / dropdown, multi value option checked.

Table 1. The following table provides a series of examples with the corresponding query and the expected output:
Filter for current user	`users = ${userID}`	The filter returns the records where either one of the multi-value values matches the current user.
Filter select for pattern	`selects LIKE 'a%'`	The filter returns the records where either one of the multi-value select options matches the `a%` pattern.
Filter select for value	`selects = 'a2'`	The filter returns the records where either one of the multi-value select options equals `a2`.
Filter for concrete users	`'A' IN users`	The filter returns record/s where either one of the multi-value select options contains `A`.
Filter out concrete users	`'A' NOT IN users`	The filter returns record/s where either one of the multi-value select options doesn’t contain `A`.

The general template for constructing a sort looks like this:

FIELD[ DIRECTION][, FIELD[ DIRECTION] [...]]

Supported Directions:

ASC: Sort in ascending order, lower values first (NULL values come first). This is the default sort if no DIRECTION is specified.
DESC: Sort in ascending order, lower values last (NULL values come last).

For the following examples, let us assume that we have a module named test with the following fields:

To order records, descending by price and ascending by quantity:

price DESC, quantity

Sorting over multi-value fields is currently not supported.

Table 2. Supported date functions:
`QUARTER`	Returns quarter from the given timestamp.
`YEAR`	Returns year from the given timestamp.
`DATE_FORMAT`	The function returns the date formatted by the provided format. Reference to date and time formatting
`DATE`	Returns date from the given timestamp.