Filtering and Sorting

The Product Query DTO

src/products/dto/product-query.dto.ts

import { Type } from 'class-transformer';
import { IsIn, IsOptional, IsString, Matches, Min } from 'class-validator';
import { PaginationQueryDto } from '../../common/dto/pagination-query.dto';

export class ProductQueryDto extends PaginationQueryDto {
  @IsOptional() @IsString() q?: string;                       // search text
  @IsOptional() @IsString() category?: string;
  @IsOptional() @Type(() => Number) @Min(0) minPrice?: number;
  @IsOptional() @Type(() => Number) @Min(0) maxPrice?: number;
  @IsOptional() @IsIn(['true', 'false']) inStock?: 'true' | 'false';

  // allow-list of sortable fields: "-price" = descending
  @IsOptional()
  @Matches(/^-?(price|name|createdAt)$/, { message: 'sort must be one of price, name, createdAt (prefix - for desc)' })
  sort: string = '-createdAt';
}

Building the Prisma Query

src/products/products.service.ts

@Injectable()
export class ProductsService {
  constructor(private readonly prisma: PrismaService) {}

  async search(query: ProductQueryDto) {
    const { page, limit, q, category, minPrice, maxPrice, inStock, sort } = query;
    const where: Prisma.ProductWhereInput = {
      ...(q && { name: { contains: q, mode: 'insensitive' } }),
      ...(category && { category: { slug: category } }),
      ...((minPrice !== undefined || maxPrice !== undefined) && { price: { gte: minPrice, lte: maxPrice } }),
      ...(inStock === 'true' && { stock: { gt: 0 } }),
    };
    const field = sort.replace('-', '') as 'price' | 'name' | 'createdAt';
    const [data, total] = await this.prisma.$transaction([
      this.prisma.product.findMany({
        where,
        orderBy: { [field]: sort.startsWith('-') ? 'desc' : 'asc' },
        skip: (page - 1) * limit,
        take: limit,
      }),
      this.prisma.product.count({ where }),
    ]);
    return { data, meta: { page, limit, total, totalPages: Math.ceil(total / limit) } };
  }
}

Example

curl "http://localhost:3000/products?q=buds&minPrice=1000&maxPrice=3000&sort=-price&limit=2"
curl "http://localhost:3000/products?sort=passwordHash"

Example output

{"data":[{"id":12,"name":"OnePlus Nord Buds 2","price":"2299","stock":55},{"id":19,"name":"Realme Buds Air 5","price":"1999","stock":80}],"meta":{"page":1,"limit":2,"total":6,"totalPages":3}}
{"message":["sort must be one of price, name, createdAt (prefix - for desc)"],"error":"Bad Request","statusCode":400}

Tip: Always allow-list sort fields. Passing user input directly into orderBy can leak hidden columns or cause slow queries on unindexed columns.

Filter, Sort and Page in Memory

Example

The same filter + sort + page steps on an array, useful for understanding and for unit tests:

type Product = { id: number; name: string; price: number; stock: number };
const products: Product[] = [
  { id: 1, name: 'boAt Airdopes 141', price: 1299, stock: 120 },
  { id: 2, name: 'OnePlus Nord Buds 2', price: 2299, stock: 0 },
  { id: 3, name: 'Realme Buds Air 5', price: 1999, stock: 80 },
  { id: 4, name: 'JBL Tune Buds', price: 4999, stock: 15 },
  { id: 5, name: 'Noise Buds VS104', price: 999, stock: 300 },
];
type Query = { q?: string; maxPrice?: number; inStock?: boolean; sort: string; page: number; limit: number };

function search(qry: Query) {
  let rows = products.filter((p) =>
    (!qry.q || p.name.toLowerCase().includes(qry.q.toLowerCase())) &&
    (qry.maxPrice === undefined || p.price <= qry.maxPrice) &&
    (!qry.inStock || p.stock > 0));
  const desc = qry.sort.startsWith('-');
  const field = qry.sort.replace('-', '') as 'price' | 'name';
  const compare = (a: Product, b: Product) =>
    field === 'name' ? a.name.localeCompare(b.name, 'en', { sensitivity: 'base' }) : a.price - b.price;
  rows = [...rows].sort((a, b) => compare(a, b) * (desc ? -1 : 1));
  const start = (qry.page - 1) * qry.limit;
  return { data: rows.slice(start, start + qry.limit).map((p) => p.name + ' ₹' + p.price), total: rows.length };
}

console.log(search({ q: 'buds', maxPrice: 3000, sort: '-price', page: 1, limit: 2 }));
console.log(search({ q: 'buds', maxPrice: 3000, sort: '-price', page: 2, limit: 2 }));
console.log(search({ inStock: true, sort: 'name', page: 1, limit: 10 }));

Output

{
  data: [ 'OnePlus Nord Buds 2 ₹2299', 'Realme Buds Air 5 ₹1999' ],
  total: 3
}
{ data: [ 'Noise Buds VS104 ₹999' ], total: 3 }
{
  data: [
    'boAt Airdopes 141 ₹1299',
    'JBL Tune Buds ₹4999',
    'Noise Buds VS104 ₹999',
    'Realme Buds Air 5 ₹1999'
  ],
  total: 4
}