API with NestJS #84. Implementing filtering using subqueries with raw SQL

November 21, 2022

This entry is part 84 of 121 in the API with NestJS

In this series, we’ve often had to filter the records in our database. We can achieve that with a simple WHERE clause.

1 2	SELECT * FROM posts WHERE author_id = 1

In 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.

CREATE TABLE posts (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

title text NOT NULL,

post_content text NOT NULL,

author_id int REFERENCES users(id) NOT NULL

)

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.

postsStatistics.controller.ts

import { ClassSerializerInterceptor, Controller, Get, UseInterceptors } from "@nestjs/common";

import PostsStatisticsService from './postsStatistics.service';

@Controller('posts-statistics')

@UseInterceptors(ClassSerializerInterceptor)

export default class PostsStatisticsController {

constructor(

private readonly postsStatisticsService: PostsStatisticsService,

) {}

@Get('users-with-any-posts')

getAuthorsWithAnyPosts() {

return this.postsStatisticsService.getAuthorsWithAnyPosts();

}

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.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from '../users/user.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getAuthorsWithAnyPosts() {

const databaseResponse = await this.databaseService.runQuery(`

SELECT * FROM users

WHERE EXISTS (

SELECT id FROM posts

WHERE posts.author_id=users.id

)

`);

return databaseResponse.rows.map(

(databaseRow) => new UserModel(databaseRow),

);

}

export 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.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from '../users/user.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getAuthorsWithoutAnyPosts() {

const databaseResponse = await this.databaseService.runQuery(`

SELECT * FROM users

WHERE NOT EXISTS (

SELECT id FROM posts

WHERE posts.author_id=users.id

)

`);

return databaseResponse.rows.map(

(databaseRow) => new UserModel(databaseRow),

);

}

// ...

}

export 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.

postsStatistics.controller.ts

import {

ClassSerializerInterceptor,

Controller,

Get,

Param,

UseInterceptors,

} from '@nestjs/common';

import PostsStatisticsService from './postsStatistics.service';

import IdParams from './idParams';

@Controller('posts-statistics')

@UseInterceptors(ClassSerializerInterceptor)

export default class PostsStatisticsController {

constructor(

private readonly postsStatisticsService: PostsStatisticsService,

) {}

@Get('users-with-posts-in-category/:id')

getAuthorsWithoutPostsInCategory(@Param() { id: categoryId }: IdParams) {

return this.postsStatisticsService.getAuthorsWithPostsInCategory(

categoryId,

);

}

// ...

}

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.

categorytIdParams.ts

import { IsNumber } from 'class-validator';

import { Transform } from 'class-transformer';

class IdParams {

@IsNumber()

@Transform(({ value }) => Number(value))

id: number;

}

export default IdParams;

Thanks to the above, we can now use the category id in our subquery.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from '../users/user.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getAuthorsWithPostsInCategory(categoryId: number) {

const databaseResponse = await this.databaseService.runQuery(

SELECT email FROM users

WHERE EXISTS (

SELECT * FROM posts

JOIN categories_posts ON posts.id = categories_posts.post_id

WHERE posts.author_id = users.id AND categories_posts.category_id = $1

)

[categoryId],

);

return databaseResponse.rows.map(

(databaseRow) => new UserModel(databaseRow),

);

}

// ...

}

export 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.

SELECT * FROM users

WHERE id IN (

SELECT posts.author_id FROM posts

WHERE length(posts.post_content) > 100

)

Let’s take this concept further and accept a parameter with the desired length of the post.

postsStatistics.controller.ts

import {

ClassSerializerInterceptor,

Controller,

Get,

Param,

Query,

UseInterceptors,

} from '@nestjs/common';

import PostsStatisticsService from './postsStatistics.service';

import PostLengthParam from './postLengthParam';

@Controller('posts-statistics')

@UseInterceptors(ClassSerializerInterceptor)

export default class PostsStatisticsController {

constructor(

private readonly postsStatisticsService: PostsStatisticsService,

) {}

@Get('users-with-posts-longer-than')

getAuthorsWithPostsLongerThan(@Query() { postLength }: PostLengthParam) {

return this.postsStatisticsService.getAuthorsWithPostsLongerThan(

postLength,

);

}

// ...

}

Above, we use the PostLengthParam class that defines the postLength param and transforms it from a string to a number.

postLengthParam.ts

import { Transform } from 'class-transformer';

import { IsNumber, Min } from 'class-validator';

class PostLengthParam {

@IsNumber()

@Min(1)

@Transform(({ value }) => Number(value))

postLength: number;

}

export default PostLengthParam;

Thanks to the above, we can now use the IN keyword in a query with the postLength argument.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from '../users/user.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getAuthorsWithPostsLongerThan(postLength: number) {

const databaseResponse = await this.databaseService.runQuery(

SELECT email FROM users

WHERE id IN (

SELECT posts.author_id FROM posts

WHERE length(posts.post_content) >= $1

)

[postLength],

);

return databaseResponse.rows.map(

(databaseRow) => new UserModel(databaseRow),

);

}

// ...

}

export 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.

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from '../users/user.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getAuthorsWithPostsLongerThan(postLength: number) {

const databaseResponse = await this.databaseService.runQuery(

SELECT email FROM users

WHERE id = ANY (

SELECT posts.author_id FROM posts

WHERE length(posts.post_content) >= $1

)

[postLength],

);

return databaseResponse.rows.map(

(databaseRow) => new UserModel(databaseRow),

);

}

// ...

}

export 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.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostModel from './post.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getPostsShorterThanPostsOfAGivenUser(userId: number) {

const databaseResponse = await this.databaseService.runQuery(

SELECT title FROM posts

WHERE length(post_content) < ALL (

SELECT length(post_content) FROM posts

WHERE author_id = $1

)

[userId],

);

return databaseResponse.rows.map(

(databaseRow) => new PostModel(databaseRow),

);

}

// ...

}

export 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.

postsStatistics.repository.ts

import { Injectable } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from '../users/user.model';

@Injectable()

class PostsStatisticsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getUsersWithPostsShorterThanAverage() {

const databaseResponse = await this.databaseService.runQuery(

SELECT email FROM users

JOIN posts ON posts.author_id = users.id

GROUP BY email

HAVING avg(length(post_content)) < ALL (

SELECT avg(length(post_content)) FROM POSTS

)

[],

);

return databaseResponse.rows.map(

(databaseRow) => new UserModel(databaseRow),

);

}

// ...

}

export 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.

Series Navigation<< API with NestJS #83. Text search with tsvector and raw SQLAPI with NestJS #85. Defining constraints with raw SQL >>