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
  }
END

Parameters and arguments

  • row

    A name for the current row. Read its columns as row.column and a JSON column's fields as row.column.field

    Required: Yes
  • table

    The table to walk, as a name or in quotes, or JOIN(["a", "b"], ON(["a.x", "b.y"])), which walks the first table with the others joined to each row

    Required: Yes
  • FILTER(…) 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:

StepWhat 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 tableAdds 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 tableAdds the record, or changes it when it exists
REMOVE table FILTER(…)Deletes the records the FILTER names
INSERT EDGE, UPDATE EDGE, REMOVE EDGELink, change or unlink records in the graph. See Edges
SORT(…), TAKE(…), DISTINCTOrder, 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, DROPSchema steps. See TRUNCATE and DROP
a function callAny 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
ErrorWhat happenedHow to recover
HTQL-1003The loop is not written in HTQL's grammarCheck BEGIN, END and the brackets of each FILTER
HTQL-3003The table is not registered as readableCheck the table's name
HTQL-6001A step failed on a row. The message names the row and the step's own errorFix 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.