Prisma 查询语法 | 分页

1. Prisma find

1. schema

   model Ticket {
     id             String    @id @default(cuid())
     title          String
     description    String
     status         String
     assigneeId     String?
     creatorId      String
     securityEventId String
     createdAt      DateTime  @default(now())
     updatedAt      DateTime  @updatedAt()
   
     assignee       User?     @relation("Assignee", fields: [assigneeId], references: [id])
     creator        User      @relation("Creator", fields: [creatorId], references: [id])
     securityEvent  SecurityEvent @relation(fields: [securityEventId], references: [id])
   }
   
   model User {
     id        String @id @default(cuid())
     name      String
     email     String
     tickets   Ticket[] @relation("Creator")
     assignedTickets Ticket[] @relation("Assignee")
   }
   
   model SecurityEvent {
     id      String @id @default(cuid())
     name    String
     tickets Ticket[]
   }
   

2. include

   const ticket = await prisma.ticket.findUnique({
     where: { id: "abc123" },
     include: {
       assignee: true,          // 获取 `assignee` 详情
       creator: true,           // 获取 `creator` 详情
       securityEvent: true      // 获取 `securityEvent` 详情
     }
   });
   
   console.log(ticket);
   
   const tickets = await prisma.ticket.findMany({
     include: {
       creator: true
     }
   });
   

3. select

   const ticket = await prisma.ticket.findUnique({
     where: { id: "abc123" },
     select: {
       title: true,
       assignee: {
         select: {
           name: true,
           email: true
         }
       }
     }
   });
   

4. 多层关联

   const ticket = await prisma.ticket.findUnique({
     where: { id: "abc123" },
     include: {
       creator: {
         include: {
           assignedTickets: true // 获取创建人名下所有分配的工单
         }
       }
     }
   });
   

2. Prisma 中的分页 (Pagination) 实现

Prisma 提供了多种分页方法,适用于不同的查询场景.最常用的分页策略包括：

• skip + take(经典偏移量分页,类似 SQL 的 OFFSET)

  
  const page = 2;
  const pageSize = 10;
  
  const total = await prisma.ticket.count();
  const tickets = await prisma.ticket.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { updatedAt: "desc" } // 推荐加排序,避免数据混乱
  });
  

• cursor 分页(基于唯一 ID,实现高效分页,推荐在数据量较大时使用)

  const pageSize = 10;
  const lastTicketId = "abc123"; // 上次查询的最后一条数据ID (cursor)
  
  const tickets = await prisma.ticket.findMany({
    take: pageSize,
    skip: 1,  // 跳过 Cursor 本身
    cursor: { id: lastTicketId }, // 起始位置
    orderBy: { updatedAt: "desc" }
  });
  

• page + `limit`` 逻辑分页(常见于 RESTful API)

  const page = parseInt(req.query.page as string) || 1;
  const limit = parseInt(req.query.limit as string) || 10;
  
  const tickets = await prisma.ticket.findMany({
    skip: (page - 1) * limit,
    take: limit,
    orderBy: { updatedAt: "desc" }
  });
  
  const total = await prisma.ticket.count();
  const totalPages = Math.ceil(total / limit);
  
  res.json({
    data: tickets,
    pagination: {
      total,
      page,
      totalPages
    }
  });