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.
tableThe table to read, such as
Required: Yesentries. It can be a plain name or a quoted nameFILTER(…)Keeps the rows that match. A value in a condition can be a literal, a variable or a column of an outer row.
Required: NoWHERE(…)means the same. See FILTERJOIN([…], ON([…]))Adds the columns of another table, matched on columns you name. See JOIN
Required: NoNEST(…)Adds a list of related child rows to each row. A NEST can hold another NEST. See NEST
Required: NoSHOW(…)Chooses the columns, and can rename them or compute new ones with a function. See SHOW
Required: NoDISTINCT()Removes repeated rows.
Required: NoDISTINCT(column, …)keeps the first row for each value of the columns namedSORT(…)Orders the rows by one or more quoted columns, each with an optional direction. See SORT
Required: NoTAKE(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
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 Unsupported syntax | A clause is not written in HTQL's grammar, for example arithmetic as a SHOW item | Check the clause against its entry |
HTQL-2001 | A column in SHOW, SORT or FILTER does not exist, or a SORT direction is not ASC or DESC | Check the column's name and the direction |
HTQL-2001 INDEX: first argument must be an array | INDEX was given a LOOKUP kept in a variable | Use SORT and TAKE(1) in the LOOKUP |
HTQL-3003 | The table is not registered as readable, or is an alias | Name a table that exists |
HTQL-5001 | A clause has the wrong number of arguments, such as SHOW() with none | Give 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 inABS(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.
TAKEtakes 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
SHOWto choose the columns you need, so each row stays small. - Add
TAKEwhen 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.