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_tableThe table holding the children, written first inside NEST
Required: YesON([…])How a child links to its parent. A plain column name such as
Required: Yes"id"means the child'sidequals the parent'sid, within the same tenant. A text with an equals sign, such as"entries.contentType = content_types.id", is used as writtenSHOW(…)The child fields to include in each list item, which can be renamed with
Required: Yes"column AS name"SORT(…), TAKE(…)Order and limit the children of each parent
Required: NoNEST(…)A NEST inside a NEST adds grandchildren, linked to the child in the same way
Required: NoAS nameThe 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
| Error | What happened | How to recover |
|---|---|---|
HTQL-1001 | NEST has no ON, so the child cannot be linked to the parent | Add ON(["key"]) |
HTQL-2001 NEST requires a SHOW | NEST has no SHOW | Add SHOW("field", …) |
HTQL-2001 | A field in SHOW does not exist on the child table | Check the field's name |
HTQL-1004 Unsupported function: NEST | NEST holds a FILTER | Remove 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
TAKEinside NEST when a parent can have many children. - Choose only the child fields you need in SHOW.
- Name the list with
ASso the result reads well. - Nest rather than join when you would otherwise group the repeated rows in your own code.