Implementing filtering using subqueries with raw SQL

November 21, 2022

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