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

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 null
  • sum and average take number attributes: integers, reals, and decimals. A decimal’s sum is text, such as "12.8".
  • minimum and maximum take any attribute you can compare. The result has the attribute’s own type, so a datetime’s is a Date.
  • NULLs are left out. With no rows, sum is 0 and the others are null. On none(), 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 three

Calculating an expression

Pass an sql fragment to calculate an expression:

import { function column(attribute: string): ColumnReference
Names 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): ColumnReference
Names 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 null

A 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 | undefined
Returns 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.
@returnsReturns the element associated with the specified key. If no element is associated with the specified key, undefined is returned.
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 Map from the group’s value to its result. count, sum, average, minimum, and maximum all work this way.
  • The keys have the attribute’s own type, so a boolean’s key is true or false, a datetime’s is a Date, and an enum’s is its name. Rows with a NULL value form a group with a null key.
  • 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): ColumnReference
Names 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): ColumnReference
Names 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, and distinct() work before or after group. After distinct(), count, sum, and average take each group’s distinct values.
  • You can only order by attributes you group by, or by an sql fragment. group drops an earlier order by any other attribute, such as one from a default scope. It keeps an earlier fragment, so call reorder() before group when a default scope orders by one. With a limit or offset, group throws instead of dropping an order, so call reorder() first there too.
  • On none(), each calculation returns an empty Map without a query.

Last updated on