API with NestJS #138. Filtering records with Prisma

NestJS

This entry is part 138 of 158 in the API with NestJS

Filtering records is one of the essential skills to have when working with SQL databases. In this article, we’ll implement various examples using NestJS and Prisma to show how to filter the data in different cases. Thanks to that, we will learn how to find precisely the data we need quickly and easily.

Implementing a search feature

In this series, we’ve often filtered records by providing the exact value we are looking for.

articles.service.ts

Besides searching for a row with a specific value, we can use various filtering operators. One of the most straightforward ones is .

articles.service.ts

Thanks to adding , our search is case-insensitive.

Let’s allow the users to search for the articles by sending a query parameter.

articles-search-service.ts

We can now use it in our controller.

articles.controller.ts

Thanks to this, the users can now make GET requests that filter the articles by the content.

Combining multiple filtering conditions

We can apply multiple search conditions in a single query.

Using the OR operator

For example, let’s search for articles containing a particular piece of text in the title or the content.

articles.service.ts

Above, we are passing an array of conditions to the operator. Thanks to that, we include the article if either the content or the title matches the query.

Using the AND operator

We can also match articles that fulfill multiple conditions. Let’s allow the users to get the articles based on the number of upvotes.

articles-search-params.dto.ts

We need the operator to require the articles to fulfill more than one condition.

articles.service.ts

With the above approach, a particular article needs to have a specific text in its content, and have a particular number of upvotes.

We can take it a step further and combine the and operators.

articles.service.ts

There is one issue with this approach, though. We should have the following cases:

  • the user didn’t provide any search params,
  • they provided only the text search param,
  • they provided only the upvotes param,
  • they provided both the text search param and the upvotes param.

To do that, we need to get a bit creative.

articles.service.ts

With this approach, we treat the search params differently based on how many of them the user provided:

  • if they didn’t provide any, we call the method,
  • if they provided a single param, we don’t use the operator,
  • we use the operator only if the user provided more than one search param.

This solution keeps our controller clean and simple because we only need to call the method.

articles.controller.ts

Filtering on relationships

We can also use Prisma to filter the related records. Let’s allow the users to filter the articles by the category name.

articles-search-params.dto.ts

A single article can have multiple categories. Let’s use the operator to get articles where at least one category has a particular name.

The official documentation mentions more operators that can come in handy with relationships, such as , or .

Let’s add this filter to our method.

articles.service.ts

Negating filters

Another helpful technique allows us to negate certain filters. For example, let’s enable the users to filter out the articles written by an author with a particular name.

articles-search-params.dto.ts

To negate a particular filter, we can use the operator.

We can add it to our method.

articles.service.ts

Summary

In this article, we’ve learned how to filter records when using Prisma by implementing a search feature. By doing that, we learned how to implement conditional filtering when using the operator. While we included various real-life scenarios, Prisma mentions other useful operators in its official documentation. However, going through the examples from this article can give you a solid grasp of what Prisma can do.

Series Navigation<< API with NestJS #137. Recursive relationships with Prisma and PostgreSQLAPI with NestJS #139. Using UUID as primary keys with Prisma and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments