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 [...]]
  • [ ] indicates that the encapsulated contents are optional.

  • …​ means that the current encapsulated content is repeated.

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

  • quantity of type Number

  • price of type Number

  • name of type String

  • visible of type Checkbox

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%'

Comparison operators

Equal
  • =

Not equal
  • !=

  • <>

Less than
  • <

Less than or equal to
  • <=

Greater than
  • >

Greater than or equal to
  • >=

Matches pattern
  • LIKE

Does not match the pattern
  • NOT LIKE

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

Arithmetic Operators

Addition
  • +

Subtraction
  • -

Multiplication
  • *

Division
  • /

Boolean Operators

  • AND: true when left and right are true

  • OR: true when left or right is true

Multi-value Fields

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.

Sorting

The general template for constructing a sort looks like this:
FIELD[ DIRECTION][, FIELD[ DIRECTION] [...]]
  • [ ] indicates that the encapsulated contents are optional.

  • …​ means that the current encapsulated content is repeated.

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:

  • quantity of type Number

  • price of type Number

  • name of type String

  • visible of type Checkbox

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

Multi-value Fields

Sorting over multi-value fields is currently not supported.

Utilities

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.

DevNote add a format reference.

DATE

Returns date from the given timestamp.