Skip to content
Active Record
Esc
↑↓navigate↵open⌘Jpreview
On this page

Joins

Find records by their associated records, and filter or sort by a joined Model’s attributes.

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:

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);
}
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Author } from "./author";

export class Profile extends ApplicationRecord {
  author = belongsTo(Author);
}
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);
}
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";

export class Comment extends ApplicationRecord {
  article = belongsTo(Article);
}
// 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"]> {}
}
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
);
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:

import { class AuthorAuthor } from "./models/author";

const const commented: Author[]commented = await class AuthorAuthor.
joins<typeof Author>(this: typeof Author, ...args: (AssociationName<Author> | {
    readonly articles?: AssociationName<Article> | {
        readonly comments?: "article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        } | readonly ("article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        })[] | undefined;
        readonly author?: AssociationName<Author> | ... | readonly (AssociationName<...> | ...)[] | undefined;
    } | readonly (AssociationName<...> | {
        readonly comments?: "article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        } | readonly ("article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        })[] | undefined;
        readonly author?: AssociationName<Author> | ... | readonly (AssociationName<...> | ...)[] | undefined;
    })[] | undefined;
    readonly profile?: "author" | ... 2 more ... | undefined;
})[]): Relation<...>
Joins associations (`INNER JOIN`), so only records with a linked record are found: `joins("author")`, nested `joins({ author: "company" })`. Loads only this Model's columns.
joins
({
articles?: AssociationName<Article> | {
    readonly comments?: "article" | {
        readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
    } | readonly ("article" | {
        readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
    })[] | undefined;
    readonly author?: AssociationName<Author> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        readonly profile?: "author" | ... 2 more ... | undefined;
    } | readonly (AssociationName<...> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        readonly profile?: "author" | ... 2 more ... | undefined;
    })[] | undefined;
} | readonly (AssociationName<...> | {
    readonly comments?: "article" | {
        readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
    } | readonly ("article" | {
        readonly article?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
    })[] | undefined;
    readonly author?: AssociationName<Author> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        readonly profile?: "author" | ... 2 more ... | undefined;
    } | readonly (AssociationName<...> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        readonly profile?: "author" | ... 2 more ... | undefined;
    })[] | undefined;
})[] | undefined
articles
: "comments" }).Relation<Author, ScopesOf<typeof Author>>.distinct(): Relation<Author, ScopesOf<typeof Author>>
Removes duplicate rows (of the selected attributes).
distinct
().Relation<Author, ScopesOf<typeof Author>>.toArray(): Promise<Author[]>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.

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

import { class AuthorAuthor } from "./models/author";

const const everyAuthor: Author[]everyAuthor = await class AuthorAuthor.
leftJoins<typeof Author>(this: typeof Author, ...args: (AssociationName<Author> | {
    readonly profile?: "author" | {
        readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
    } | readonly ("author" | {
        readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
    })[] | undefined;
    readonly articles?: AssociationName<Article> | {
        readonly comments?: "article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        } | readonly ("article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        })[] | undefined;
        readonly author?: AssociationName<...> | ... 2 more ... | undefined;
    } | readonly (AssociationName<...> | {
        readonly comments?: "article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        } | readonly ("article" | {
            readonly article?: AssociationName<Article> | ... | readonly (AssociationName<...> | ...)[] | undefined;
        })[] | undefined;
        readonly author?: AssociationName<...> | ... 2 more ... | undefined;
    })[] | undefined;
})[]): Relation<...>
Joins associations as `joins()` does, but with `LEFT OUTER JOIN`: unlinked records stay.
leftJoins
("profile").Relation<Author, ScopesOf<typeof Author>>.toArray(): Promise<Author[]>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:

import { class ArticleArticle } from "./models/article";
import { class AuthorAuthor } from "./models/author";

const const byJane: Article[]byJane = await class ArticleArticle.
joins<typeof Article>(this: typeof Article, ...args: (AssociationName<Article> | {
    readonly author?: AssociationName<Author> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        readonly profile?: "author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        } | readonly ("author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        })[] | undefined;
    } | readonly (AssociationName<...> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        readonly profile?: "author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        } | readonly ("author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        })[] | undefined;
    })[] | undefined;
    readonly comments?: "article" | ... 2 more ... | undefined;
})[]): Relation<...>
Joins associations (`INNER JOIN`), so only records with a linked record are found: `joins("author")`, nested `joins({ author: "company" })`. Loads only this Model's columns.
joins
("author")
.Relation<Article, ScopesOf<typeof Article>>.merge<Author, ScopesOf<typeof Author>>(other: Relation<Author, ScopesOf<typeof Author>>): Relation<Article, ScopesOf<typeof Article>>
ANDs `other`'s conditions onto this Relation's. For select, order, limit, offset and distinct, a clause `other` sets explicitly replaces this one's; one it doesn't set is kept. Orders are replaced, not concatenated, so a merged order reads as written. `other` may be a Relation of a Model this one joins once (#24): its conditions apply to the joined table.
merge
(class AuthorAuthor.where<typeof Author>(this: typeof Author, conditions: SafeSql | Conditions<Author>): Relation<Author, ScopesOf<typeof Author>>where({ name?: string | readonly string[] | Comparison<string> | undefinedname: "Jane" }))
.Relation<Article, ScopesOf<typeof Article>>.toArray(): Promise<Article[]>toArray(); const const byAuthorName: Article[]byAuthorName = await class ArticleArticle.
joins<typeof Article>(this: typeof Article, ...args: (AssociationName<Article> | {
    readonly author?: AssociationName<Author> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        readonly profile?: "author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        } | readonly ("author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        })[] | undefined;
    } | readonly (AssociationName<...> | {
        readonly articles?: AssociationName<Article> | ... | readonly (AssociationName<Article> | ...)[] | undefined;
        readonly profile?: "author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        } | readonly ("author" | {
            readonly author?: AssociationName<Author> | ... | readonly (AssociationName<Author> | ...)[] | undefined;
        })[] | undefined;
    })[] | undefined;
    readonly comments?: "article" | ... 2 more ... | undefined;
})[]): Relation<...>
Joins associations (`INNER JOIN`), so only records with a linked record are found: `joins("author")`, nested `joins({ author: "company" })`. Loads only this Model's columns.
joins
("author")
.Relation<Article, ScopesOf<typeof Article>>.merge<Author, ScopesOf<typeof Author>>(other: Relation<Author, ScopesOf<typeof Author>>): Relation<Article, ScopesOf<typeof Article>>
ANDs `other`'s conditions onto this Relation's. For select, order, limit, offset and distinct, a clause `other` sets explicitly replaces this one's; one it doesn't set is kept. Orders are replaced, not concatenated, so a merged order reads as written. `other` may be a Relation of a Model this one joins once (#24): its conditions apply to the joined table.
merge
(class AuthorAuthor.order<typeof Author>(this: typeof Author, spec: SafeSql | OrderSpec<Author>): Relation<Author, ScopesOf<typeof Author>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
name?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
name
: "asc" }))
.Relation<Article, ScopesOf<typeof Article>>.toArray(): Promise<Article[]>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.

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.

Last updated on