# 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](/docs/schema/postgres-specific-columns#search). 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](/docs/queries/where-operators#search-and-similarity).

### simple

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

```ts title="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 }); // [!code focus]
}
```

### complex

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

```ts title="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" }, // [!code focus]
    where: { body: matches("deploy rollback", { match: "phrase", language: "english" }) },
    orderBy: {
      body: relevance("deploy rollback", { match: "phrase", language: "english" }),
      id: "asc",
    },
    limit: 20,
  });
}
```

<Callout type="error" title="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.
</Callout>

<Callout type="error" title="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.
</Callout>

## Tree

`descendants(id)` reads the rows below `id`. `ancestors(id)` reads the rows above it. Declare a [relation](/docs/schema/relations) 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.`).

```ts title="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 }); // [!code focus]
}
```

### ancestors

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

```ts title="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 }); // [!code focus]
}
```

<Callout type="error" title="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.
</Callout>

## 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.

```ts title="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}` }, // [!code focus]
    select: ["id", fragment`length(${ident("title")})`.as("len")], // [!code focus]
    limit: 20,
  });
}
```

<Callout type="error" title="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.
</Callout>

| Next | You use it for |
| --- | --- |
| [Typed raw SQL](/docs/queries/typed-raw-sql) | A typed raw call. |

Index: [/llms.txt](/llms.txt)