GET

Read a table once and get back exactly the answer your application needs.

Syntax

Name the table and an alias, then add the steps you need. Only the table is required.

LET name = value
GET(table, alias)
  FILTER(condition, …)
  DISTINCT [column, …]
  SORT("column [ASC|DESC]", …)
  TAKE(limit [, offset])
  IF(condition)
  LET name = value
  RETURN value

Parameters and arguments

  • table

    The table to read, as a plain name such as entries

    Required: Yes
  • alias

    The name each row goes by, such as e. Every later step reads the row through it: e.version, e.fields.link

    Required: Needed by every step after the read
  • FILTER(…)

    The conditions a row must meet, run in the database. WHERE(…) is the same. See Comparison and Logical operators

    Required: No
  • DISTINCT

    One row for each value of the columns named after it, or for each whole row when none is named. No brackets in a GET

    Required: No
  • SORT("column DESC", …)

    The order, by the table's columns, each a quoted column name with ASC (the default) or DESC

    Required: No
  • TAKE(limit, offset)

    At most limit rows, after skipping offset. Whole numbers

    Required: No
  • IF(condition)

    Keeps the rows the condition holds for, tested on each row the read returned

    Required: No
  • LET name = value

    A value computed for each row, or once over all the rows (see Stages). A LET whose value is a LOOKUP filtered by the row's key (FILTER(orderId = o.orderId)) binds that row's child rows

    Required: No
  • RETURN value

    Each row's answer, or one answer over all the rows. Without RETURN, each row comes back whole

    Required: No

Examples

A variable, the read, then steps over the rows in the order written.

Return type and behaviour

RETURNThe answer
RETURN {"name": value, …}A list with one object for each row, in the order of SORT
RETURN alias.columnA plain list of that column's values
RETURN alias, or no RETURNEach row whole, its fields named in camelCase (contentType)
RETURN with an aggregateOne answer over all the rows: a value, or an object of values

A read that matches no rows answers an empty list. A column that is empty is null unless ?? gives it a default.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-1003 Unsupported syntaxThe statement is not in HTQL's grammar, such as SORT(id DESC) without quotesQuote each SORT term: SORT("id DESC")
HTQL-1004 Unsupported function: GETThe table name is quoted, as in GET("entries")Write the table name without quotes
HTQL-1016 Invalid GET clauseThe names inside GET are not a table and an aliasWrite GET(table, alias)
HTQL-2001A column named in FILTER, SORT or a step does not exist on the tableCheck the column against the table
HTQL-3003The table is not registered as readableCheck the table's name
A function's own error, such as HTQL-5005A step failed on a row; the whole statement answers the error, and no row is left out silentlyGive the function the type it takes, or guard the value with ??
Placement and context

Each step runs where you write it. The first example beside this section, line by line:

LineRuns
LET kindOnce, before the read
FILTER, SORT("id"), TAKE(200)In the database, as one query
LET labelFor each row
SORT("label DESC")Orders the rows by label
IF(…)Keeps the rows it holds for
LET newest = MAX(e.version)Once, over the rows kept: e.version is every row's version
TAKE(5)Keeps five
RETURNEach row's answer

Warning

Order changes the answer. TAKE(5) then IF(…) keeps the matches among five rows; IF(…) then TAKE(5) keeps five matches.

Tip

Write FILTER, SORT and TAKE first: the database runs them, and only those rows leave it.

Aggregates (SUM, COUNT, AVERAGE, MIN, MAX, MEDIAN, MODE, STDEV, VAR, FIRST, LAST) run once over the rows kept; every other function runs per row. A LET reads only the names above it; a name read before its LET is null. GET and FOR run the same steps; LOOKUP is a function call, so its arguments can come in any order.

Limitations
  • One table for each GET. Not supported: several table and alias pairs in one GET. To read related tables, use a LOOKUP for each row, or LOOKUP with JOIN or NEST.
  • SORT orders by the table's columns, named in quotes: SORT("version DESC", "id"). Not supported: sorting by a value or an alias path such as SORT(e.id).
  • An aggregate reads the table's columns. A value a LET computed for each row is not part of an aggregate.
  • MIN and MAX of the alias are aggregates over the rows. For the larger of two columns in one row, write IF(e.a > e.b, e.a, e.b).
  • DESCRIBE(table) and STATS(table) take the same form as GET.
Permissions and security

GET needs read access to the table; a table that is not registered as readable is refused with HTQL-3003. A function a step calls is authorised for the caller like any other.

Best practices
  • Narrow in FILTER first, so the database returns only the rows worth working on; use IF for what only a row can compute.
  • Add TAKE to every read of a table that grows, and SORT before paging so the pages stay in order.
  • Name a value with LET when two steps read it, such as a label returned twice or a total every row is compared with. A value read once belongs where it is used.
  • Return only the fields the caller needs, under the names its code expects.