Filtering records with Prisma

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: { id, }, }); 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. 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: { OR: [ { 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: [ { OR: [ { 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({ OR: [ { 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';