NEST

Read a record with its related records attached underneath it, in one statement.

Syntax

NEST is a clause of LOOKUP. Name the child table, how it links to the parent, and the fields you want from it.

LOOKUP(
  parent_table,
  NEST(
    child_table,
    ON(["key"]),
    SHOW("field", …),
    SORT("field"),
    TAKE(limit),
    NEST(
      grandchild_table,
      ON(["key"]),
      SHOW("field", …)
    ) AS name
  ) AS name
)

Parameters and arguments

  • child_table

    The table holding the children, written first inside NEST

    Required: Yes
  • ON([…])

    How a child links to its parent. A plain column name such as "id" means the child's id equals the parent's id, within the same tenant. A text with an equals sign, such as "entries.contentType = content_types.id", is used as written

    Required: Yes
  • SHOW(…)

    The child fields to include in each list item, which can be renamed with "column AS name"

    Required: Yes
  • SORT(…), TAKE(…)

    Order and limit the children of each parent

    Required: No
  • NEST(…)

    A NEST inside a NEST adds grandchildren, linked to the child in the same way

    Required: No
  • AS name

    The name of the list on each parent. Without it, the child table's name is used

    Required: No

Examples

One entry, with the asset that shares its identifier nested underneath.

Return type and behaviour

Each parent row comes back once, with a list under the NEST's name. The list holds one item for each child, shaped by the child's SHOW. A parent with no children gets an empty list.

The parent's own columns follow SHOW and FILTER as in any LOOKUP. TAKE inside NEST applies to each parent's children separately, so TAKE(2) keeps two children for every parent.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-1001NEST has no ON, so the child cannot be linked to the parentAdd ON(["key"])
HTQL-2001 NEST requires a SHOWNEST has no SHOWAdd SHOW("field", …)
HTQL-2001A field in SHOW does not exist on the child tableCheck the field's name
HTQL-1004 Unsupported function: NESTNEST holds a FILTERRemove it, and filter the parent instead
Placement and context

Inside LOOKUP, among its clauses. AS name comes after the closing bracket of NEST. A NEST can hold its own SORT, TAKE and NEST.

A LOOKUP can hold several NESTs, each with its own name, and a JOIN beside them. When a LOOKUP also holds a JOIN, write the link as a whole condition, ON(["entry_versions.id = entries.id"]), so no column name is ambiguous.

Limitations
  • Not supported: a FILTER inside NEST. Filter the parent in LOOKUP, or limit the children with TAKE.
  • Every NEST needs both an ON and a SHOW.
  • Without a SORT, the children of a parent come back in no promised order. Add a SORT when the order matters.
Permissions and security

NEST needs read access to the parent and the child table. When you link by column name, HTQLDB also requires that the child and parent share the same tenant.

Best practices
  • Use TAKE inside NEST when a parent can have many children.
  • Choose only the child fields you need in SHOW.
  • Name the list with AS so the result reads well.
  • Nest rather than join when you would otherwise group the repeated rows in your own code.