Calculations
Count, add up, average, and find the extremes of an attribute, overall or per group, without loading the records.
At a glance
| To | Write | Returns |
|---|---|---|
| add up an attribute | sum("priceCents") |
a number, 0 with no rows |
| average it | average("priceCents") |
a number, or null |
| find the smallest or largest | minimum("priceCents"), maximum("createdAt") |
the attribute’s type, or null |
| calculate an expression | average(sql`…`) |
what the database returns |
| calculate per group | group("isAvailable").count() |
a Map from group to result |
| keep some groups | group("isAvailable").having(…), with an sql condition |
a grouped Relation |
| choose the calculation by name | calculate("sum", "priceCents") |
as the calculation named |
The Model
The examples use this Model, whose attributes come from its table through the schema active-record schema generates:
import { ApplicationRecord } from "./application-record";
export class Product extends ApplicationRecord {}// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 69a8287500f9
import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";
/** products — last changed by 0001_create_products.sql */
export const products = {
id: { type: "string", null: false, generateId: true },
name: { type: "string", null: false },
priceCents: { type: "integer", null: false },
isAvailable: { type: "boolean", null: false },
createdAt: { type: "datetime", null: false },
updatedAt: { type: "datetime", 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: {
products: { attributes: products, primaryKey: "id" },
},
models: { Product: "products" },
});
declare module "../models/product" {
interface Product extends TableAttributes<typeof products, (typeof Product)["attributes"]> {}
}CREATE TABLE products (
id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
price_cents INTEGER NOT NULL,
is_available BOOLEAN NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
);CREATE TABLE products (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
price_cents INTEGER NOT NULL,
is_available BOOLEAN NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
);Calculating values
Add up, average, or find the extremes of an attribute:
import { class ProductProduct } from "./models/product";
const const total: numbertotal = await class ProductProduct.where<typeof Product>(this: typeof Product, conditions: SafeSql | Conditions<Product>): Relation<Product, ScopesOf<typeof Product>>where({ isAvailable?: boolean | readonly boolean[] | undefinedisAvailable: true }).Relation<Product, ScopesOf<typeof Product>>.sum<"priceCents">(attribute: "priceCents"): Promise<number>The total of `attribute`'s values over the records, leaving out NULLs; 0 with none.sum("priceCents"); // 0 with no products
const const averagePrice: number | nullaveragePrice = await class ProductProduct.average<typeof Product>(this: typeof Product, attribute: "priceCents" | SafeSql): Promise<number | null>The mean of `attribute`'s values over the records, leaving out NULLs; null with none.average("priceCents"); // a number, or null
const const cheapest: number | nullcheapest = await class ProductProduct.OverloadedStatics.minimum: <typeof Product, "priceCents">(this: typeof Product, attribute: "priceCents") => Promise<number | null>minimum("priceCents"); // or null
const const newest: Date | nullnewest = await class ProductProduct.OverloadedStatics.maximum: <typeof Product, "createdAt">(this: typeof Product, attribute: "createdAt") => Promise<Date | null>maximum("createdAt"); // a Date, or nullsumandaveragetake number attributes: integers, reals, and decimals. A decimal’s sum is text, such as"12.8".minimumandmaximumtake any attribute you can compare. The result has the attribute’s own type, so a datetime’s is aDate.- NULLs are left out. With no rows,
sumis 0 and the others arenull. Onnone(), they answer without a query.
Calculating over some rows
A calculation covers the rows toArray() would return, so a limit, an offset, or distinct() decides which values count:
import { class ProductProduct } from "./models/product";
const const mostExpensive: Relation<Product, ScopesOf<typeof Product>>mostExpensive = class ProductProduct.order<typeof Product>(this: typeof Product, spec: SafeSql | OrderSpec<Product>): Relation<Product, ScopesOf<typeof Product>>Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc",
nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.order({ priceCents?: OrderDirection | {
direction: OrderDirection;
nulls?: NullsPlacement;
} | undefined
priceCents: "desc" }).Relation<Product, ScopesOf<typeof Product>>.limit(count: number): Relation<Product, ScopesOf<typeof Product>>limit(3);
const const topThree: numbertopThree = await const mostExpensive: Relation<Product, ScopesOf<typeof Product>>mostExpensive.Relation<Product, ScopesOf<typeof Product>>.sum<"priceCents">(attribute: "priceCents"): Promise<number>The total of `attribute`'s values over the records, leaving out NULLs; 0 with none.sum("priceCents"); // only those threeCalculating an expression
Pass an sql fragment to calculate an expression:
import { function column(attribute: string): ColumnReferenceNames the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).column, function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class ProductProduct } from "./models/product";
const const inDollars: SafeSqlinDollars = function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`${function column(attribute: string): ColumnReferenceNames the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).column("priceCents")} / 100.0`;
const const averageDollars: number | nullaverageDollars = await class ProductProduct.average<typeof Product>(this: typeof Product, attribute: SafeSql | "priceCents"): Promise<number | null>The mean of `attribute`'s values over the records, leaving out NULLs; null with none.average(const inDollars: SafeSqlinDollars); // a number, or nullA fragment’s sum and average are numbers. Its minimum and maximum are the values the database returns.
Calculating per group
Group the rows by an attribute to calculate each group’s total:
import { class ProductProduct } from "./models/product";
const const byAvailability: Map<boolean, number>byAvailability = await class ProductProduct.OverloadedStatics.group: <typeof Product, readonly ["isAvailable"]>(this: typeof Product, terms_0: "isAvailable") => GroupedRelation<Product, readonly ["isAvailable"]>group("isAvailable").GroupedRelation<Product, readonly ["isAvailable"]>.count(attribute?: "isAvailable" | "id" | "name" | "priceCents" | "createdAt" | "updatedAt" | undefined): Promise<Map<boolean, number>>The number of rows in each group, or of their non-null values of `attribute`.count(); // Map { true => 3, false => 1 }
const const inStock: number | undefinedinStock = const byAvailability: Map<boolean, number>byAvailability.Map<boolean, number>.get(key: boolean): number | undefinedReturns a specified element from the Map object. If the value that is associated to the provided key is an object, then you will get a reference to that object and any change made to that object will effectively modify it inside the Map.get(true); // 3
const const valueByAvailability: Map<boolean, number>valueByAvailability = await class ProductProduct.OverloadedStatics.group: <typeof Product, readonly ["isAvailable"]>(this: typeof Product, terms_0: "isAvailable") => GroupedRelation<Product, readonly ["isAvailable"]>group("isAvailable").GroupedRelation<Product, readonly ["isAvailable"]>.sum<"priceCents">(attribute: "priceCents"): Promise<Map<boolean, number>>Each group's sum of `attribute`'s values or a fragment's, as `sum` on a Relation.sum("priceCents");- Each calculation returns a
Mapfrom the group’s value to its result.count,sum,average,minimum, andmaximumall work this way. - The keys have the attribute’s own type, so a boolean’s key is
trueorfalse, a datetime’s is aDate, and an enum’s is its name. Rows with a NULL value form a group with anullkey. - The groups come in the order the database returns them, unless you order them.
Grouping by several values
Pass each attribute or expression to group:
import { function column(attribute: string): ColumnReferenceNames the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).column, function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class ProductProduct } from "./models/product";
const const band: SafeSqlband = function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`CASE WHEN ${function column(attribute: string): ColumnReferenceNames the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).column("priceCents")} >= ${10_000} THEN 'pricey' ELSE 'cheap' END`;
const const byBand: Map<[boolean, unknown], number>byBand = await class ProductProduct.OverloadedStatics.group: <typeof Product, readonly ["isAvailable", SafeSql]>(this: typeof Product, terms_0: "isAvailable", terms_1: SafeSql) => GroupedRelation<Product, readonly ["isAvailable", SafeSql]>group("isAvailable", const band: SafeSqlband).GroupedRelation<Product, readonly ["isAvailable", SafeSql]>.count(attribute?: "priceCents" | "isAvailable" | "id" | "name" | "createdAt" | "updatedAt" | undefined): Promise<Map<[boolean, unknown], number>>The number of rows in each group, or of their non-null values of `attribute`.count(); // Map { [true, "cheap"] => 2, … }Each key is then an array of the values, in the order you passed them. An expression’s value is whatever the database returns.
Keeping some groups
Pass a condition on the groups’ totals to having:
import { function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class ProductProduct } from "./models/product";
const const crowded: GroupedRelation<Product, readonly ["isAvailable"]>crowded = class ProductProduct.OverloadedStatics.group: <typeof Product, readonly ["isAvailable"]>(this: typeof Product, terms_0: "isAvailable") => GroupedRelation<Product, readonly ["isAvailable"]>group("isAvailable").GroupedRelation<Product, readonly ["isAvailable"]>.having(condition: SafeSql): GroupedRelation<Product, readonly ["isAvailable"]>Keeps the groups an `sql` fragment matches, written with their aggregates:
``having(sql`COUNT(*) > ${1}`)``. Several are ANDed.having(function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`COUNT(*) > ${1}`);
const const crowdedCounts: Map<boolean, number>crowdedCounts = await const crowded: GroupedRelation<Product, readonly ["isAvailable"]>crowded.GroupedRelation<Product, readonly ["isAvailable"]>.count(attribute?: "isAvailable" | "id" | "name" | "priceCents" | "createdAt" | "updatedAt" | undefined): Promise<Map<boolean, number>>The number of rows in each group, or of their non-null values of `attribute`.count(); // Map { true => 3 }Ordering groups
Order the groups by a grouped attribute or by a total, and a limit then picks groups:
import { function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class ProductProduct } from "./models/product";
const const largestFirst: GroupedRelation<Product, readonly ["isAvailable"]>largestFirst = class ProductProduct.OverloadedStatics.group: <typeof Product, readonly ["isAvailable"]>(this: typeof Product, terms_0: "isAvailable") => GroupedRelation<Product, readonly ["isAvailable"]>group("isAvailable").GroupedRelation<Product, readonly ["isAvailable"]>.order(spec: SafeSql | OrderSpec<Product>): GroupedRelation<Product, readonly ["isAvailable"]>Sorts the groups by grouped attributes, or a fragment such as ``sql`COUNT(*) DESC` ``.order(function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`COUNT(*) DESC`);
const const largest: Map<boolean, number>largest = await const largestFirst: GroupedRelation<Product, readonly ["isAvailable"]>largestFirst.GroupedRelation<Product, readonly ["isAvailable"]>.limit(count: number): GroupedRelation<Product, readonly ["isAvailable"]>At most `count` groups.limit(1).GroupedRelation<Product, readonly ["isAvailable"]>.count(attribute?: "isAvailable" | "id" | "name" | "priceCents" | "createdAt" | "updatedAt" | undefined): Promise<Map<boolean, number>>The number of rows in each group, or of their non-null values of `attribute`.count(); // Map { true => 3 }where,limit,offset, anddistinct()work before or aftergroup. Afterdistinct(),count,sum, andaveragetake each group’s distinct values.- You can only order by attributes you group by, or by an
sqlfragment.groupdrops an earlier order by any other attribute, such as one from a default scope. It keeps an earlier fragment, so callreorder()beforegroupwhen a default scope orders by one. With alimitoroffset,groupthrows instead of dropping an order, so callreorder()first there too. - On
none(), each calculation returns an emptyMapwithout a query.