We can learn much about how users use our app by looking at our database. With this information, we can improve the experience of the users of our application. Luckily, PostgreSQL and Prisma make it easy to collect different kinds of data statistics. In this article, we learn how to use them to group and aggregate data.
Aggregating data
Let’s say we have the following models in our application.
1model Article {
2 id Int @id @default(autoincrement())
3 title String
4 content String?
5 upvotes Int @default(0)
6 author User @relation(fields: [authorId], references: [id])
7 authorId Int
8}
9
10model User {
11 id Int @id @default(autoincrement())
12 email String @unique
13 name String
14 password String
15 articles Article[]
16}We can gather various information through aggregating data with the aggregate function. Its job is to compute a single result from multiple rows.
One of the most straightforward operations we can do is to count an average upvotes value across all our articles.
1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class ReportsService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getArticlesStatistics() {
9 const result = await this.prismaService.article.aggregate({
10 _avg: {
11 upvotes: true,
12 },
13 });
14 return {
15 averageUpvotesCount: result._avg.upvotes
16 }
17 }
18}Another operation worth mentioning is summing. For example, we can get a sum of all upvotes our articles received.
1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class ReportsService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getArticlesStatistics() {
9 const result = await this.prismaService.article.aggregate({
10 _avg: {
11 upvotes: true,
12 },
13 _sum: {
14 upvotes: true,
15 },
16 });
17
18 return {
19 averageUpvotesCount: result._avg.upvotes,
20 allUpvotesReceived: result._sum.upvotes,
21 };
22 }
23}We can also use _max to get the biggest upvotes count any article ever received.
1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class ReportsService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getArticlesStatistics() {
9 const result = await this.prismaService.article.aggregate({
10 _avg: {
11 upvotes: true,
12 },
13 _sum: {
14 upvotes: true,
15 },
16 _max: {
17 upvotes: true,
18 },
19 });
20 return {
21 averageUpvotesCount: result._avg.upvotes,
22 allUpvotesReceived: result._sum.upvotes,
23 biggestUpvotesCount: result._max.upvotes,
24 };
25 }
26}If we want to get the length of the longest and shortest articles using Prisma, we need to get a little creative.
In PostgreSQL, we can use the combination of the max(), min(), and length() functions to get the biggest and smallest length of the content.
The length() function returns the length of a string.
1SELECT
2max(length(content)) AS longest_article_length,
3min(length(content)) AS shortest_article_length
4FROM "Article"Unfortunately, Prisma does not allow us to combine those functions using aggregate(). Because of that, we will have to run a raw SQL query.
1import { Injectable, InternalServerErrorException } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3import { isRecord } from '../utilities/is-record';
4
5@Injectable()
6export class ReportsService {
7 constructor(private readonly prismaService: PrismaService) {}
8
9 private async getArticlesLength() {
10 const rawResults = await this.prismaService.$queryRaw`
11 SELECT
12 max(length(content)) AS longest_article_length,
13 min(length(content)) AS shortest_article_length
14 FROM "Article"
15 `;
16 if (!Array.isArray(rawResults)) {
17 throw new InternalServerErrorException();
18 }
19 const result = rawResults[0];
20 if (!isRecord(result)) {
21 throw new InternalServerErrorException();
22 }
23 const longestArticleLength = result.longest_article_length;
24 const shortestArticleLength = result.shortest_article_length;
25 return {
26 longestArticleLength,
27 shortestArticleLength,
28 };
29 }
30
31 async getArticlesStatistics() {
32 const result = await this.prismaService.article.aggregate({
33 _avg: {
34 upvotes: true,
35 },
36 _sum: {
37 upvotes: true,
38 },
39 _max: {
40 upvotes: true,
41 },
42 });
43
44 const { longestArticleLength, shortestArticleLength } =
45 await this.getArticlesLength();
46
47 return {
48 averageUpvotesCount: result._avg.upvotes,
49 biggestUpvotesCount: result._max.upvotes,
50 allUpvotesReceived: result._sum.upvotes,
51 longestArticleLength,
52 shortestArticleLength,
53 };
54 }
55}Since the result of the $queryRaw has the type unknown, we need to narrow it down. Because of that, we use the isRecord function above.
1export function isRecord(value: unknown): value is Record<string, unknown> {
2 return value !== null && typeof value === 'object' && !Array.isArray(value);
3}The isRecord function is a type guard. If you want to know more, check out Structural type system and polymorphism in TypeScript. Type guards with predicates
Grouping
Besides aggregating our table as a whole, we can group the data by one or more fields.
For example, let’s sum all upvotes received by each author.
1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class ReportsService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getAuthorsStatistics() {
9 const results = await this.prismaService.article.groupBy({
10 by: 'authorId',
11 _sum: {
12 upvotes: true,
13 },
14 });
15
16 return results.map(({ authorId, _sum }) => {
17 return {
18 authorId,
19 allUpvotesReceived: _sum.upvotes,
20 };
21 });
22 }
23
24 // ...
25}Thanks to the above approach, we can get various information about all articles written by a particular author. Similarly to the aggregate function, we can calculate the average upvotes count and the maximum upvotes an author received on a single article.
1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class ReportsService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getAuthorsStatistics() {
9 const results = await this.prismaService.article.groupBy({
10 by: 'authorId',
11 _sum: {
12 upvotes: true,
13 },
14 _avg: {
15 upvotes: true,
16 },
17 _max: {
18 upvotes: true,
19 },
20 });
21
22 return results.map(({ authorId, _sum, _max, _avg }) => {
23 return {
24 authorId,
25 allUpvotesReceived: _sum.upvotes,
26 averageUpvotesCount: _avg.upvotes,
27 biggestUpvotesCount: _max.upvotes,
28 };
29 });
30 }
31
32 // ...
33}Sorting
Both the groupBy and aggregate functions allow us to sort the results. For example, let’s make sure to start with the authors who have the biggest sum of all their upvotes.
1import { Injectable } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class ReportsService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getAuthorsStatistics() {
9 const results = await this.prismaService.article.groupBy({
10 by: 'authorId',
11 _sum: {
12 upvotes: true,
13 },
14 _avg: {
15 upvotes: true,
16 },
17 _max: {
18 upvotes: true,
19 },
20 orderBy: {
21 _sum: {
22 upvotes: 'desc'
23 }
24 }
25 });
26
27 return results.map(({ authorId, _sum, _max, _avg }) => {
28 return {
29 authorId,
30 allUpvotesReceived: _sum.upvotes,
31 averageUpvotesCount: _avg.upvotes,
32 biggestUpvotesCount: _max.upvotes,
33 };
34 });
35 }
36
37 // ...
38}Filtering
We can also filter the rows taken into account when calculating the results. For example, let’s only consider articles with a negative upvotes count. To do that, we need the where property.
1const results = await this.prismaService.article.groupBy({
2 by: 'authorId',
3 _sum: {
4 upvotes: true,
5 },
6 _avg: {
7 upvotes: true,
8 },
9 _max: {
10 upvotes: true,
11 },
12 where: {
13 upvotes: {
14 lt: 0,
15 },
16 },
17});With where, we’ve managed to filter out the rows used for aggregation. We can also use the having keyword to filter entire groups. For example, we can show only authors with an average of ten or more upvotes on their articles.
1const results = await this.prismaService.article.groupBy({
2 by: 'authorId',
3 _sum: {
4 upvotes: true,
5 },
6 _avg: {
7 upvotes: true,
8 },
9 _max: {
10 upvotes: true,
11 },
12 having: {
13 upvotes: {
14 _avg: {
15 gt: 10
16 }
17 }
18 }
19});The groupBy function can use both where and having.
Summary
In this article, we’ve gone through the idea of aggregating our data to collect various statistics. To do that, we learned how to aggregate a table as a whole or group them by a particular field. Since Prisma does not support all cases we might encounter, we also used some raw queries to get the necessary information. By combining these methods, we can tailor our data analysis to fit exactly what we need for our projects.