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 valueParameters and arguments
tableThe table to read, as a plain name such as
Required: YesentriesaliasThe name each row goes by, such as
Required: Needed by every step after the reade. Every later step reads the row through it:e.version,e.fields.linkFILTER(…)The conditions a row must meet, run in the database.
Required: NoWHERE(…)is the same. See Comparison and Logical operatorsDISTINCTOne 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: NoSORT("column DESC", …)The order, by the table's columns, each a quoted column name with
Required: NoASC(the default) orDESCTAKE(limit, offset)At most
Required: Nolimitrows, after skippingoffset. Whole numbersIF(condition)Keeps the rows the condition holds for, tested on each row the read returned
Required: NoLET name = valueA value computed for each row, or once over all the rows (see Stages). A
Required: NoLETwhose value is a LOOKUP filtered by the row's key (FILTER(orderId = o.orderId)) binds that row's child rowsRETURN valueEach 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
| RETURN | The answer |
|---|---|
RETURN {"name": value, …} | A list with one object for each row, in the order of SORT |
RETURN alias.column | A plain list of that column's values |
RETURN alias, or no RETURN | Each row whole, its fields named in camelCase (contentType) |
RETURN with an aggregate | One 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
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 Unsupported syntax | The statement is not in HTQL's grammar, such as SORT(id DESC) without quotes | Quote each SORT term: SORT("id DESC") |
HTQL-1004 Unsupported function: GET | The table name is quoted, as in GET("entries") | Write the table name without quotes |
HTQL-1016 Invalid GET clause | The names inside GET are not a table and an alias | Write GET(table, alias) |
HTQL-2001 | A column named in FILTER, SORT or a step does not exist on the table | Check the column against the table |
HTQL-3003 | The table is not registered as readable | Check the table's name |
A function's own error, such as HTQL-5005 | A step failed on a row; the whole statement answers the error, and no row is left out silently | Give 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:
| Line | Runs |
|---|---|
LET kind | Once, before the read |
FILTER, SORT("id"), TAKE(200) | In the database, as one query |
LET label | For 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 |
RETURN | Each 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 asSORT(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)andSTATS(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.