4

API with NestJS #138. Filtering records with Prisma

 4 months ago
source link: https://wanago.io/2023/12/18/api-nestjs-prisma-sql-filtering/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

API with NestJS #138. Filtering records with Prisma

NestJS

December 18, 2023

This entry is part 138 of 138 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
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
import { ArticleNotFoundException } from './article-not-found.exception';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  // ...
  async getById(id: number) {
    const article = await this.prismaService.article.findUnique({
      where: {
    if (!article) {
      throw new ArticleNotFoundException(id);
    return article;

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

articles.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  searchByText(query: string) {
    return this.prismaService.article.findMany({
      where: {
        content: {
          contains: query,
          mode: 'insensitive',
  // ...

Thanks to adding mode: 'insensitive', our search is case-insensitive.

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

articles-search-service.ts
import { IsNotEmpty, IsOptional, IsString } from 'class-validator';
export class ArticlesSearchParamsDto {
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  textSearch?: string | null;

We can now use it in our controller.

articles.controller.ts
import { Controller, Get, Query } from '@nestjs/common';
import { ArticlesService } from './articles.service';
import { ArticlesSearchParamsDto } from './dto/articles-search-params.dto';
@Controller('articles')
export default class ArticlesController {
  constructor(private readonly articlesService: ArticlesService) {}
  @Get()
  getAll(@Query() { textSearch }: ArticlesSearchParamsDto) {
    if (textSearch) {
      return this.articlesService.searchByText(textSearch);
    return this.articlesService.getAll();
  // ...

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

Screenshot-from-2023-12-16-21-52-17.png

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
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  searchByText(query: string) {
    return this.prismaService.article.findMany({
      where: {
            content: {
              contains: query,
              mode: 'insensitive',
            title: {
              contains: query,
              mode: 'insensitive',
  // ...

Above, we are passing an array of conditions to the OR 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
import { IsNotEmpty, IsNumber, IsOptional, IsString } from 'class-validator';
import { Type } from 'class-transformer';
export class ArticlesSearchParamsDto {
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  textSearch?: string | null;
  @Type(() => Number)
  @IsOptional()
  @IsNumber()
  upvotesGreaterThan?: number | null;

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

articles.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  search(textSearch: string, upvotesGreaterThan: number) {
    return this.prismaService.article.findMany({
      where: {
        AND: [
            content: {
              contains: textSearch,
              mode: 'insensitive',
            upvotes: {
              gt: upvotesGreaterThan,
  // ...

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 and OR operators.

articles.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  search(textSearch: string, upvotesGreaterThan: number) {
    return this.prismaService.article.findMany({
      where: {
        AND: [
                content: {
                  contains: textSearch,
                  mode: 'insensitive',
                title: {
                  contains: textSearch,
                  mode: 'insensitive',
            upvotes: {
              gt: upvotesGreaterThan,
  // ...

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
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
import { Prisma } from '@prisma/client';
import { ArticlesSearchParamsDto } from './dto/articles-search-params.dto';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  search({ textSearch, upvotesGreaterThan }: ArticlesSearchParamsDto) {
    const searchInputs: Prisma.ArticleWhereInput[] = [];
    if (textSearch) {
      searchInputs.push({
            content: {
              contains: textSearch,
              mode: 'insensitive',
            title: {
              contains: textSearch,
              mode: 'insensitive',
    if (typeof upvotesGreaterThan === 'number') {
      searchInputs.push({
        upvotes: {
          gt: upvotesGreaterThan,
    if (!searchInputs.length) {
      return this.getAll();
    if (searchInputs.length === 1) {
      return this.prismaService.article.findMany({
        where: searchInputs[0],
    return this.prismaService.article.findMany({
      where: {
        AND: searchInputs,
  getAll() {
    return this.prismaService.article.findMany();
  // ...

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 getAll method,
  • if they provided a single param, we don’t use the AND operator,
  • we use the AND 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 search method.

articles.controller.ts
import { Controller, Get, Query } from '@nestjs/common';
import { ArticlesService } from './articles.service';
import { ArticlesSearchParamsDto } from './dto/articles-search-params.dto';
@Controller('articles')
export default class ArticlesController {
  constructor(private readonly articlesService: ArticlesService) {}
  @Get()
  getAll(@Query() searchParams: ArticlesSearchParamsDto) {
    return this.articlesService.search(searchParams);
  // ...

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
import { IsNotEmpty, IsNumber, IsOptional, IsString } from 'class-validator';
import { Type } from 'class-transformer';
export class ArticlesSearchParamsDto {
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  textSearch?: string | null;
  @Type(() => Number)
  @IsOptional()
  @IsNumber()
  upvotesGreaterThan?: number | null;
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  categoryName?: string | null;

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

this.prismaService.article.findMany({
  where: {
    categories: {
      some: {
        name: categoryName,

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

Let’s add this filter to our search method.

articles.service.ts
import { BadRequestException, Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
import { CreateArticleDto } from './dto/create-article.dto';
import { Prisma } from '@prisma/client';
import { PrismaError } from '../database/prisma-error.enum';
import { ArticleNotFoundException } from './article-not-found.exception';
import { UpdateArticleDto } from './dto/update-article.dto';
import { ArticlesSearchParamsDto } from './dto/articles-search-params.dto';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  search({
    textSearch,
    upvotesGreaterThan,
    categoryName,
  }: ArticlesSearchParamsDto) {
    const searchInputs: Prisma.ArticleWhereInput[] = [];
    if (categoryName) {
      searchInputs.push({
        categories: {
          some: {
            name: categoryName,
    // ...
    return this.prismaService.article.findMany({
      where: {
        AND: searchInputs,
  // ...

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
import { IsNotEmpty, IsNumber, IsOptional, IsString } from 'class-validator';
import { Type } from 'class-transformer';
export class ArticlesSearchParamsDto {
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  textSearch?: string | null;
  @Type(() => Number)
  @IsOptional()
  @IsNumber()
  upvotesGreaterThan?: number | null;
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  categoryName?: string | null;
  @IsOptional()
  @IsNotEmpty()
  @IsString()
  authorNameToAvoid?: string;

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

this.prismaService.article.findMany({
  where: {
    NOT: {
      author: {
        name: authorNameToAvoid,

We can add it to our search method.

articles.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../database/prisma.service';
import { Prisma } from '@prisma/client';
import { ArticlesSearchParamsDto } from './dto/articles-search-params.dto';
@Injectable()
export class ArticlesService {
  constructor(private readonly prismaService: PrismaService) {}
  search({
    textSearch,
    upvotesGreaterThan,
    categoryName,
    authorNameToAvoid,
  }: ArticlesSearchParamsDto) {
    const searchInputs: Prisma.ArticleWhereInput[] = [];
    if (authorNameToAvoid) {
      searchInputs.push({
        NOT: {
          author: {
            name: authorNameToAvoid,
    // ...
    return this.prismaService.article.findMany({
      where: {
        AND: searchInputs,
  // ...

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


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK