prisma-ltree

Pattern matching operators filter ltree paths using PostgreSQL lquery and ltxtquery syntax. Pattern arguments are strings (or string[] for array matching) passed through param(). They are not separate column types.

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

const category = db.schema.tables.category;

matchesLquery()

Match a path against a single lquery pattern.

const plan = db.sql
  .from(category)
  .select({ id: category.columns.id, path: category.columns.path })
  .where(category.columns.path.matchesLquery(param("pattern")))
  .build({ params: { pattern: "*.computers.*" } });

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

Common lquery syntax:

*: matches zero or more labels (default quantifier {,})
*{1}: exactly one label; *{n} / *{n,m} / *{n,} / *{,m}: other quantifiers on wildcard labels
{a,b,c}: matches any one of the labels at that position
|: alternation within a label

SQL equivalent: path ~ $1::lquery

Examples

Paths exactly two labels deep:

db.sql
  .from(category)
  .where(category.columns.path.matchesLquery(param("pat")))
  .build({ params: { pat: "*{1}.*{1}" } });

Paths under electronics at any depth:

db.sql
  .from(category)
  .where(category.columns.path.matchesLquery(param("pat")))
  .build({ params: { pat: "electronics.*" } });

Direct children of electronics only:

db.sql
  .from(category)
  .where(category.columns.path.matchesLquery(param("pat")))
  .build({ params: { pat: "electronics.*{1}" } });

matchesLqueryArray()

Match if the path satisfies any pattern in a string[].

const plan = db.sql
  .from(category)
  .select({ id: category.columns.id, path: category.columns.path })
  .where(category.columns.path.matchesLqueryArray(param("patterns")))
  .build({
    params: { patterns: ["electronics.*", "software.*"] },
  });

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

SQL equivalent: path ? $1::lquery[]

matchesLtxtquery()

Match using ltxtquery full-text-style patterns over path labels (words combined with &, |, !).

const plan = db.sql
  .from(category)
  .select({ id: category.columns.id, path: category.columns.path })
  .where(category.columns.path.matchesLtxtquery(param("query")))
  .build({ params: { query: "computer | phone" } });

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

SQL equivalent: path @ $1::ltxtquery

Pattern syntax reference

Syntax	Meaning	Example
`*`	Zero or more labels	`a.*.c` matches `a.c`, `a.b.c`, …
`{1}` / `{n,m}`	Bounded wildcards	`Top.Science.*{1}`: one label under `Top.Science`
`{a,b}`	Label alternatives	`{a,b}.c` matches `a.c` or `b.c`
`\|` (ltxtquery)	Boolean OR	`computer \| phone`
`&` (ltxtquery)	Boolean AND	`computer & laptop`

Refer to PostgreSQL ltree documentation for full pattern syntax.

ORM lane (optional)

await db.orm.Category.where((c) => c.path.matchesLquery("electronics.*"))
  .select("id", "path")
  .all();

Pattern Matching Operators