You are reading the documentation for an outdated Corteza release. 2021.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

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

Utilities

Table 1. 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.