Lowest Common Ancestor

The lowest common ancestor (LCA) is the deepest path that is an ancestor of every input. For example, the LCA of Top.Science.Astronomy and Top.Science.Physics is Top.Science.

import { db } from "../prisma/db";
import { param } from "@prisma-next/sql-query/param";

const page = db.schema.tables.page;

lcaAll()

lcaAll() computes the LCA across all paths held in an ltree[] column. It takes no arguments — the array column is the input.

const plan = db.sql
  .from(page)
  .select({ id: page.columns.id, ancestor: page.columns.breadcrumbs.lcaAll() })
  .build();

await db.runtime().execute(plan);

SQL equivalent: lca(breadcrumbs)

An empty array yields null.

Use ltreeArray() (TypeScript) or ltree.LtreeArray() (PSL) for the ltree[] column — see Authoring Contracts.

lca()

For separate ltree paths rather than an array column, use lca() on a path column. It takes one or more additional paths (2–8 total, per PostgreSQL).

const plan = db.sql
  .from(page)
  .select({ id: page.columns.id, ancestor: page.columns.path.lca(param("other")) })
  .build({ params: { other: "Top.Science.Physics" } });

await db.runtime().execute(plan);

SQL equivalent: lca(path, $1::ltree)