Hierarchy Operators
Ancestor and descendant checks for tree paths
Hierarchy operators check whether a row's ltree path is an ancestor or descendant of another path. They are methods on ltree column references in the SQL query builder.
import { db } from "../prisma/db";
import { param } from "@prisma-next/sql-query/param";
const category = db.schema.tables.category;PostgreSQL compares the row's path as the left operand:
| SQL | Meaning |
|---|---|
A @> B | A is an ancestor of B (or equal) |
A <@ B | A is a descendant of B (or equal) |
Both comparisons are inclusive of the argument path.
isAncestorOf()
Returns rows whose path is an ancestor of the argument.
const plan = db.sql
.from(category)
.select({ id: category.columns.id, path: category.columns.path })
.where(category.columns.path.isAncestorOf(param("target")))
.build({ params: { target: "electronics.computers" } });
const rows = await db.runtime().execute(plan);Given electronics.computers as the argument:
electronics: matches (ancestor)electronics.computers: matches (@>is inclusive of equality)electronics.computers.laptops: does NOT match (descendant, not an ancestor)
SQL equivalent: path @> $1::ltree
isDescendantOf()
Returns rows whose path is a descendant of the argument. This is the usual choice for "everything under this prefix."
const plan = db.sql
.from(category)
.select({ id: category.columns.id, path: category.columns.path })
.where(category.columns.path.isDescendantOf(param("prefix")))
.build({ params: { prefix: "electronics" } });
const rows = await db.runtime().execute(plan);Given electronics as the argument:
electronics: matches (equal;<@is inclusive)electronics.computers: matches (child)electronics.computers.laptops: matches (grandchild)
SQL equivalent: path <@ $1::ltree
Mnemonic: isDescendantOf(prefix) → "my path sits under this prefix."
Examples
All subcategories under electronics:
const subcategories = db.sql
.from(category)
.select({ id: category.columns.id, path: category.columns.path })
.where(category.columns.path.isDescendantOf(param("prefix")))
.build({ params: { prefix: "electronics" } });
await db.runtime().execute(subcategories);All ancestors of a deep path:
const ancestors = db.sql
.from(category)
.select({ id: category.columns.id, path: category.columns.path })
.where(category.columns.path.isAncestorOf(param("target")))
.build({ params: { target: "electronics.computers.laptops" } });
await db.runtime().execute(ancestors);ORM lane (optional)
When your app uses @prisma-next/sql-orm-client, the same methods appear on ltree-typed fields:
const rows = await db.orm.Category.where((c) => c.path.isDescendantOf("electronics"))
.select("id", "path")
.all();