FOREACH
Do the same work for every record you choose, in one statement and one transaction.
Syntax
Name a variable for each row, the table, an optional FILTER, then the steps between BEGIN and END. FOR and FOREACH are the same word.
FOR row IN table
FILTER(row.column = value) BEGIN
LET name = LOOKUP(
other_table,
FILTER(key = row.key),
SHOW(column)
)
UPDATE {"column": value} IN table FILTER(id = row.id)
RETURN {
"id": row.id,
"related": name
}
ENDParameters and arguments
rowA name for the current row. Read its columns as
Required: Yesrow.columnand a JSON column's fields asrow.column.fieldtableThe table to walk, as a name or in quotes, or
Required: YesJOIN(["a", "b"], ON(["a.x", "b.y"])), which walks the first table with the others joined to each rowFILTER(…) or WHERE(…)Between the table and BEGIN: keeps only the rows to walk. Several conditions separated by commas must all hold. See FILTER
Required: No
The steps between BEGIN and END run in the order written, each on the rows the step before it left, as the steps after a GET do. The grammar accepts these steps:
| Step | What it does for each row |
|---|---|
LET name = … | Keeps a value for the later steps of the same row. The value can be a column, a computation, a function or a LOOKUP that reads the row's own fields, such as FILTER(key = row.key). With an aggregate such as MAX(row.version) it is worked out once, over the rows kept so far |
FILTER(…) | Keeps the row only when its conditions all hold; the steps after it do not run for a row it drops |
INSERT {…} INTO table | Adds a record built from the row's fields |
UPDATE {…} IN table FILTER(…) | Changes the records the FILTER names, which may read the row's fields |
UPSERT {…} INSERT {…} UPDATE {…} IN table | Adds the record, or changes it when it exists |
REMOVE table FILTER(…) | Deletes the records the FILTER names |
INSERT EDGE, UPDATE EDGE, REMOVE EDGE | Link, change or unlink records in the graph. See Edges |
SORT(…), TAKE(…), DISTINCT | Order, cut and de-duplicate the rows where written. Written first in the body they are part of the read; after other steps they order by a bound name, cut what is left, and may appear more than once |
TRUNCATE, DROP | Schema steps. See TRUNCATE and DROP |
| a function call | Any HTQL function, run for each row |
RETURN {…} | Adds one result to the answer for each row. A value may be a column, a field, a ?? default or a LOOKUP |
Examples
A result for each of three navigation links, built from a column and a field inside a JSON column.
Return type and behaviour
A loop with a RETURN answers with a list: one result for each RETURN reached, for each row. A loop that finds no rows answers an empty list.
A loop with no RETURN is a batch. It answers {"success": true, "processed": n, "failed": 0, "total": n, "results": [{"row": 0, "ok": true}, …]}.
Every step of every row runs in one transaction. If a step fails, the answer is the error, with the row it failed on, and nothing the earlier rows did is kept.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-1003 | The loop is not written in HTQL's grammar | Check BEGIN, END and the brackets of each FILTER |
HTQL-3003 | The table is not registered as readable | Check the table's name |
HTQL-6001 | A step failed on a row. The message names the row and the step's own error | Fix the step as its entry says |
Placement and context
FOR is a statement of its own. GET and FOR are the same loop over a table's rows: the same read, the same steps, the same order rules (see GET for every step and what the row stands for at each). The difference is the body: FOR's steps sit between BEGIN and END and may write. LOOKUP is the other read, a function call whose value is the rows.
A traversal also begins with FOR; HTQL tells them apart by what follows IN: digits and a direction make a traversal, a table makes a loop. Write nothing after END.
Limitations
- Writes inside a loop follow the rules of the same write outside one: a platform table changes only through the statement that owns it.
Permissions and security
FOREACH needs read access to the table it walks, and each step needs the access its own statement needs.
Best practices
- Use FILTER before BEGIN to walk only the rows you need.
- Reach for one UPDATE or REMOVE with a FILTER when a single statement can do the job. Use FOREACH when each row needs its own lookup, its own values or more than one step.
- Test a loop with a RETURN before you add writes.
- Put a LET only where a value is read more than once.