StabilizeORM

Aggregates API

Count, sum, average, min and max on the Repository and the QueryBuilder

count()

typescript
async count(conditions?: Partial<T>): Promise<number>

Counts rows, excluding soft-deleted ones.

Parameters:

  • conditions Optional partial object of column values to match.
example/count.ts
const n = await repo.count({ status: "active" });

countDistinct()

typescript
async countDistinct(column: string): Promise<number>

Counts distinct values of a column. This exists on the Repository only — there is no countDistinct() on the QueryBuilder. To do it on a builder, select COUNT(DISTINCT col) AS __cnt yourself.

example/count-distinct.ts
const countries = await repo.countDistinct("country");

aggregate()

typescript
async aggregate(options: {
  count?: string | string[];
  sum?: string[];
  avg?: string[];
  min?: string[];
  max?: string[];
}): Promise<Record<string, any>>

Runs every requested aggregate in a single query. This is the only place to compute sum, avg, min and max from a Repository.

Options:

  • count One column name or an array of them. "*" counts all rows.
  • sum Array of columns to sum.
  • avg Array of columns to average.
  • min Array of columns to take the minimum of.
  • max Array of columns to take the maximum of.

Result keys are built from the aggregate and the column name:

typescript
count_all      // when you pass "*"
count_<col>
sum_<col>
avg_<col>
min_<col>
max_<col>
example/aggregate.ts
const stats = await repo.aggregate({
  count: "*",
  sum: ["amount"],
  avg: ["amount"],
  min: ["amount"],
  max: ["amount"],
});
// { count_all: 42, sum_amount: 1980, avg_amount: 47.1, min_amount: 3, max_amount: 210 }
example/aggregate-grouped.ts
const perStatus = await repo.aggregate({
  count: ["status", "region"],
});

QueryBuilder aggregates

typescript
count(column: string = "*", alias: string = "count"): QueryBuilder<T>
sum(column: string, alias: string = "sum"): QueryBuilder<T>
avg(column: string, alias: string = "avg"): QueryBuilder<T>
min(column: string, alias: string = "min"): QueryBuilder<T>
max(column: string, alias: string = "max"): QueryBuilder<T>

Every method is chainable and returns the same QueryBuilder<T>, so aggregates compose with filters. Run the builder with .execute(client).

Parameters:

  • column Column to aggregate. count() defaults to "*"; the others have no default.
  • alias Output key for the value. Defaults are "count", "sum", "avg", "min" and "max".
example/query-builder-aggregates.ts
const rows = await repo
  .find()
  .where("status = ?", "paid")
  .sum("amount", "total")
  .execute(orm.client);

countExec() / existsExec()

typescript
async countExec(client: DBClient): Promise<number>
async existsExec(client: DBClient): Promise<boolean>

Terminal helpers on the QueryBuilder that execute immediately against a client instead of returning a builder.

example/exec-helpers.ts
const total = await repo.find().countExec(orm.client);
const any = await repo.find().where("status = ?", "active").existsExec(orm.client);

Repository or QueryBuilder?

There are no sum(), avg(), min() or max() methods on the Repository — those exist only on the QueryBuilder. On a repository, use aggregate() instead.

typescript
// count and countDistinct exist on both
await repo.count();
await repo.countDistinct("country");
await repo.find().count().execute(orm.client);


// sum/avg/min/max: QueryBuilder only
await repo.find().sum("amount").execute(orm.client);


// on the Repository, use aggregate()
await repo.aggregate({ sum: ["amount"] });