如何从Query Params优雅构建NestJS+Prisma筛选对象？

在NestJS结合Prisma操作PostgreSQL的场景中，从可选Query Params构建符合Prisma规则的筛选对象，可通过以下几种方案避免代码冗余混乱：

方案1：DTO+自定义管道统一处理 #

通过定义筛选DTO和专用管道，将参数转换逻辑集中管理，控制器代码更简洁。

1.1 定义筛选DTO #

import { PropertyType } from '@prisma/client';

export class HomeFilterDto {
  city?: string;
  minPrice?: string;
  maxPrice?: string;
  numberOfBedrooms?: string;
  numberOfBathrooms?: string;
  minLandSize?: string;
  maxLandSize?: string;
  propertyType?: PropertyType;
}

1.2 实现自定义转换管道 #

import { PipeTransform, Injectable } from '@nestjs/common';
import { HomeFilterDto } from './home-filter.dto';
import { PropertyType } from '@prisma/client';

@Injectable()
export class HomeFilterPipe implements PipeTransform<HomeFilterDto, any> {
  transform(value: HomeFilterDto) {
    const filters: any = {};

    // 基础字符串字段
    if (value.city) filters.city = value.city;
    if (value.propertyType) filters.propertyType = value.propertyType;

    // 数字转换字段
    if (value.numberOfBedrooms) {
      filters.numberOfBedrooms = parseInt(value.numberOfBedrooms);
    }
    if (value.numberOfBathrooms) {
      filters.numberOfBathrooms = parseInt(value.numberOfBathrooms);
    }

    // 处理price范围筛选
    const priceConditions: any = {};
    if (value.minPrice) priceConditions.gte = parseFloat(value.minPrice);
    if (value.maxPrice) priceConditions.lte = parseFloat(value.maxPrice);
    if (Object.keys(priceConditions).length > 0) {
      filters.price = priceConditions;
    }

    // 处理landSize范围筛选
    const landSizeConditions: any = {};
    if (value.minLandSize) landSizeConditions.gte = parseInt(value.minLandSize);
    if (value.maxLandSize) landSizeConditions.lte = parseInt(value.maxLandSize);
    if (Object.keys(landSizeConditions).length > 0) {
      filters.landSize = landSizeConditions;
    }

    return filters;
  }
}

1.3 控制器中使用 #

import { Controller, Get, Query, UsePipes } from '@nestjs/common';
import { HomeFilterDto } from './home-filter.dto';
import { HomeFilterPipe } from './home-filter.pipe';

@Controller('homes')
export class HomeController {
  constructor(private readonly prisma: PrismaService) {}

  @Get()
  @UsePipes(HomeFilterPipe)
  async getHomes(@Query() filters: any) {
    return this.prisma.home.findMany({ where: filters });
  }
}

方案2：Class-Transformer + Class-Validator 自动转换 #

利用NestJS生态的class-validator和class-transformer，实现参数校验与类型转换，再通过管道生成Prisma筛选结构。

2.1 带转换规则的DTO #

import { PropertyType } from '@prisma/client';
import { Transform } from 'class-transformer';
import { IsOptional, IsEnum, IsString, IsNumberString } from 'class-validator';

export class HomeFilterDto {
  @IsOptional()
  @IsString()
  city?: string;

  @IsOptional()
  @IsNumberString()
  minPrice?: string;

  @IsOptional()
  @IsNumberString()
  maxPrice?: string;

  @IsOptional()
  @IsNumberString()
  @Transform(({ value }) => parseInt(value))
  numberOfBedrooms?: number;

  @IsOptional()
  @IsNumberString()
  @Transform(({ value }) => parseInt(value))
  numberOfBathrooms?: number;

  @IsOptional()
  @IsNumberString()
  minLandSize?: string;

  @IsOptional()
  @IsNumberString()
  maxLandSize?: string;

  @IsOptional()
  @IsEnum(PropertyType)
  propertyType?: PropertyType;
}

2.2 转换为Prisma筛选的管道 #

import { PipeTransform, Injectable } from '@nestjs/common';
import { HomeFilterDto } from './home-filter.dto';

@Injectable()
export class PrismaFilterPipe implements PipeTransform<HomeFilterDto, any> {
  transform(dto: HomeFilterDto) {
    const filters: any = {};

    Object.assign(filters, {
      ...(dto.city && { city: dto.city }),
      ...(dto.numberOfBedrooms && { numberOfBedrooms: dto.numberOfBedrooms }),
      ...(dto.numberOfBathrooms && { numberOfBathrooms: dto.numberOfBathrooms }),
      ...(dto.propertyType && { propertyType: dto.propertyType }),
    });

    // 处理price范围
    const price = {};
    if (dto.minPrice) price['gte'] = parseFloat(dto.minPrice);
    if (dto.maxPrice) price['lte'] = parseFloat(dto.maxPrice);
    if (Object.keys(price).length) filters.price = price;

    // 处理landSize范围
    const landSize = {};
    if (dto.minLandSize) landSize['gte'] = parseInt(dto.minLandSize);
    if (dto.maxLandSize) landSize['lte'] = parseInt(dto.maxLandSize);
    if (Object.keys(landSize).length) filters.landSize = landSize;

    return filters;
  }
}

2.3 控制器中使用 #

import { Controller, Get, Query, UsePipes, ValidationPipe } from '@nestjs/common';
import { HomeFilterDto } from './home-filter.dto';
import { PrismaFilterPipe } from './prisma-filter.pipe';

@Controller('homes')
export class HomeController {
  constructor(private readonly prisma: PrismaService) {}

  @Get()
  @UsePipes(new ValidationPipe({ transform: true }), PrismaFilterPipe)
  async getHomes(@Query() filters: any) {
    return this.prisma.home.findMany({ where: filters });
  }
}

方案3：工具函数动态构建筛选对象 #

抽离通用工具函数，将重复的参数判断、类型转换逻辑封装，控制器直接调用生成筛选结构。

3.1 工具函数实现 #

// src/utils/prisma-filter.util.ts
import { PropertyType } from '@prisma/client';

type HomeQueryParams = {
  city?: string;
  minPrice?: string;
  maxPrice?: string;
  numberOfBedrooms?: string;
  numberOfBathrooms?: string;
  minLandSize?: string;
  maxLandSize?: string;
  propertyType?: PropertyType;
};

export function buildHomeFilters(params: HomeQueryParams) {
  const filters: any = {};

  // 基础字段处理
  addIfPresent(filters, 'city', params.city);
  addIfPresent(filters, 'propertyType', params.propertyType);

  // 数字转换字段处理
  addIfPresent(filters, 'numberOfBedrooms', params.numberOfBedrooms, val => parseInt(val));
  addIfPresent(filters, 'numberOfBathrooms', params.numberOfBathrooms, val => parseInt(val));

  // 范围筛选字段处理
  addRangeFilter(filters, 'price', params.minPrice, params.maxPrice, val => parseFloat(val));
  addRangeFilter(filters, 'landSize', params.minLandSize, params.maxLandSize, val => parseInt(val));

  return filters;
}

// 通用工具：值存在则添加，支持转换
function addIfPresent(obj: any, key: string, value: any, transform?: (val: any) => any) {
  if (value != null) {
    obj[key] = transform ? transform(value) : value;
  }
}

// 通用工具：添加范围筛选条件
function addRangeFilter(obj: any, field: string, minVal: any, maxVal: any, transform?: (val: any) => any) {
  const range: any = {};
  if (minVal != null) range.gte = transform ? transform(minVal) : minVal;
  if (maxVal != null) range.lte = transform ? transform(maxVal) : maxVal;
  if (Object.keys(range).length > 0) {
    obj[field] = range;
  }
}

3.2 控制器中使用 #

import { Controller, Get, Query } from '@nestjs/common';
import { buildHomeFilters } from './utils/prisma-filter.util';

@Controller('homes')
export class HomeController {
  constructor(private readonly prisma: PrismaService) {}

  @Get()
  async getHomes(@Query() params) {
    const filters = buildHomeFilters(params);
    return this.prisma.home.findMany({ where: filters });
  }
}

注：无需编写原生SQL，以上方案均基于Prisma的Query API构建筛选条件，符合Prisma的where参数规则。

内容的提问来源于stack exchange，提问作者danulqua