Counter caches
Keep a count of a record’s associated records on the record itself, so reading it needs no query.
At a glance
| To | Write |
|---|---|
| keep an article’s count of comments | belongsTo(Article, { counterCache: true }), with commentsCount |
| keep it in an attribute of another name | counterCache: "replyCount", on the belongsTo and the hasMany |
| read the count, with no query | article.comments().size(), isEmpty(), hasAny() |
| set the count from the rows | Article.resetCounters(id, "comments") |
| fill in a new counter before it’s read | counterCache: { attribute: "commentsCount", active: false } |
The Models
The examples use these Models, whose attributes come from their tables through the schema active-record schema generates:
import { hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Comment } from "./comment";
export class Article extends ApplicationRecord {
comments = hasMany(Comment); // size() reads commentsCount
}import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";
export class Comment extends ApplicationRecord {
article = belongsTo(Article, { counterCache: true }); // keeps Article's commentsCount
}import { hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Reply } from "./reply";
export class Forum extends ApplicationRecord {
replies = hasMany(Reply, { counterCache: "replyCount" }); // not repliesCount
}import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Forum } from "./forum";
export class Reply extends ApplicationRecord {
forum = belongsTo(Forum, { counterCache: "replyCount" });
}import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";
export class ImportedComment extends ApplicationRecord {
static override table = "comments";
// Writes keep commentsCount, but nothing reads it until active: false goes.
article = belongsTo(Article, { counterCache: { attribute: "commentsCount", active: false } });
}// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 2ed73c98509c
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 },
title: { type: "string", null: false },
commentsCount: { type: "integer", null: false, default: () => 0 },
updatedAt: "datetime",
} 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;
/** forums — last changed by 0001_create_tables.sql */
export const forums = {
id: { type: "string", null: false, generateId: true },
replyCount: { type: "integer", null: false, default: () => 0 },
} as const;
/** replies — last changed by 0001_create_tables.sql */
export const replies = {
id: { type: "string", null: false, generateId: true },
forumId: { type: "string", null: false },
} 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" },
comments: { attributes: comments, primaryKey: "id" },
forums: { attributes: forums, primaryKey: "id" },
replies: { attributes: replies, primaryKey: "id" },
},
models: {
Article: "articles",
Comment: "comments",
Forum: "forums",
ImportedComment: "comments",
Reply: "replies",
},
});
declare module "../models/article" {
interface Article extends TableAttributes<typeof articles, (typeof Article)["attributes"]> {}
}
declare module "../models/comment" {
interface Comment extends TableAttributes<typeof comments, (typeof Comment)["attributes"]> {}
}
declare module "../models/forum" {
interface Forum extends TableAttributes<typeof forums, (typeof Forum)["attributes"]> {}
}
declare module "../models/imported-comment" {
interface ImportedComment
extends TableAttributes<typeof comments, (typeof ImportedComment)["attributes"]> {}
}
declare module "../models/reply" {
interface Reply extends TableAttributes<typeof replies, (typeof Reply)["attributes"]> {}
}CREATE TABLE articles (
id TEXT PRIMARY KEY NOT NULL,
title TEXT NOT NULL,
comments_count INTEGER NOT NULL DEFAULT 0,
updated_at DATETIME
);
CREATE TABLE comments (
id TEXT PRIMARY KEY NOT NULL,
article_id TEXT NOT NULL REFERENCES articles (id),
body TEXT NOT NULL
);
CREATE TABLE forums (
id TEXT PRIMARY KEY NOT NULL,
reply_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE replies (
id TEXT PRIMARY KEY NOT NULL,
forum_id TEXT NOT NULL REFERENCES forums (id)
);CREATE TABLE articles (
id UUID PRIMARY KEY,
title TEXT NOT NULL,
comments_count INTEGER NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ
);
CREATE TABLE comments (
id UUID PRIMARY KEY,
article_id UUID NOT NULL REFERENCES articles (id),
body TEXT NOT NULL
);
CREATE TABLE forums (
id UUID PRIMARY KEY,
reply_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE replies (
id UUID PRIMARY KEY,
forum_id UUID NOT NULL REFERENCES forums (id)
);Declaring a counter cache
Declare counterCache on the counted records’ belongsTo, as Comment does, and give the owner an attribute for the count:
- The attribute is named for the counted Model:
commentsCountfor comments. - To keep it in another attribute, name it, as
Replydoes:counterCache: "replyCount". - A polymorphic
belongsTocounts a record on whichever listed Model its type names, so each of them declares the attribute. - A counter cache on a Model on another database throws
InvalidModel, since its count couldn’t be written in the same batch. So do twobelongsTos on one foreign key that keep the same attribute, since each comment would count twice.
Counting records
Creating a comment adds one to its article’s count, and destroying it takes one away:
import { class ArticleArticle } from "./models/article";
const const article: Articlearticle = await class ArticleArticle.findByOrThrow<typeof Article>(this: typeof Article, conditions: SafeSql | Conditions<Article>): Promise<Article>Like `findBy`, but throws `RecordNotFound` instead of returning null.findByOrThrow({ title?: string | readonly string[] | Comparison<string> | undefinedtitle: "Hello" });
const const comment: Commentcomment = await const article: Articlearticle.Article.comments: () => HasManyCollection<Comment, ScopesOf<typeof Comment>>comments().Relation<Comment, ScopesOf<typeof Comment>>.create(attributes?: Partial<AttributeProperties<OverriddenSpecs<{
readonly id: {
readonly type: "string";
readonly null: false;
readonly generateId: true;
};
readonly articleId: {
readonly type: "string";
readonly null: false;
};
readonly body: {
readonly type: "string";
readonly null: false;
};
}, Readonly<Record<string, false | AttributeOverride>> | undefined>>>, init?: Init<Comment> | undefined): Promise<Comment>
Builds a record, awaits `init` with it if it's given, and saves it; resolves to it whether or
not it was saved (check `isPersisted()`).create({ body?: string | undefinedbody: "First!" }); // commentsCount + 1
await const comment: Commentcomment.Model.destroy(): Promise<false | Comment>Deletes the row and resolves to this record, now a read-only destroyed snapshot.
A row that's already gone still counts. `false` means a `beforeDestroy` or `aroundDestroy`
callback aborted (or an `aroundDestroy` didn't call `next()`), or a `restrictWithError`
association refused.destroy(); // commentsCount - 1- Changing a comment’s
articleIdmoves the count to the other article. An article you’ve loaded takes the new count too, without a save. - Removing comments through the article’s association counts them out:
delete,clear, anddeleteAll()onarticle.comments(). Removing records through an association through another counts out its join records the same way. - Bulk writes leave the count alone:
insert,insertAll,upsert,updateAll, anddeleteAll()on any other Relation.
Reading the count
size(), isEmpty(), and hasAny() on the association read the count, without a query:
import { class ArticleArticle } from "./models/article";
const const article: Articlearticle = await class ArticleArticle.findByOrThrow<typeof Article>(this: typeof Article, conditions: SafeSql | Conditions<Article>): Promise<Article>Like `findBy`, but throws `RecordNotFound` instead of returning null.findByOrThrow({ title?: string | readonly string[] | Comparison<string> | undefinedtitle: "Hello" });
const const count: numbercount = await const article: Articlearticle.Article.comments: () => HasManyCollection<Comment, ScopesOf<typeof Comment>>comments().Relation<Comment, ScopesOf<typeof Comment>>.size(): Promise<number>The number of records: a loaded association's (built records included) without a query;
otherwise a `COUNT(*)`, plus an association's unsaved built records.size(); // reads commentsCount, with no query- An association reads the counter named for it:
commentsreadscommentsCount. If its counter has another name, name it on thehasManytoo, asForumdoes. - An association with any other name counts its rows, so one narrowed by a scope counts only its own records.
count()always counts the rows.
Resetting a count
Set a count from the rows with resetCounters, after a bulk write for example:
import { class ArticleArticle } from "./models/article";
const const article: Articlearticle = await class ArticleArticle.findByOrThrow<typeof Article>(this: typeof Article, conditions: SafeSql | Conditions<Article>): Promise<Article>Like `findBy`, but throws `RecordNotFound` instead of returning null.findByOrThrow({ title?: string | readonly string[] | Comparison<string> | undefinedtitle: "Hello" });
await class ArticleArticle.KeyedWriteStatics.resetCounters: <typeof Article>(this: typeof Article, id: string | number, names: "comments" | readonly "comments"[], options?: CounterOptions<Article> | undefined) => Promise<void>Sets the counter caches of the record with this primary key (#61) to how many records each
named hasMany has, counted in the same write: `Topic.resetCounters(id, "replies")`, or a
list of names, and `{ touch }`. A missing record is `RecordNotFound`, as `find`'s.resetCounters(const article: Articlearticle.id: stringid, "comments", { CounterOptions<Article>.touch?: Touch<Article> | undefinedtouch: true }); // counts the rows- It takes one association’s name or a list of them, and sets every counter the association’s records keep on the article.
touch: truealso setsupdatedAt.- An association with a scope can’t reset a counter, since its records aren’t all the ones counted.
Adding a counter to existing records
Declare a new counter with active: false, as ImportedComment does, to fill it in before anything reads it:
- A new counter starts at zero, even for an article that already has comments.
- With
active: false, writes keep the count up to date, whilesize()and the others count the rows. - Run
resetCountersfor each article, then removeactive: false.