在Prisma中处理隐式多对多关系：用户申请职位时的更新报错解决

问题背景 #

开发类LinkedIn/Indeed的招聘网站，使用Nest.js、Postgres和Prisma作为ORM，在实现用户申请职位功能时，尝试更新Job的申请者列表时触发唯一约束错误，且需要保留已有申请者。

Prisma模型Schema #

model Job {
  id                 String          @id @default(cuid())
  title              String
  description        String
  postedAt           DateTime        @default(now())
  location           Location
  experienceLevel    ExperienceLevel
  monthlySalary      Int
  currency           String
  requirements       Requirement[]
  activelyRecruiting Boolean         @default(true)
  companyId          String
  company            Company         @relation(fields: [companyId], references: [id], onDelete: Cascade)
  applicants         User[]
}

model User {
  id            String       @id @default(cuid())
  username      String
  password      String
  email         String       @unique
  github        String?
  linkedin      String?
  website       String?
  resume        String?
  experiences   Experience[]
  skills        Skill[]
  education     Education[]
  appliedToJobs Job[]
  createdAt     DateTime     @default(now())
  updatedAt     DateTime     @updatedAt
}

使用隐式多对多关系关联User和Job，无需存储额外关系元数据。

尝试过的代码及报错 #

尝试代码1 #

async applyToJob(id: string, accessor: Payload) {
        const user = await this.prisma.user.findUnique({ where: { id: accessor.id } });
        const job = await this.prisma.job.update({ where: { id }, data: { applicants: { create: [user] } } });
        return job;
    }

尝试代码2 #

async applyToJob(id: string, accessor: Payload) {
        const user = await this.prisma.user.findUnique({ where: { id: accessor.id } });
        const job = await this.prisma.job.update({ where: { id }, data: { applicants: { create: user } } });
        return job;
    }

报错信息 #

PrismaClientKnownRequestError: 
Invalid `this.prisma.job.update()` invocation in

  33
  34 async applyToJob(id: string, accessor: Payload) {
  35     const user = await this.prisma.user.findUnique({ where: { id: accessor.id } });
→ 36     const job = await this.prisma.job.update(
Unique constraint failed on the fields: (`id`)
    at Hr.handleRequestError (C:\Users\Muham\Documents\dev-jobs\api\node_modules\.pnpm\@prisma+client@5.1.1_prisma@5.1.1\node_modules\@prisma\client\runtime\library.js:122:6999)
    at Hr.handleAndLogRequestError (C:\Users\Muham\Documents\dev-jobs\api\node_modules\.pnpm\@prisma+client@5.1.1_prisma@5.1.1\node_modules\@prisma\client\runtime\library.js:122:6388)
    at Hr.request (C:\Users\Muham\Documents\dev-jobs\api\node_modules\.pnpm\@prisma+client@5.1.1_prisma@5.1.1\node_modules\@prisma\client\runtime\library.js:122:6108)
    at l (C:\Users\Muham\Documents\dev-jobs\api\node_modules\.pnpm\@prisma+client@5.1.1_prisma@5.1.1\node_modules\@prisma\client\runtime\library.js:126:10298)
    at JobService.applyToJob (C:\Users\Muham\Documents\dev-jobs\api\src\job\job.service.ts:36:21)
    at C:\Users\Muham\Documents\dev-jobs\api\node_modules\.pnpm\@nestjs+core@10.0.0_@nestjs+common@10.0.0_@nestjs+platform-express@10.0.0_reflect-metadata@0.1.13_rxjs@7.8.1\node_modules\@nestjs\core\router\router-execution-context.js:46:28)
    at C:\Users\Muham\Documents\dev-jobs\api\node_modules\.pnpm\@nestjs+core@10.0.0_@nestjs+common@10.0.0_@nestjs+platform-express@10.0.0_reflect-metadata@0.1.13_rxjs@7.8.1\node_modules\@nestjs\core\router\router-proxy.js:9:17

解决方案 #

正确代码实现 #

无需提前查询用户，直接通过connect关联已存在的用户ID，这是隐式多对多关系中添加关联的正确方式：

async applyToJob(id: string, accessor: Payload) {
  const job = await this.prisma.job.update({
    where: { id },
    data: {
      applicants: {
        connect: { id: accessor.id }
      }
    },
    // 可选：返回更新后的职位及申请者列表
    include: { applicants: true }
  });
  return job;
}

防止重复申请的优化 #

为避免用户重复申请同一职位，可先检查关联是否已存在：

async applyToJob(id: string, accessor: Payload) {
  // 检查用户是否已申请该职位
  const job = await this.prisma.job.findUnique({
    where: { id },
    select: { applicants: { where: { id: accessor.id } } }
  });

  if (job?.applicants.length) {
    throw new Error('该用户已申请此职位');
  }

  // 建立关联
  return this.prisma.job.update({
    where: { id },
    data: {
      applicants: { connect: { id: accessor.id } }
    },
    include: { applicants: true }
  });
}

错误原因解释 #

之前使用create操作是错误的：create会尝试创建一条新的User记录，但该用户的ID已存在于数据库中，因此触发了id字段的唯一约束冲突。

而connect操作的作用是将已存在的记录建立关联，Prisma会自动维护隐式多对多的中间表，同时更新User的appliedToJobs关联字段，且不会覆盖已有的申请者列表。

内容的提问来源于stack exchange，提问作者The Online Lyceum