Search and trees

search ranks rows, a tree walks a self-relation, and fragment nests SQL.

Text search

find({ search }) and count({ search }) match a search column. A string is enough when the table has one. Rows come back closest first, then by primary key, unless you pass orderBy. count uses the same match and does not order.

`match`	Query
`natural`	`websearch_to_tsquery`. The default. Unquoted words are combined with AND.
`all-words`	`plainto_tsquery`.
`phrase`	`phraseto_tsquery`.
`advanced`	`to_tsquery`. Raw syntax. A syntax error in that query is OKM1120. Other syntax errors stay a driver error.

language is a text search language such as english. Leave it out for the database default. A well-formed name that Postgres does not know as a language reaches the server and comes back as a driver error. When the table has more than one search column, name it with in. The error lists the names. { mode, config } is not accepted. Ranking is ts_rank. matches and relevance take the same { match, language }. The clauses are Where operators.

simple

A string is enough when the table has one search column. Rows come back closest first.

simple-search.ts

import { connect } from "okmodel/pg/postgresjs";
import { schema, table, t } from "okmodel/pg";

const pages = table("pages", {
  id: t.identity(),
  title: t.text(),
  body: t.tsvector(),
});
const app = schema({ tables: [pages] });

export async function found(url: string) {
  await using db = connect(url, { schema: app });
  return await db.pages.find({ search: "deploy rollback", limit: 20 }); 
}

complex

match and language name the query. where and orderBy use the same text, so the filter and the rank agree.

search.ts

import { connect } from "okmodel/pg/postgresjs";
import { matches, relevance, schema, table, t } from "okmodel/pg";

const pages = table("pages", {
  id: t.identity(),
  title: t.text(),
  body: t.tsvector(),
});
const app = schema({ tables: [pages] });

export async function ranked(url: string) {
  await using db = connect(url, { schema: app });
  return await db.pages.find({
    search: { for: "deploy rollback", match: "phrase", language: "english" }, 
    where: { body: matches("deploy rollback", { match: "phrase", language: "english" }) },
    orderBy: {
      body: relevance("deploy rollback", { match: "phrase", language: "english" }),
      id: "asc",
    },
    limit: 20,
  });
}

OKM1120

page(), one, and exists do not take search. A language that is not text is refused. find({ search }) and count({ search }) match a column built for search.

OKM1122

A missing text search configuration is OKM1122, including public.english. A malformed advanced search is OKM1120 and names the search input. An empty or malformed name is still OKM1122 when the statement is built.

Tree

descendants(id) reads the rows below id. ancestors(id) reads the rows above it. Declare a relation from the table to itself. ltree is not required.

Option	Result
`limit`	How many rows come back, after the walk's order. Required unless `connect({ limits: { find } })` is set or the caller uses `.all(reason)`. A `limit` above `limits.max` is OKM1101.
`relation`	Required when the self-relations use different columns. The fix lists the names.
`depth`	Stops after that many steps. `0` returns nothing unless `self: true`, which returns the start row. It does not bound rows.
`where`	Filters the rows that come back. It does not cut the walk short.
a missing id	No rows.
a cycle	A row already on the path is not entered again.
`include`	Not available.

descendants

descendants(id) reads the rows below id. depth stops the walk. limit is how many of those rows come back. No limit and no .all(reason) uses limits.find, or fails with OKM1101 (descendants() needs a limit.).

descendants.ts

import { connect } from "okmodel/pg/postgresjs";
import { many, schema, table, t } from "okmodel/pg";

const nodes = table(
  "nodes",
  {
    id: t.identity(),
    parentId: t.bigint().nullable().references("nodes"),
    name: t.text(),
  },
  { relations: { children: many("nodes", "parentId") } },
);
const app = schema({ tables: [nodes] });

export async function below(url: string, id: string) {
  await using db = connect(url, { schema: app });
  return await db.nodes.descendants(id, { depth: 2, select: ["id", "name"], limit: 20 }); 
}

ancestors

ancestors(id) reads the rows above id. limit follows the same rule (ancestors() needs a limit.).

ancestors.ts

import { connect } from "okmodel/pg/postgresjs";
import { many, schema, table, t } from "okmodel/pg";

const nodes = table(
  "nodes",
  {
    id: t.identity(),
    parentId: t.bigint().nullable().references("nodes"),
    name: t.text(),
  },
  { relations: { children: many("nodes", "parentId") } },
);
const app = schema({ tables: [nodes] });

export async function above(url: string, id: string) {
  await using db = connect(url, { schema: app });
  return await db.nodes.ancestors(id, { select: ["id", "name"], limit: 20 }); 
}

OKM1120

No self-relation is nodes has no relation to itself. More than one, and no relation, is nodes has more than one relation to itself. Pass relation. A primary key that is not exactly one column besides the tenant key is nodes needs one primary key column besides the tenant key. depth must be an integer from 0 up.

SQL fragment

fragment is a SQL expression you can nest. A string, number, boolean, or null is a parameter. ident names the field, the same name where uses, and a hidden field is refused (OKM1120); hidden({ filterable: true }) is allowed in where only. fragment.column is the field the fragment is attached to in where. .as(name) puts a numeric expression in select on find or one. The tenant predicate stays in front of the fragment.

The check sql tag is a different function. It builds a check, an index predicate, or a default. It does not accept parameters.

where and select

length(${fragment.column}) uses the title field this fragment filters. ident("status") is the field status. len is a number.

fragment.ts

import { connect } from "okmodel/pg/postgresjs";
import { fragment, ident, schema, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
  status: t.text(),
});
const app = schema({ tables: [notes] });

const open = fragment`${ident("status")} = ${"open"}`;

export async function openNotes(url: string) {
  await using db = connect(url, { schema: app });
  return await db.notes.find({
    where: { title: fragment`length(${fragment.column}) > ${3} and ${open}` }, 
    select: ["id", fragment`length(${ident("title")})`.as("len")], 
    limit: 20,
  });
}

OKM1125

A fragment that closes the surrounding predicate, a comment, a semicolon, a $, or a backslash in the literal is OKM1125. So is an interpolation inside quotes, an empty fragment, and a select fragment that is not a number. Pass a value that needs a backslash as an interpolation. ident("") is OKM1122.

Next	You use it for
Typed raw SQL	A typed raw call.

On this page