Skip to Content
prisma-ltree
Query Operations

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-typed fields in the ORM lane (and on column references in the SQL query builder).

import { db } from "../prisma/db";

PostgreSQL compares the row's path as the left operand:

SQLMeaning
A @> BA is an ancestor of B (or equal)
A <@ BA 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 rows = await db.orm.Category.where((c) => c.path.isAncestorOf("electronics.computers"))
  .select("id", "path")
  .all();

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 rows = await db.orm.Category.where((c) => c.path.isDescendantOf("electronics"))
  .select("id", "path")
  .all();

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 = await db.orm.Category.where((c) => c.path.isDescendantOf("electronics"))
  .select("id", "path")
  .all();

All ancestors of a deep path:

const ancestors = await db.orm.Category.where((c) =>
  c.path.isAncestorOf("electronics.computers.laptops"),
)
  .select("id", "path")
  .all();

ORM using helpers

Each scalar ltree field also gets two collection methods under using, named after the field. They filter the same way as the operators above and chain with where / orderBy / limit / include:

const underElectronics = await db.orm.Category.using
  .path_descendants("electronics")
  .select("id", "path")
  .all();

const aboveLaptops = await db.orm.Category.using
  .path_ancestors("electronics.computers.laptops")
  .select("id", "path")
  .all();
MethodSame as
using.path_descendants(root).where((c) => c.path.isDescendantOf(root))
using.path_ancestors(leaf).where((c) => c.path.isAncestorOf(leaf))

There is no separate path_subtree helper: under ltree, the subtree of a root is exactly path_descendants(root). List (ltree[]) fields do not get these methods.

GiST required for index use: add @@index([path], type: "gist") so PostgreSQL can avoid a sequential scan. Without GiST, these filters still run and read every row. See Add a GiST index.

For sorting paths or equality filters (not hierarchy), use framework comparison via codec traits: Sort and compare paths.

On this page