You are reading the documentation for an outdated Corteza release. 2023.9 is the latest stable Corteza release.

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 then
  • <

Less or equal then
  • <=

Greater then
  • >

Greater or equal then
  • >=

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: truthy when left and right are truthy

  • OR: truthy when left or right is truthy

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 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:

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