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 rowstoArray()returns, andupdateAllanddeleteAllwrite the records the join finds. - The association’s
scopeand 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:
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 notFiltering 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 theas:association of each Model instead, such asPhoto.joins("likes"). - An association through another. Merge a Relation of the joined Model instead, and preload with
includes.