7.1.4 过滤与排序

一句话破题

不是所有数据都需要，也不是所有数据都要按默认顺序——过滤和排序让客户端能精确获取需要的数据。

查询参数设计

基本格式

GET /api/posts?status=published&author=alice&sort=-createdAt&page=1&pageSize=10

参数	作用	示例
status	过滤条件	status=published
author	过滤条件	author=alice
sort	排序字段	sort=-createdAt（-表示降序）
page	分页	page=1
pageSize	每页数量	pageSize=10

过滤实现

基本过滤

// app/api/posts/route.ts
export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url)
  
  // 构建过滤条件
  const where: Prisma.PostWhereInput = {}
  
  const status = searchParams.get('status')
  if (status) where.status = status
  
  const authorId = searchParams.get('authorId')
  if (authorId) where.authorId = authorId
  
  const posts = await prisma.post.findMany({ where })
  
  return NextResponse.json({ data: posts })
}

模糊搜索

const search = searchParams.get('search')
if (search) {
  where.OR = [
    { title: { contains: search, mode: 'insensitive' } },
    { content: { contains: search, mode: 'insensitive' } },
  ]
}

范围过滤

// 日期范围
const startDate = searchParams.get('startDate')
const endDate = searchParams.get('endDate')

if (startDate || endDate) {
  where.createdAt = {}
  if (startDate) where.createdAt.gte = new Date(startDate)
  if (endDate) where.createdAt.lte = new Date(endDate)
}

// 数值范围
const minPrice = searchParams.get('minPrice')
const maxPrice = searchParams.get('maxPrice')

if (minPrice || maxPrice) {
  where.price = {}
  if (minPrice) where.price.gte = parseFloat(minPrice)
  if (maxPrice) where.price.lte = parseFloat(maxPrice)
}

多值过滤

// GET /api/posts?tags=tech,design,ai

const tags = searchParams.get('tags')?.split(',')
if (tags?.length) {
  where.tags = { hasSome: tags }
}

排序实现

单字段排序

// sort=createdAt（升序）
// sort=-createdAt（降序，用 - 前缀）

const sort = searchParams.get('sort') || '-createdAt'
const isDesc = sort.startsWith('-')
const field = isDesc ? sort.slice(1) : sort

const posts = await prisma.post.findMany({
  where,
  orderBy: { [field]: isDesc ? 'desc' : 'asc' },
})

多字段排序

// sort=-createdAt,title

const sortParam = searchParams.get('sort') || '-createdAt'
const orderBy = sortParam.split(',').map(s => {
  const isDesc = s.startsWith('-')
  const field = isDesc ? s.slice(1) : s
  return { [field]: isDesc ? 'desc' : 'asc' }
})

const posts = await prisma.post.findMany({
  where,
  orderBy,
})

完整示例

// app/api/posts/route.ts
import { NextRequest, NextResponse } from 'next/server'
import { Prisma } from '@prisma/client'

export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url)
  
  // 分页参数
  const page = Math.max(1, parseInt(searchParams.get('page') || '1'))
  const pageSize = Math.min(100, parseInt(searchParams.get('pageSize') || '10'))
  const skip = (page - 1) * pageSize
  
  // 过滤条件
  const where: Prisma.PostWhereInput = {}
  
  const status = searchParams.get('status')
  if (status) where.status = status
  
  const search = searchParams.get('search')
  if (search) {
    where.OR = [
      { title: { contains: search, mode: 'insensitive' } },
      { content: { contains: search, mode: 'insensitive' } },
    ]
  }
  
  // 排序
  const sortParam = searchParams.get('sort') || '-createdAt'
  const isDesc = sortParam.startsWith('-')
  const sortField = isDesc ? sortParam.slice(1) : sortParam
  const orderBy = { [sortField]: isDesc ? 'desc' : 'asc' }
  
  // 查询
  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      orderBy,
      skip,
      take: pageSize,
      include: { author: true },
    }),
    prisma.post.count({ where }),
  ])
  
  return NextResponse.json({
    data: posts,
    pagination: {
      page,
      pageSize,
      total,
      totalPages: Math.ceil(total / pageSize),
    },
  })
}

字段选择

// 只返回需要的字段
// GET /api/posts?fields=id,title,author

const fieldsParam = searchParams.get('fields')
const select = fieldsParam
  ? Object.fromEntries(fieldsParam.split(',').map(f => [f, true]))
  : undefined

const posts = await prisma.post.findMany({
  where,
  select,
})

觉知：安全问题

1. 排序字段白名单

// ❌ 直接用用户输入的字段名
const orderBy = { [userInput]: 'desc' }  // 危险！

// ✅ 使用白名单
const allowedSortFields = ['createdAt', 'title', 'viewCount']
const field = allowedSortFields.includes(sortField) 
  ? sortField 
  : 'createdAt'

2. 分页参数限制

// 防止请求过多数据
const pageSize = Math.min(100, parseInt(params.pageSize || '10'))
const page = Math.max(1, parseInt(params.page || '1'))

3. 搜索注入防护

// Prisma 已经处理了 SQL 注入
// 但仍需注意正则表达式注入
const search = searchParams.get('search')
const sanitized = search?.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')

本节小结

要点	说明
过滤	用查询参数传递条件
排序	-field 表示降序
字段选择	减少不必要的数据传输
安全	白名单验证、参数限制