知识库

Appearance

Return to top
On this page

Prisma 高级查询

Prisma Client 提供了丰富的查询功能，支持复杂的过滤、排序、分页和聚合操作。本指南将详细介绍如何使用这些高级查询功能。

复杂过滤操作

嵌套过滤

typescript
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

// 在关系上进行过滤
async function getUsersWithPublishedPosts() {
  const users = await prisma.user.findMany({
    where: {
      posts: {
        some: {                    // 用户至少有一篇已发布的文章
          published: true,
        },
      },
    },
    include: {
      posts: {
        where: {
          published: true,
        },
      },
    },
  })
  return users
}

// 多层嵌套过滤
async function getAuthorsWithRecentPopularPosts() {
  const authors = await prisma.user.findMany({
    where: {
      posts: {
        some: {
          AND: [
            {
              published: true,
            },
            {
              createdAt: {
                gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000), // 一周内
              },
            },
            {
              likes: {
                gt: 100, // 喜欢数大于100
              },
            },
          ],
        },
      },
    },
    include: {
      posts: {
        where: {
          published: true,
          createdAt: {
            gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000),
          },
        },
        orderBy: {
          likes: 'desc',
        },
      },
    },
  })
  return authors
}

高级比较操作

typescript
// 各种比较操作
async function getAdvancedComparisons() {
  const posts = await prisma.post.findMany({
    where: {
      AND: [
        {
          views: {
            gte: 1000,      // 大于等于
            lt: 10000,      // 小于
          },
        },
        {
          title: {
            contains: 'Prisma',    // 包含
            mode: 'insensitive',   // 不区分大小写
          },
        },
        {
          OR: [
            {
              createdAt: {
                gte: new Date('2023-01-01'),
                lt: new Date('2023-12-31'),
              },
            },
            {
              updatedAt: {
                gte: new Date(Date.now() - 24 * 60 * 60 * 1000), // 24小时内更新
              },
            },
          ],
        },
      ],
    },
    orderBy: [
      { views: 'desc' },      // 首先按浏览量降序
      { createdAt: 'desc' },  // 然后按创建时间降序
    ],
    take: 20, // 限制结果数量
  })
  return posts
}

聚合查询

基础聚合

typescript
// 各种聚合操作
async function getAggregations() {
  // 计数聚合
  const userCount = await prisma.user.count({
    where: {
      email: {
        endsWith: 'prisma.io',
      },
    },
  })

  // 高级聚合
  const postStats = await prisma.post.aggregate({
    where: {
      published: true,
    },
    _count: {
      _all: true,           // 计算所有记录
      title: true,          // 计算非空标题数量
    },
    _sum: {
      views: true,          // 求和
      likes: true,          // 喜欢数总和
    },
    _avg: {
      views: true,          // 平均值
      likes: true,
    },
    _min: {
      views: true,          // 最小值
      createdAt: true,
    },
    _max: {
      views: true,          // 最大值
      createdAt: true,
    },
  })

  return { userCount, postStats }
}

// 分组聚合
async function getGroupedAggregations() {
  // 按用户分组统计文章
  const userPostStats = await prisma.post.groupBy({
    by: ['authorId'],
    where: {
      published: true,
    },
    _count: {
      id: true,             // 每个用户的发布文章数
    },
    _sum: {
      views: true,          // 每个用户的总浏览量
    },
    _avg: {
      likes: true,          // 每个用户的平均喜欢数
    },
    having: {
      _count: {
        id: {
          gt: 5,            // 只包括发布超过5篇文章的用户
        },
      },
    },
  })

  return userPostStats
}

关联查询优化

智能关联加载

typescript
// 精确控制关联数据加载
async function getOptimizedQueries() {
  const users = await prisma.user.findMany({
    include: {
      posts: {
        select: {           // 只选择需要的字段
          id: true,
          title: true,
          published: true,
          _count: {
            select: {       // 统计相关数据而不获取详情
              comments: true,
            },
          },
        },
        where: {            // 过滤关联数据
          published: true,
        },
        orderBy: {          // 排序关联数据
          createdAt: 'desc',
        },
        take: 5,            // 限制关联数据数量
      },
      profile: {
        select: {           // 只获取资料的部分字段
          bio: true,
          avatar: true,
        },
      },
    },
  })
  return users
}

// 延迟加载模式
async function getLazyLoadingExample() {
  // 首先获取用户基本信息
  const users = await prisma.user.findMany({
    select: {
      id: true,
      email: true,
      name: true,
    },
  })

  // 根据需要延迟加载关联数据
  for (const user of users) {
    if (user.email.endsWith('prisma.io')) {
      // 只为特定用户加载额外数据
      user.posts = await prisma.post.findMany({
        where: {
          authorId: user.id,
          published: true,
        },
        take: 3,
      })
    }
  }

  return users
}

复杂查询模式

动态查询构建

typescript
// 动态构建查询条件
interface PostFilters {
  title?: string
  authorEmail?: string
  minViews?: number
  maxViews?: number
  published?: boolean
  tags?: string[]
  dateFrom?: Date
  dateTo?: Date
}

async function getPostsDynamic(filters: PostFilters) {
  const whereClause: any = {}

  if (filters.title) {
    whereClause.title = {
      contains: filters.title,
      mode: 'insensitive',
    }
  }

  if (filters.authorEmail) {
    whereClause.author = {
      email: filters.authorEmail,
    }
  }

  if (filters.minViews !== undefined || filters.maxViews !== undefined) {
    whereClause.views = {}
    if (filters.minViews !== undefined) {
      whereClause.views.gte = filters.minViews
    }
    if (filters.maxViews !== undefined) {
      whereClause.views.lte = filters.maxViews
    }
  }

  if (filters.published !== undefined) {
    whereClause.published = filters.published
  }

  if (filters.tags && filters.tags.length > 0) {
    whereClause.tags = {
      some: {
        name: {
          in: filters.tags,
        },
      },
    }
  }

  if (filters.dateFrom || filters.dateTo) {
    whereClause.createdAt = {}
    if (filters.dateFrom) {
      whereClause.createdAt.gte = filters.dateFrom
    }
    if (filters.dateTo) {
      whereClause.createdAt.lte = filters.dateTo
    }
  }

  const posts = await prisma.post.findMany({
    where: whereClause,
    include: {
      author: {
        select: {
          id: true,
          name: true,
          email: true,
        },
      },
    },
    orderBy: {
      createdAt: 'desc',
    },
  })

  return posts
}

查询缓存策略

typescript
// 简单的查询缓存实现
class QueryCache {
  private cache = new Map<string, { data: any; timestamp: number; ttl: number }>()

  async getCached<T>(
    key: string,
    queryFn: () => Promise<T>,
    ttl: number = 5 * 60 * 1000 // 5分钟默认TTL
  ): Promise<T> {
    const cached = this.cache.get(key)
    
    if (cached && Date.now() - cached.timestamp < cached.ttl) {
      console.log(`Cache hit for key: ${key}`)
      return cached.data
    }

    console.log(`Cache miss for key: ${key}`)
    const data = await queryFn()
    
    this.cache.set(key, {
      data,
      timestamp: Date.now(),
      ttl,
    })

    return data
  }

  invalidate(key: string) {
    this.cache.delete(key)
  }
}

const queryCache = new QueryCache()

// 使用缓存的热门文章查询
async function getPopularPostsCached() {
  return await queryCache.getCached(
    'popular-posts',
    async () => {
      return await prisma.post.findMany({
        where: {
          published: true,
          views: {
            gte: 1000,
          },
        },
        include: {
          author: true,
        },
        orderBy: [
          { views: 'desc' },
          { createdAt: 'desc' },
        ],
        take: 10,
      })
    },
    10 * 60 * 1000 // 10分钟TTL
  )
}

性能优化查询

索引友好的查询

typescript
// 使用复合索引的查询
async function getUsersWithIndexFriendlyQuery() {
  // 假设在 schema 中定义了复合索引
  // @@index([email, createdAt])
  
  const users = await prisma.user.findMany({
    where: {
      email: 'specific@example.com',  // 使用索引的第一个字段
      createdAt: {
        gte: new Date('2023-01-01'),
      },  // 使用索引的第二个字段
    },
    take: 100,
  })
  return users
}

// 分页查询优化
async function getPaginatedResults(page: number, pageSize: number = 10) {
  const skip = (page - 1) * pageSize

  // 使用 cursor-based pagination (推荐)
  const posts = await prisma.post.findMany({
    where: {
      published: true,
    },
    orderBy: {
      id: 'asc', // 使用稳定的排序字段
    },
    skip,
    take: pageSize,
  })

  // 获取总数（用于分页信息）
  const total = await prisma.post.count({
    where: {
      published: true,
    },
  })

  return {
    data: posts,
    meta: {
      page,
      pageSize,
      total,
      totalPages: Math.ceil(total / pageSize),
    },
  }
}

// Cursor-based 分页
async function getCursorBasedPagination(afterCursor?: number, first: number = 10) {
  const where = {
    published: true,
  }

  const posts = await prisma.post.findMany({
    where,
    orderBy: {
      id: 'asc',
    },
    cursor: afterCursor ? { id: afterCursor } : undefined,
    skip: afterCursor ? 1 : 0, // 跳过游标指向的记录
    take: first,
  })

  return posts
}

原生查询与 Prisma 查询结合

复杂统计查询

typescript
// 复杂的统计查询使用原生SQL
async function getComplexAnalytics() {
  // 使用原生查询进行复杂统计
  const monthlyStats = await prisma.$queryRaw<Array<{
    month: string
    postCount: bigint
    avgViews: number
    totalViews: bigint
  }>>`
    SELECT 
      DATE_TRUNC('month', "createdAt") as "month",
      COUNT(*) as "postCount",
      AVG("views") as "avgViews",
      SUM("views") as "totalViews"
    FROM "Post"
    WHERE "published" = true
    GROUP BY DATE_TRUNC('month', "createdAt")
    ORDER BY "month" DESC
    LIMIT 12
  `

  return monthlyStats
}

// 使用 Prisma 查询和原生查询结合
async function getCombinedQueryResults() {
  // 首先用 Prisma 获取基础数据
  const topAuthors = await prisma.user.findMany({
    include: {
      _count: {
        select: {
          posts: {
            where: {
              published: true,
            },
          },
        },
      },
    },
    orderBy: {
      posts: {
        _count: 'desc',
      },
    },
    take: 10,
  })

  // 然后用原生查询获取更详细的统计
  const authorStats = await prisma.$queryRaw<Array<{
    userId: number
    totalViews: bigint
    avgLikes: number
    engagementRate: number
  }>>`
    SELECT 
      "authorId" as "userId",
      SUM("views") as "totalViews",
      AVG("likes") as "avgLikes",
      (AVG("likes") * 100.0 / AVG(CASE WHEN "views" > 0 THEN "views" ELSE 1 END)) as "engagementRate"
    FROM "Post"
    WHERE "authorId" IN (${Prisma.join(topAuthors.map(u => u.id))})
      AND "published" = true
    GROUP BY "authorId"
  `

  // 结合两种结果
  return topAuthors.map(author => {
    const stats = authorStats.find(s => s.userId === author.id)
    return {
      ...author,
      stats: stats || null,
    }
  })
}

查询性能监控

查询分析和优化

typescript
// 查询性能监控
async function monitoredQuery<T>(queryName: string, queryFn: () => Promise<T>) {
  const startTime = Date.now()
  
  try {
    const result = await queryFn()
    
    const duration = Date.now() - startTime
    console.log(`Query "${queryName}" executed in ${duration}ms`)
    
    // 如果查询时间过长，记录警告
    if (duration > 1000) { // 超过1秒
      console.warn(`Slow query detected: "${queryName}", duration: ${duration}ms`)
    }
    
    return result
  } catch (error) {
    const duration = Date.now() - startTime
    console.error(`Query "${queryName}" failed after ${duration}ms:`, error)
    throw error
  }
}

// 使用监控的查询
async function getMonitoredUserPosts(userId: number) {
  return await monitoredQuery(
    `getUserPosts-${userId}`,
    async () => {
      return await prisma.post.findMany({
        where: {
          authorId: userId,
          published: true,
        },
        include: {
          tags: true,
        },
        orderBy: {
          createdAt: 'desc',
        },
        take: 20,
      })
    }
  )
}

通过掌握这些高级查询技巧，您可以构建高效、灵活的数据库查询，满足复杂的应用需求。