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 then

Less or equal then

Greater then

Greater or equal then

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`.

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 are first). This is the default sort if no DIRECTION is specified.
DESC: sort in ascending order, lower values last (NULL values are last).

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