# Relations

<Callout type="info" title="Where this fits">
The foreign key is the column. This page is the edge you include: one row, many rows, a join table, or one relation across several tables.
</Callout>

The key in `relations` is the name you pass to `include` and to `where`. `schema()` finds the foreign key. A value that is not `one()`, `many()`, `manyThrough()`, `morph()`, or `morphMany()` is OKM1061.

<Callout type="error" title="OKM1020">
A missing table is refused. The message names it and lists the accepted names. For a join, that includes the join table.
</Callout>

### To-one

`one(table, field)` is one related row. Include returns that row, or null when it is missing. `true` loads the row. `select`, `where`, and `orderBy` narrow it.

#### table

The argument is the related table name. The foreign key is a column on this table that points there.

```ts title="to-one.ts"
import { connect } from "okmodel/pg/postgresjs";
import { one, schema, table, t } from "okmodel/pg";

const users = table("users", {
  id: t.identity(),
  name: t.text(),
});
const notes = table(
  "notes",
  {
    id: t.identity(),
    authorId: t.bigint().references("users"),
    title: t.text(),
  },
  { relations: { author: one("users") } }, // [!code focus]
);
const app = schema({ tables: [users, notes] });

export async function authorName(url: string) {
  await using db = connect(url, { schema: app });
  const rows = await db.notes.find({ limit: 20, include: { author: true } }); // [!code focus]
  return rows[0]?.author?.name;
}
```

#### field

Pass `field` when this table has more than one foreign key to that table. It is the local field name. Omit it when there is one key. Zero keys and more than one key are both OKM1021. The message lists the columns, or `none`.

```ts title="two-keys.ts"
import { one, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  {
    id: t.identity(),
    authorId: t.bigint().references("users"),
    editorId: t.bigint().references("users"),
    title: t.text(),
  },
  {
    relations: {
      author: one("users", "authorId"), // [!code focus]
      editor: one("users", "editorId"), // [!code focus]
    },
  },
);
```

<Callout type="error" title="OKM1021">
`Relation notes.author on users is ambiguous. Accepted columns: authorId, editorId.`
</Callout>

### To-many

`many(table, field)` is the rows that point here. Include returns a list. A to-many include needs `limit`, or the call is OKM1105: `Include comments is to-many and has no limit. Pass limit, or call .all(reason).`

#### table

The argument is the other table. The foreign key is a column on that table that points here.

```ts title="to-many.ts"
import { connect } from "okmodel/pg/postgresjs";
import { many, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } }, // [!code focus]
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function firstComment(url: string) {
  await using db = connect(url, { schema: app });
  const rows = await db.notes.find({
    limit: 20,
    include: { comments: { limit: 1 } }, // [!code focus]
  });
  return rows[0]?.comments[0]?.body;
}
```

#### field

Pass `field` when the other table has more than one foreign key here. It is that table's field name. Omit it when there is one key. Zero keys and more than one key are both OKM1021.

```ts title="many-field.ts"
import { many, table, t } from "okmodel/pg";

export const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { edits: many("comments", "noteId") } }, // [!code focus]
);
```

### Filter

`has`, `none`, and `every` go in `where` under the relation name. A morph to-one refuses them: OKM1120, and the message says to filter the type and id columns or include the relation.

| Call | Keeps a row when |
| --- | --- |
| `has` | At least one related row matches. |
| `none` | No related row matches. |
| `every` | Every related row matches. No related rows also matches. |

#### has

`has` keeps a row that has a match.

```ts title="has.ts"
import { connect } from "okmodel/pg/postgresjs";
import { has, many, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } },
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function discussed(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    limit: 20,
    where: { comments: has({ body: "ship it" }) }, // [!code focus]
  });
}
```

#### none

`none` keeps a row that has no match.

```ts title="none.ts"
import { connect } from "okmodel/pg/postgresjs";
import { many, none, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } },
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function quiet(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    limit: 20,
    where: { comments: none({ body: "ship it" }) }, // [!code focus]
  });
}
```

#### every

`every` keeps a row whose related rows all match.

```ts title="every.ts"
import { connect } from "okmodel/pg/postgresjs";
import { every, many, schema, table, t } from "okmodel/pg";

const notes = table(
  "notes",
  { id: t.identity(), title: t.text() },
  { relations: { comments: many("comments") } },
);
const comments = table("comments", {
  id: t.identity(),
  noteId: t.bigint().references("notes"),
  body: t.text(),
});
const app = schema({ tables: [notes, comments] });

export async function agreed(url: string) {
  await using db = connect(url, { schema: app });
  return db.notes.find({
    limit: 20,
    where: { comments: every({ body: "ship it" }) }, // [!code focus]
  });
}
```

### Join table

`manyThrough(table, { through, from, to })` is to-many through a join table. The join table has one foreign key to this table and one to the related table. Include is to-many, so it needs `limit`. Tenancy and archive apply to the join rows and to the related rows.

#### through

`through` is the join table name.

```ts title="through.ts"
import { manyThrough, schema, table, t } from "okmodel/pg";

const teams = table("teams", { id: t.identity(), name: t.text() });
const memberships = table("memberships", {
  id: t.identity(),
  userId: t.bigint().references("users"),
  teamId: t.bigint().references("teams"),
});
const users = table(
  "users",
  { id: t.identity(), name: t.text() },
  { relations: { teams: manyThrough("teams", { through: "memberships" }) } }, // [!code focus]
);

export const app = schema({ tables: [users, teams, memberships] });
```

#### from

`from` is the join-table field that references this table. Pass it when more than one foreign key on the join table points here.

```ts title="from.ts"
import { manyThrough, table, t } from "okmodel/pg";

export const users = table(
  "users",
  { id: t.identity(), name: t.text() },
  {
    relations: {
      teams: manyThrough("teams", { through: "memberships", from: "userId" }), // [!code focus]
    },
  },
);
```

#### to

`to` is the join-table field that references the related table. Pass it when more than one foreign key points there. The same field for both ends is OKM1021.

```ts title="from-to.ts"
import { manyThrough, table, t } from "okmodel/pg";

export const users = table(
  "users",
  { id: t.identity(), name: t.text() },
  {
    relations: {
      teams: manyThrough("teams", { through: "memberships", from: "userId", to: "teamId" }), // [!code focus]
    },
  },
);
```

<Callout type="error" title="OKM1021">
`Relation users.teams through memberships is ambiguous at from. Accepted columns: (none).` The same field for both ends says `Pass from and to.`
</Callout>

### Morph

One table points at a closed list of others. Include returns the matching row, with the type field set to that table's name, or null when the row is missing, archived, outside the tenant, or the stored type is outside the list.

#### morph

`morph(name, targets)` is declared on the table that stores the pointer. `name` is the column stem. The targets are table names. An empty name, an empty target, or a target missing from the schema is OKM1020. The same target twice is OKM1023.

The table has `${name}Type` and `${name}Id`. The type column is text, `varchar`, `char`, or `citext`. A `varchar(n)` has to fit the longest target name. The id column matches each target's primary key, and that key is one column. The two columns are both nullable, or neither. A missing column is OKM1021. A type or id mismatch is OKM1022.

```ts title="morph-declare.ts"
import { morph, table, t } from "okmodel/pg";

export const comments = table(
  "comments",
  {
    id: t.text().primaryKey(),
    body: t.text(),
    commentableType: t.text(),
    commentableId: t.text(),
  },
  { relations: { commentable: morph("commentable", ["tasks", "lists"]) } }, // [!code focus]
);
```

#### morphMany

`morphMany(table, name)` is the reverse, declared on each target. `table` holds the type and id columns. `name` is the same stem. That table must declare `morph(name, targets)` and list this table. Include is to-many, so it takes `limit`, `orderBy`, `where`, and `select`. `has`, `none`, and `every` work on this side.

```ts title="morph-many.ts"
import { morphMany, table, t } from "okmodel/pg";

export const tasks = table(
  "tasks",
  { id: t.text().primaryKey(), title: t.text() },
  { relations: { comments: morphMany("comments", "commentable") } }, // [!code focus]
);
```

#### morphTo

`morphTo(name, type, id)` sets the type and id columns together. Spread it into insert or update. Setting one without the other, or clearing one and setting the other, is OKM1121. Clearing both is allowed. A type outside the list is OKM1120, and the message lists the accepted tables. An empty name or type is OKM1020. A null name, a null type, or an id that is not a non-empty string is OKM1120. A write can store another tenant's id. The include reads as the caller, so that target is null.

```ts title="morph-to.ts"
import { morphTo } from "okmodel/pg";

export const link = {
  id: "c1",
  body: "ship it",
  ...morphTo("commentable", "tasks", "t1"), // [!code focus]
};
```

#### include

A morph include is `true`. `select`, `where`, `orderBy`, and `limit` are OKM1120. Nesting it inside another include is OKM1120. A write can store another tenant's id. The include reads as the caller, so that target is null.

```ts title="morph.ts"
import { connect } from "okmodel/pg/postgresjs";
import { morph, morphMany, morphTo, schema, table, t } from "okmodel/pg";

const tasks = table(
  "tasks",
  { id: t.text().primaryKey(), title: t.text() },
  { relations: { comments: morphMany("comments", "commentable") } },
);
const lists = table(
  "lists",
  { id: t.text().primaryKey(), name: t.text() },
  { relations: { comments: morphMany("comments", "commentable") } },
);
const comments = table(
  "comments",
  {
    id: t.text().primaryKey(),
    body: t.text(),
    commentableType: t.text(),
    commentableId: t.text(),
  },
  { relations: { commentable: morph("commentable", ["tasks", "lists"]) } },
);
const app = schema({ tables: [tasks, lists, comments] });

export async function attach(url: string, taskId: string) {
  await using db = connect(url, { schema: app });
  await db.comments.insert({
    id: "c1",
    body: "ship it",
    ...morphTo("commentable", "tasks", taskId),
  });
  const rows = await db.comments.find({ limit: 20, include: { commentable: true } }); // [!code focus]
  return rows[0]?.commentable;
}
```