In this series, we’ve often had to filter the records in our database. We can achieve that with a simple WHERE clause.
1SELECT * FROM posts
2WHERE author_id = 1In this article, we go through different use cases of more advanced filtering. We achieve it by using the WHERE keyword with subqueries.
EXISTS
In some of the previous parts of this series, we’ve defined the posts table.
1CREATE TABLE posts (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 title text NOT NULL,
4 post_content text NOT NULL,
5 author_id int REFERENCES users(id) NOT NULL
6)The EXISTS keyword returns true if the provided subquery returns at least one record. For example, we can use it to get a list of users that wrote at least one post.
To keep our codebase clean, let’s create a designated controller to manage post statistics.
1import { ClassSerializerInterceptor, Controller, Get, UseInterceptors } from "@nestjs/common";
2import PostsStatisticsService from './postsStatistics.service';
3
4@Controller('posts-statistics')
5@UseInterceptors(ClassSerializerInterceptor)
6export default class PostsStatisticsController {
7 constructor(
8 private readonly postsStatisticsService: PostsStatisticsService,
9 ) {}
10
11 @Get('users-with-any-posts')
12 getAuthorsWithAnyPosts() {
13 return this.postsStatisticsService.getAuthorsWithAnyPosts();
14 }
15}To get a list of users that wrote at least one post, we need to write a subquery that receives a list of posts by a given user.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from '../users/user.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getAuthorsWithAnyPosts() {
10 const databaseResponse = await this.databaseService.runQuery(`
11 SELECT * FROM users
12 WHERE EXISTS (
13 SELECT id FROM posts
14 WHERE posts.author_id=users.id
15 )
16 `);
17
18 return databaseResponse.rows.map(
19 (databaseRow) => new UserModel(databaseRow),
20 );
21 }
22}
23
24export default PostsStatisticsRepository;By using the EXIST keyword, we filter out the users for which the subquery does not return any records. By doing that, we achieved a list of users that wrote at least one post.
We can reverse the above logic by using NOT EXIST to get a list of users that didn’t write any posts.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from '../users/user.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getAuthorsWithoutAnyPosts() {
10 const databaseResponse = await this.databaseService.runQuery(`
11 SELECT * FROM users
12 WHERE NOT EXISTS (
13 SELECT id FROM posts
14 WHERE posts.author_id=users.id
15 )
16 `);
17
18 return databaseResponse.rows.map(
19 (databaseRow) => new UserModel(databaseRow),
20 );
21 }
22
23 // ...
24}
25
26export default PostsStatisticsRepository;Subqueries with JOIN
We can use subqueries that are a lot more complex than the example above. For example, let’s get a list of users that wrote a post in a specific category.
1import {
2 ClassSerializerInterceptor,
3 Controller,
4 Get,
5 Param,
6 UseInterceptors,
7} from '@nestjs/common';
8import PostsStatisticsService from './postsStatistics.service';
9import IdParams from './idParams';
10
11@Controller('posts-statistics')
12@UseInterceptors(ClassSerializerInterceptor)
13export default class PostsStatisticsController {
14 constructor(
15 private readonly postsStatisticsService: PostsStatisticsService,
16 ) {}
17
18 @Get('users-with-posts-in-category/:id')
19 getAuthorsWithoutPostsInCategory(@Param() { id: categoryId }: IdParams) {
20 return this.postsStatisticsService.getAuthorsWithPostsInCategory(
21 categoryId,
22 );
23 }
24
25 // ...
26}For the above method to work as expected, we’ve defined the IdParams class. Its purpose is to convert the param from a string to a number.
1import { IsNumber } from 'class-validator';
2import { Transform } from 'class-transformer';
3
4class IdParams {
5 @IsNumber()
6 @Transform(({ value }) => Number(value))
7 id: number;
8}
9
10export default IdParams;Thanks to the above, we can now use the category id in our subquery.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from '../users/user.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getAuthorsWithPostsInCategory(categoryId: number) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT email FROM users
13 WHERE EXISTS (
14 SELECT * FROM posts
15 JOIN categories_posts ON posts.id = categories_posts.post_id
16 WHERE posts.author_id = users.id AND categories_posts.category_id = $1
17 )
18 `,
19 [categoryId],
20 );
21
22 return databaseResponse.rows.map(
23 (databaseRow) => new UserModel(databaseRow),
24 );
25 }
26
27 // ...
28}
29
30export default PostsStatisticsRepository;IN
By using the IN keyword, we can check if any of the rows returned by a subquery matches a particular column. For example, let’s get a list of users who wrote a post longer than 100 characters.
1SELECT * FROM users
2WHERE id IN (
3 SELECT posts.author_id FROM posts
4 WHERE length(posts.post_content) > 100
5)Let’s take this concept further and accept a parameter with the desired length of the post.
1import {
2 ClassSerializerInterceptor,
3 Controller,
4 Get,
5 Param,
6 Query,
7 UseInterceptors,
8} from '@nestjs/common';
9import PostsStatisticsService from './postsStatistics.service';
10import PostLengthParam from './postLengthParam';
11
12@Controller('posts-statistics')
13@UseInterceptors(ClassSerializerInterceptor)
14export default class PostsStatisticsController {
15 constructor(
16 private readonly postsStatisticsService: PostsStatisticsService,
17 ) {}
18
19 @Get('users-with-posts-longer-than')
20 getAuthorsWithPostsLongerThan(@Query() { postLength }: PostLengthParam) {
21 return this.postsStatisticsService.getAuthorsWithPostsLongerThan(
22 postLength,
23 );
24 }
25
26 // ...
27}Above, we use the PostLengthParam class that defines the postLength param and transforms it from a string to a number.
1import { Transform } from 'class-transformer';
2import { IsNumber, Min } from 'class-validator';
3
4class PostLengthParam {
5 @IsNumber()
6 @Min(1)
7 @Transform(({ value }) => Number(value))
8 postLength: number;
9}
10
11export default PostLengthParam;Thanks to the above, we can now use the IN keyword in a query with the postLength argument.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from '../users/user.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getAuthorsWithPostsLongerThan(postLength: number) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT email FROM users
13 WHERE id IN (
14 SELECT posts.author_id FROM posts
15 WHERE length(posts.post_content) >= $1
16 )
17 `,
18 [postLength],
19 );
20
21 return databaseResponse.rows.map(
22 (databaseRow) => new UserModel(databaseRow),
23 );
24 }
25
26 // ...
27}
28
29export default PostsStatisticsRepository;ANY
By using the ANY keyword, we can check if any of the rows returned by a subquery matches a specific condition. When used with the = operator, it acts as the IN keyword.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from '../users/user.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getAuthorsWithPostsLongerThan(postLength: number) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT email FROM users
13 WHERE id = ANY (
14 SELECT posts.author_id FROM posts
15 WHERE length(posts.post_content) >= $1
16 )
17 `,
18 [postLength],
19 );
20
21 return databaseResponse.rows.map(
22 (databaseRow) => new UserModel(databaseRow),
23 );
24 }
25
26 // ...
27}
28
29export default PostsStatisticsRepository;The ANY keyword is more versatile than IN, though. We can use it with operators such as < and >. We can also use them when working with the ALL keyword.
ALL
When we use the ALL keyword, we check if all of the subquery results match a given condition. An example would be fetching a list of posts shorter than the posts of a given user.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostModel from './post.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getPostsShorterThanPostsOfAGivenUser(userId: number) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT title FROM posts
13 WHERE length(post_content) < ALL (
14 SELECT length(post_content) FROM posts
15 WHERE author_id = $1
16 )
17 `,
18 [userId],
19 );
20 return databaseResponse.rows.map(
21 (databaseRow) => new PostModel(databaseRow),
22 );
23 }
24 // ...
25}
26
27export default PostsStatisticsRepository;The above query might run for quite a bit of time without appropriate indexes. If you want to know more about how to optimize our queries for performance, check out API with NestJS #82. Introduction to indexes with raw SQL queries
The ALL keyword would also be a good choice when we expect our subquery to return just one result. For example, let’s find the users that wrote posts shorter than average.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from '../users/user.model';
4
5@Injectable()
6class PostsStatisticsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getUsersWithPostsShorterThanAverage() {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT email FROM users
13 JOIN posts ON posts.author_id = users.id
14 GROUP BY email
15 HAVING avg(length(post_content)) < ALL (
16 SELECT avg(length(post_content)) FROM POSTS
17 )
18 `,
19 [],
20 );
21
22 return databaseResponse.rows.map(
23 (databaseRow) => new UserModel(databaseRow),
24 );
25 }
26
27 // ...
28}
29
30export default PostsStatisticsRepository;Summary
In this article, we’ve gone through more advanced filtering using the WHERE keyword and subqueries. When doing that, we’ve gone through examples of using the EXISTS, IN, ANY, and ALL keywords. All of the above can come in handy when generating statistics.