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

JavaScript NestJS SQL

This entry is part 84 of 168 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 clause.

In this article, we go through different use cases of more advanced filtering. We achieve it by using the keyword with subqueries.

EXISTS

In some of the previous parts of this series, we’ve defined the table.

The 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

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

By using the 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 to get a list of users that didn’t write any posts.

postsStatistics.repository.ts

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

For the above method to work as expected, we’ve defined the class. Its purpose is to convert the param from a string to a number.

categorytIdParams.ts

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

postsStatistics.repository.ts

IN

By using the 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.

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

postsStatistics.controller.ts

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

postLengthParam.ts

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

postsStatistics.repository.ts

ANY

By using the 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 keyword.

The keyword is more versatile than , though. We can use it with operators such as and . We can also use them when working with the keyword.

ALL

When we use the 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

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

Summary

In this article, we’ve gone through more advanced filtering using the keyword and subqueries. When doing that, we’ve gone through examples of using the , , , and 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 >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments