Count, group by and aggregates in Drizzle

Drizzle aggregates in NestJS are three counting tools on your service. count(filters) returns how many rows match. countBy(column, filters) runs one GROUP BY and returns the count for each value of a column. options.counts puts the number of related rows on each row under _count, with one grouped query per relation and no N+1. SUM, AVG, MIN and MAX are not built in: write those as a custom service method.

Which aggregates are there?Copy Anchor Link

Three methods and one option, all counting.

To skip the total count of a list, see how do I skip the count query?

How do I count rows?Copy Anchor Link

Call count(filters?, options?). It takes the same filters as findAll and returns a number.

await this.postsService.count({ status: 'published' });
// 15

Filters, options.search, the tenant scope and soft delete apply, so soft-deleted rows are not counted unless you pass withDeleted: true (or onlyDeleted: true to count only the trash).

How do I count rows per column value?Copy Anchor Link

Call countBy(column, filters?, options?). It returns one entry per value, largest group first, ties ordered by value.

await this.postsService.countBy('status');
// [{ value: 'published', count: 15 }, { value: 'draft', count: 14 }]

await this.postsService.countBy('author.name', { status: 'published' });
// [{ value: 'Ann', count: 5 }, { value: 'Bob', count: 5 }, { value: 'Cy', count: 5 }]

It is one query:

SELECT status AS value, count(*) AS count FROM posts
WHERE deleted_at IS NULL
GROUP BY status
ORDER BY count(*) DESC, status ASC

Two more rules:

• The column must be in filterableColumns when that list is set, since grouping exposes its values the way filtering does.
• The type of value is a generic: countBy<string>('status') returns CountByRow<string>[].

How do I add relation counts to each row?Copy Anchor Link

Pass options.counts with the relation names. Each row gets a _count object.

const page = await this.authorsService.findAll({}, {}, { counts: ['posts'] });
page.data[0]._count; // { posts: 10 }

counts works on find, findOne, findAll, findByCursor, and on the rows a write returns. From a query string, ?counts=posts,tags sets it. See parse query strings into filters.

How are relation counts loaded?Copy Anchor Link

With one SELECT key, count(*) ... WHERE key IN (...) GROUP BY key per relation, for every row of the page at once, and the relations run concurrently. A page of 50 authors with counts: ['posts', 'tags'] is the data query, the count query and two grouped queries, never one per row.

When select narrows the columns, the key the count groups on is added back, so the counts still attach.

Can I count only some related rows?Copy Anchor Link

Yes. Pass an object instead of a list. Each value is a filter on the related table, or true for all of them.

await this.authorsService.findAll({}, {}, {
  counts: { posts: { status: 'published' }, tags: true },
});
// _count: { posts: 5, tags: 2 }

The filter accepts everything a filter does, groups included.

What can be counted?Copy Anchor Link

These are the limits, and each one is a 400 when broken:

• Only has-many and many-to-many relations. Counting a belongs-to, or a relation that is not configured, is a 400.
• The relation must be in loadableRelations when that list is set.
• Counts are top level: posts, not posts.comments.

How do I get a SUM or AVG?Copy Anchor Link

Write a method on your service with Drizzle's own aggregate helpers. Your methods can use this.config.db, the Drizzle instance the service runs on.

import { and, eq, isNull, sum } from 'drizzle-orm';
import { SqlBaseCrudService, type CrudFilter } from '@avytrix/nestjs-drizzle-crud';
import { posts } from '../db/schema';

export class PostsService extends SqlBaseCrudService<Post, CreatePostDto, UpdatePostDto, CrudFilter> {
  async totalViews(authorId: number): Promise<number> {
    const [row] = await this.config.db
      .select({ total: sum(posts.views) })
      .from(posts)
      .where(and(eq(posts.author_id, authorId), isNull(posts.deleted_at)));
    return Number(row?.total ?? 0);
  }
}

A custom query does not get the package's soft delete, tenant scope or allowlists for free, so add the deleted_at IS NULL condition (and your tenant column) yourself, as above. Drizzle's sum returns a string, which is why the result goes through Number.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link