---
title: Joins
description: "Find records by their associated records, and filter or sort by a joined Model’s attributes."
seo:
  description: "Join Active Record associations with joins and leftJoins, get each record once with distinct, and filter or sort by a joined Model’s attributes with merge."
---

## At a glance

| To                                     | Write                                                |
| -------------------------------------- | ---------------------------------------------------- |
| find records that have a linked record | `Author.joins("articles")`                           |
| join through several associations      | `Author.joins({ articles: "comments" })`             |
| get each record once                   | `.distinct()`                                        |
| keep records with no linked record     | `Author.leftJoins("profile")`                        |
| filter or sort by the joined Model     | `Article.joins("author").merge(Author.where({ … }))` |

## The Models

The examples use these Models, whose attributes come from their tables through the schema `active-record schema` generates:

```ts author.ts
import { hasMany, hasOne } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";
import { Profile } from "./profile";

export class Author extends ApplicationRecord {
  articles = hasMany(Article);
  profile = hasOne(Profile);
}
```

```ts profile.ts
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Author } from "./author";

export class Profile extends ApplicationRecord {
  author = belongsTo(Author);
}
```

```ts article.ts
import { belongsTo, hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Author } from "./author";
import { Comment } from "./comment";

export class Article extends ApplicationRecord {
  author = belongsTo(Author);
  comments = hasMany(Comment);
}
```

```ts comment.ts
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";

export class Comment extends ApplicationRecord {
  article = belongsTo(Article);
}
```

```ts schema.ts
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: d6e921e3fa3b

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** articles — last changed by 0001_create_tables.sql */
export const articles = {
  id: { type: "string", null: false, generateId: true },
  authorId: { type: "string", null: false },
  title: { type: "string", null: false },
} as const;

/** authors — last changed by 0001_create_tables.sql */
export const authors = {
  id: { type: "string", null: false, generateId: true },
  name: { type: "string", null: false },
} as const;

/** comments — last changed by 0001_create_tables.sql */
export const comments = {
  id: { type: "string", null: false, generateId: true },
  articleId: { type: "string", null: false },
  body: { type: "string", null: false },
} as const;

/** profiles — last changed by 0001_create_tables.sql */
export const profiles = {
  id: { type: "string", null: false, generateId: true },
  authorId: { type: "string", null: false },
  bio: "string",
} as const;

/** The database's adapter and binding, its tables (with their primary keys), and the Models built on them. */
export const schema = defineSchema({
  adapter: d1({ binding: "DB" }),
  tables: {
    articles: { attributes: articles, primaryKey: "id" },
    authors: { attributes: authors, primaryKey: "id" },
    comments: { attributes: comments, primaryKey: "id" },
    profiles: { attributes: profiles, primaryKey: "id" },
  },
  models: { Article: "articles", Author: "authors", Comment: "comments", Profile: "profiles" },
});

declare module "../models/article" {
  interface Article extends TableAttributes<typeof articles, (typeof Article)["attributes"]> {}
}

declare module "../models/author" {
  interface Author extends TableAttributes<typeof authors, (typeof Author)["attributes"]> {}
}

declare module "../models/comment" {
  interface Comment extends TableAttributes<typeof comments, (typeof Comment)["attributes"]> {}
}

declare module "../models/profile" {
  interface Profile extends TableAttributes<typeof profiles, (typeof Profile)["attributes"]> {}
}
```

```sql D1
CREATE TABLE authors (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL
);
CREATE TABLE profiles (
  id TEXT PRIMARY KEY NOT NULL,
  author_id TEXT NOT NULL REFERENCES authors (id),
  bio TEXT
);
CREATE TABLE articles (
  id TEXT PRIMARY KEY NOT NULL,
  author_id TEXT NOT NULL REFERENCES authors (id),
  title TEXT NOT NULL
);
CREATE TABLE comments (
  id TEXT PRIMARY KEY NOT NULL,
  article_id TEXT NOT NULL REFERENCES articles (id),
  body TEXT NOT NULL
);
```

```sql Postgres
CREATE TABLE authors (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE profiles (
  id UUID PRIMARY KEY,
  author_id UUID NOT NULL REFERENCES authors (id),
  bio TEXT
);
CREATE TABLE articles (
  id UUID PRIMARY KEY,
  author_id UUID NOT NULL REFERENCES authors (id),
  title TEXT NOT NULL
);
CREATE TABLE comments (
  id UUID PRIMARY KEY,
  article_id UUID NOT NULL REFERENCES articles (id),
  body TEXT NOT NULL
);
```

## Joining associations

`joins` finds the records that have a linked record, through an association or nested ones:

```ts
import { Author } from "./models/author";

const commented = await Author.joins({ articles: "comments" }).distinct().toArray();
```

- A record comes back once for each linked record, so an author with three commented articles appears three times. `distinct()` gets each record once.
- A joined query loads only its own Model’s columns, and every Relation method works on it. `count()` counts the rows `toArray()` returns, and `updateAll` and `deleteAll` write the records the join finds.
- The association’s `scope` and the joined Model’s default scope are part of the join. So joining an association scoped to published articles finds only authors with a published article.

## Keeping records without a link

`leftJoins` joins the same way, but keeps the records that have no linked record:

```ts
import { Author } from "./models/author";

const everyAuthor = await Author.leftJoins("profile").toArray(); // with a profile or not
```

## Filtering by a joined Model

Merge a Relation of the joined Model to filter or sort by its attributes:

```ts
import { Article } from "./models/article";
import { Author } from "./models/author";

const byJane = await Article.joins("author")
  .merge(Author.where({ name: "Jane" }))
  .toArray();
const byAuthorName = await Article.joins("author")
  .merge(Author.order({ name: "asc" }))
  .toArray();
```

Its conditions, its scopes, and its order apply to the joined table. As with any `merge`, the joined Model’s order replaces the receiver’s.

:::note
The merged Relation can only add conditions and an order. A `limit`, `offset`, `select`, `distinct()`, `includes`, or `joins` of its own throws `IncompatibleRelation`. Merging a Model the query doesn’t join, or joins more than once, throws it too. So does `distinct()` on a query ordered by a joined Model.
:::

## Joining what can’t be joined

Some associations can’t be joined, and joining one throws `IncompatibleRelation` when you write the query:

- **A Model on another database.** A join reads one database.
- **A polymorphic `belongsTo`**, since its records may be in several tables. Join the `as:` association of each Model instead, such as `Photo.joins("likes")`.
- **An association through another.** Merge a Relation of the joined Model instead, and preload with `includes`.

> **Compared with Rails**
>
> - Rails builds the SQL for a merged Model the query doesn’t join, and the database rejects it when the query runs. Active Record throws `IncompatibleRelation` when you write it.
> - Rails applies a merged Relation’s `limit` to the whole query. Active Record refuses it.
> - Rails leaves `distinct` with an order on a joined table to the database, which Postgres refuses and SQLite runs. Active Record throws `IncompatibleRelation` on both.
> - With a `hasMany` join, Rails’ `find(1, 2)` counts the duplicate rows and raises `RecordNotFound`. Active Record returns each record once.
> - With a `hasMany` join, Rails’ `destroy_all` destroys a record once for each of its rows, running its callbacks each time. Active Record destroys each record once.
> - Rails’ `where(author: { name: "Jane" })`, `where.missing`, `eager_load`, and joins through another association aren’t here yet.
