LOOKUP

Read exactly the columns you want, from one table or several, in a single statement.

Syntax

Name the table, then list the clauses you need. Every clause after the table is optional.

LOOKUP(
  table,
  FILTER(condition, …),
  JOIN(
    ["other_table"],
    ON(["other_table.column", "table.column"])
  ),
  NEST(
    child_table,
    ON(["key"]),
    SHOW("field", …),
    SORT(…),
    TAKE(…)
  ) AS name,
  SHOW("field", "field AS name", FUNCTION(…) AS name),
  DISTINCT(),
  SORT("field [ASC|DESC]", …),
  TAKE(limit [, offset])
)

Parameters and arguments

Separate the clauses with commas. Each has its own entry in this guide.

  • table

    The table to read, such as entries. It can be a plain name or a quoted name

    Required: Yes
  • FILTER(…)

    Keeps the rows that match. A value in a condition can be a literal, a variable or a column of an outer row. WHERE(…) means the same. See FILTER

    Required: No
  • JOIN([…], ON([…]))

    Adds the columns of another table, matched on columns you name. See JOIN

    Required: No
  • NEST(…)

    Adds a list of related child rows to each row. A NEST can hold another NEST. See NEST

    Required: No
  • SHOW(…)

    Chooses the columns, and can rename them or compute new ones with a function. See SHOW

    Required: No
  • DISTINCT()

    Removes repeated rows. DISTINCT(column, …) keeps the first row for each value of the columns named

    Required: No
  • SORT(…)

    Orders the rows by one or more quoted columns, each with an optional direction. See SORT

    Required: No
  • TAKE(limit, offset)

    Limits the rows and skips some. See TAKE

    Required: No

Write each column in SHOW, SORT and DISTINCT as a quoted text, such as "id" or "id DESC".

Examples

Two columns of the first four navigation links.

Return type and behaviour

LOOKUP returns a list of rows. A table with no matching rows returns an empty list, not an error. Without TAKE, LOOKUP returns every matching row.

Without SHOW, each row holds every column. Fields come back named in camelCase, the same names a statement writes, such as contentType.

A column that holds an object or a list, such as the fields column of entries, comes back whole, as an object or a list. A NEST arrives as a list inside each row, shaped by its SHOW.

Reading one row of a kept LOOKUP by position uses INDEX(rows, 1), counting from 1. To find the first row by an order, write the SORT and TAKE(1) in the LOOKUP itself.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-1003 Unsupported syntaxA clause is not written in HTQL's grammar, for example arithmetic as a SHOW itemCheck the clause against its entry
HTQL-2001A column in SHOW, SORT or FILTER does not exist, or a SORT direction is not ASC or DESCCheck the column's name and the direction
HTQL-2001 INDEX: first argument must be an arrayINDEX was given a LOOKUP kept in a variableUse SORT and TAKE(1) in the LOOKUP
HTQL-3003The table is not registered as readable, or is an aliasName a table that exists
HTQL-5001A clause has the wrong number of arguments, such as SHOW() with noneGive the clause the arguments its entry lists
Placement and context

LOOKUP can be a whole statement. It can also be the value of a variable (LET rows = LOOKUP(…)), which later statements use in three ways: a later LOOKUP's FILTER can name the variable, RETURN can shape or count it with LENGTH, and PLUCK can lift one column out of it.

Inside a GET, a LOOKUP kept in a variable can name the GET's current row in its FILTER, such as FILTER(contentType = t.id). HTQLDB runs that LOOKUP for each row of the GET, inside the one statement, so your code sends one request rather than one for every row. The same holds inside a FOREACH.

Add ?? value after a LOOKUP kept in a variable to give a default when it finds no rows.

LOOKUP is a function call: FILTER, SHOW, SORT, TAKE and the rest are its arguments, so they can be written in any order and each is given once (a repeated one, such as a second TAKE, replaces the first). This is the difference from GET and FOR, which loop over the rows and run every step where it is written; a second TAKE there cuts again.

Limitations
  • Not supported: grouping or counting inside LOOKUP. LOOKUP has no GROUP, and its SHOW lists columns and function calls, not aggregates. Use LEARN with GROUP for that.
  • Not supported: a FILTER inside NEST. Filter the parent in LOOKUP, or limit the children with TAKE.
  • Not supported: arithmetic written directly as a SHOW item, such as SHOW(version * 10 AS x). SHOW takes columns and function calls, so write the arithmetic inside a function, as in ABS(version * 10) AS x.
  • Not supported: a table alias, as in LOOKUP("entries" AS e, …). Name the table itself in SHOW, FILTER and JOIN.
  • Not supported: a variable as the number in TAKE. TAKE takes digits: TAKE(25).
  • A second FILTER replaces the first. Put every condition in one FILTER.
  • A condition on text inside a JSON column, such as fields CONTAINS "Apple", compares the column as a whole and is refused. Read the JSON column and test its parts in a GET's RETURN.
Permissions and security

LOOKUP needs read access to each table it names. A table that is not registered as readable is refused with HTQL-3003.

A column that is classified above the caller's clearance is not refused: it comes back masked. Text columns show ***** and other columns show null.

You do not write a tenant condition.

Best practices
  • Use SHOW to choose the columns you need, so each row stays small.
  • Add TAKE when the table can grow. Sort first so the pages stay in the same order.
  • Use NEST when you want children under each parent, and JOIN when you want their columns beside the parent's.
  • Where you would loop in code and read once for each row, read the parent with GET or FOR and bind each row's child rows with a LET whose LOOKUP is filtered by the row's key; HTQLDB does the looping.