Implementing filtering using subqueries with raw SQL

In this series, we’ve often had to filter the records in our database. We can achieve that with a simple WHERE clause.SELECT * FROM posts WHERE 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.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 A