基于Prisma Raw的PostgreSQL Job分组统计查询求助

使用Prisma ORM开发，涉及Jobs和Payments两张数据表，表结构定义如下：

Jobs表结构 #

enum JobTypes {
  VISUAL_IDENTITY
  BRAND_DESIGN
  PACKAGING_DESIGN
  UI_UX
  NAMING
  ILLUSTRATION
  PHOTOGRAPHY
  VIDEO_FILMING
  AUDIO_SOUND
  OTHER
}

enum JobStatus {
  OPEN
  DONE
  CANCELED
}

model Job {
  id          String     @id @default(uuid())
  name        String     @db.VarChar(255)
  description String?    @db.VarChar(1000)
  customer    Customer   @relation(fields: [customerId], references: [id], onDelete: Cascade)
  customerId  String
  invoice     Invoice?   @relation(fields: [invoiceId], references: [id], onDelete: SetNull)
  invoiceId   String?    @unique
  types       JobTypes[]
  status      JobStatus
  deadline    DateTime?
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  finishedAt  DateTime?
  canceledAt  DateTime?

  payments Payment[]

  @@map(name: "jobs")
}

Payments表结构 #

model Payment {
  id                  String    @id @default(uuid())
  displayId           Int       @unique @default(autoincrement())
  value               Float
  dueDate             DateTime  @db.Date
  payedAt             DateTime? @db.Date
  notes               String?   @db.VarChar(255)
  job                 Job       @relation(fields: [jobId], references: [id], onDelete: Cascade)
  jobId               String
  sendReminderEmailTo String?   @db.VarChar(255)
  email               Email?    @relation(fields: [emailId], references: [id], onDelete: SetNull)
  emailId             String?   @unique
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt

  @@map(name: "payments")
}

需要编写可通过Prisma Raw执行的PostgreSQL查询语句，满足以下要求：

• 按Job的types字段中的单个类型分组（因types是数组类型）
• 统计各类型Job的数量
• 求和对应Job关联的Payments表中value字段的总金额
• 过滤customerId在指定值列表中的数据
• 输出格式示例：

[
    {
        "job_type": "ILLUSTRATION",
        "count": 10,
        "payments_sum": 10000
    },
    {
        "job_type": "UI_UX",
        "count": 10,
        "payments_sum": 10000
    }
]

PostgreSQL原始查询语句 #

SELECT
  unnest(j.types) AS job_type,
  COUNT(DISTINCT j.id) AS count,
  COALESCE(SUM(p.value), 0) AS payments_sum
FROM jobs j
LEFT JOIN payments p ON j.id = p.job_id
WHERE j.customer_id IN ('customer_id_1', 'customer_id_2', 'customer_id_3')
GROUP BY job_type
ORDER BY count DESC;

关键说明 #

1. UNNEST(j.types)：将Jobs表中types数组拆分为独立行，实现按单个Job类型分组
2. COUNT(DISTINCT j.id)：避免同一Job因包含多个类型被重复统计数量
3. COALESCE(SUM(p.value), 0)：确保没有关联支付记录的Job类型显示金额为0而非NULL
4. LEFT JOIN：保留所有符合条件的Job类型，即使没有对应的支付记录

Prisma Raw执行示例 #

在Prisma中使用$queryRaw执行该查询，安全传递customer_id列表参数：

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

const prisma = new PrismaClient();

async function getJobStatsByType(customerIds: string[]) {
  const result = await prisma.$queryRaw`
    SELECT
      unnest(j.types) AS job_type,
      COUNT(DISTINCT j.id) AS count,
      COALESCE(SUM(p.value), 0) AS payments_sum
    FROM jobs j
    LEFT JOIN payments p ON j.id = p.job_id
    WHERE j.customer_id IN (${Prisma.join(customerIds)})
    GROUP BY job_type
    ORDER BY count DESC;
  `;
  
  return result;
}

// 使用示例
const targetCustomerIds = ['uuid-1', 'uuid-2'];
getJobStatsByType(targetCustomerIds)
  .then(stats => console.log(stats))
  .catch(err => console.error(err))
  .finally(() => prisma.$disconnect());

注意事项 #

• 使用Prisma.join()处理数组参数，避免SQL注入风险
• 若需过滤特定状态的Job，可在WHERE子句中添加j.status = 'DONE'这类条件
• 查询结果会直接映射为符合需求的对象数组，无需额外转换

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