如何在Prisma中更新多对多关系？好友请求功能报错修复

问题背景 #

开发聊天应用的好友功能，核心需求：

• 用户可发送/接收好友请求
• 接受请求：双方friends数组互加对方，同时移除请求记录（发起方的sentFriendRequests、接收方的receivedFriendRequests）
• 拒绝请求：仅移除请求记录，friends数组无变化

使用当前Prisma Schema编写接受请求的API时，触发"找不到要关联的记录"报错。

当前Prisma Schema #

model User {
id            String    @id @default(cuid())
username      String?   @unique
password      String?
email         String?   @unique
emailVerified DateTime?
image         String?
accounts      Account[]
sessions      Session[]

sentFriendRequests     Friend[] @relation("SentFriendRequests")
receivedFriendRequests Friend[] @relation("ReceivedFriendRequests")

friends Friend[]
}

model Friend {
id     String  @id @default(cuid())
User   User?   @relation(fields: [userId], references: [id])
userId String?

requester   User?   @relation("SentFriendRequests", fields: [requesterId], references: [id])
receiver    User?   @relation("ReceivedFriendRequests", fields: [receiverId], references: [id])
requesterId String?
receiverId  String?

@@unique([requesterId, receiverId])
}

错误的API代码 #

import { db } from "@/lib/db";
import { FriendRequest } from "@/types/chatTypes";
import { NextRequest, NextResponse } from "next/server";
import { ZodError } from "zod";

export async function POST(req: NextRequest) {
  const data = await req.json();

  try {
    // 验证用户输入
    const validatedData = FriendRequest.parse(data);
    const { receiverId, requesterId } = validatedData;

    // 检查用户是否存在
    const requester = await db.user.findUnique({
      where: { id: requesterId },
    });
    const receiver = await db.user.findUnique({
      where: { id: receiverId },
    });

    if (!requester || !receiver) {
      return NextResponse.json({ message: "User not Found" }, { status: 404 });
    }

    await db.$transaction([
      // 互相添加到friends数组
      db.user.update({
        where: { id: receiverId },
        data: { friends: { connect: { id: requesterId } } },
      }),
      db.user.update({
        where: { id: requesterId },
        data: { friends: { connect: { id: receiverId } } },
      }),
      // 删除请求记录
      db.friend.delete({
        where: {
          requesterId_receiverId: {
            requesterId: requesterId,
            receiverId: receiverId,
          },
        },
      }),
    ]);

    return NextResponse.json(
      { message: "Friend Request Accepted Successfully" },
      { status: 201 }
    );
  } catch (error) {
    // Zod验证错误处理
    if (error instanceof ZodError) {
      return NextResponse.json(
        { message: error.flatten().fieldErrors },
        { status: 400 }
      );
    }
    return NextResponse.json(
      { message: `Something bad happened, Try again : ${error}` },
      { status: 400 }
    );
  }
}

报错信息 #

{
"message": "Something bad happened, Try again : PrismaClientKnownRequestError:\nInvalid `prisma.user.update()` invocation:\n\n\nThe required connected records were not found. Expected 1 records to be connected after connect operation on one-to-many relation 'FriendToUser', found 0."
}

问题根源 #

1. Schema设计缺陷：当前User与Friend的friends关联是一对多关系，但好友关系本质是多对多，这种设计会导致数据冗余且关联逻辑混乱。
2. API操作错误：更新friends数组时，使用connect: {id: requesterId}试图通过用户ID关联Friend记录，但Friend的id是自动生成的CUID，并非用户ID，因此找不到对应记录触发报错。

解决方案 #

方案一：优化Prisma Schema（推荐） #

重新设计Schema，用独立模型区分好友请求和好友关系，好友关系采用多对多关联：

model User {
  id            String           @id @default(cuid())
  username      String?          @unique
  password      String?
  email         String?          @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]

  // 好友请求关联
  sentFriendRequests     FriendRequest[] @relation("SentRequests")
  receivedFriendRequests FriendRequest[] @relation("ReceivedRequests")
  
  // 多对多好友关系
  friends                User[]          @relation("Friendship", references: [id])
}

model FriendRequest {
  id           String   @id @default(cuid())
  requester    User     @relation("SentRequests", fields: [requesterId], references: [id])
  requesterId  String
  receiver     User     @relation("ReceivedRequests", fields: [receiverId], references: [id])
  receiverId   String
  status       String   @default("pending") // 状态：pending/accepted/rejected
  @@unique([requesterId, receiverId])
}

适配新Schema的接受请求API只需：

1. 将FriendRequest的status更新为accepted
2. 调用connect将双方添加到彼此的friends数组

方案二：修改API代码适配原有Schema #

如果暂时不想调整Schema，需修改操作逻辑：删除原请求记录后，创建新的Friend记录表示双向好友关系，而非直接关联用户ID：

import { db } from "@/lib/db";
import { FriendRequest } from "@/types/chatTypes";
import { NextRequest, NextResponse } from "next/server";
import { ZodError } from "zod";

export async function POST(req: NextRequest) {
  const data = await req.json();

  try {
    const validatedData = FriendRequest.parse(data);
    const { receiverId, requesterId } = validatedData;

    const requester = await db.user.findUnique({ where: { id: requesterId } });
    const receiver = await db.user.findUnique({ where: { id: receiverId } });

    if (!requester || !receiver) {
      return NextResponse.json({ message: "User not Found" }, { status: 404 });
    }

    // 先删除原请求记录
    await db.friend.delete({
      where: {
        requesterId_receiverId: { requesterId, receiverId },
      },
    });

    // 创建双向好友记录
    const friendRecord1 = await db.friend.create({ data: { userId: receiverId } });
    const friendRecord2 = await db.friend.create({ data: { userId: requesterId } });

    // 关联到双方的friends数组
    await db.$transaction([
      db.user.update({
        where: { id: receiverId },
        data: { friends: { connect: { id: friendRecord1.id } } },
      }),
      db.user.update({
        where: { id: requesterId },
        data: { friends: { connect: { id: friendRecord2.id } } },
      }),
    ]);

    return NextResponse.json(
      { message: "Friend Request Accepted Successfully" },
      { status: 201 }
    );
  } catch (error) {
    if (error instanceof ZodError) {
      return NextResponse.json(
        { message: error.flatten().fieldErrors },
        { status: 400 }
      );
    }
    return NextResponse.json(
      { message: `Something bad happened, Try again : ${error}` },
      { status: 400 }
    );
  }
}

方案三：单模型双状态（折中） #

给原Friend模型添加status字段，用同一张表同时管理请求和好友关系，避免删除记录：

model User {
id            String    @id @default(cuid())
username      String?   @unique
password      String?
email         String?   @unique
emailVerified DateTime?
image         String?
accounts      Account[]
sessions      Session[]

sentFriendRequests     Friend[] @relation("SentFriendRequests")
receivedFriendRequests Friend[] @relation("ReceivedFriendRequests")

friends Friend[] @relation("Friendship")
}

model Friend {
id     String  @id @default(cuid())
// 好友关系关联
friendUser   User?   @relation("Friendship", fields: [friendUserId], references: [id])
friendUserId String?

// 请求关系关联
requester   User?   @relation("SentFriendRequests", fields: [requesterId], references: [id])
receiver    User?   @relation("ReceivedFriendRequests", fields: [receiverId], references: [id])
requesterId String?
receiverId  String?
status      String  @default("pending") // pending/accepted/rejected

@@unique([requesterId, receiverId])
}

此时接受请求只需更新status为accepted，并将记录关联到双方的friends数组即可。

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