将隐式多对多关系转换为显式多对多关系

问题

¥Problem

多对多关系是关系数据库的一个重要方面，允许一个表中的多个记录与另一个表中的多个记录相关。Prisma 提供了两种方法来建模多对多关系：implicit 和 explicit。

¥Many-to-many relationships are an important aspect of relational databases, allowing multiple records in one table to be related to multiple records in another table. Prisma provides two approaches to model many-to-many relationships: implicit and explicit.

用户有时会遇到需要从模型之间的隐式多对多关系转换为显式关系的情况。将隐式关系转换为显式关系允许你更好地控制关系并存储特定于关系的其他数据，例如时间戳或任何其他字段。本指南提供了有关如何进行该转换的分步演练。

¥Users sometimes encounter situations where they need to transition from implicit many-to-many relationships between models to explicit ones. Converting an implicit relation to explicit allows you to have more control over the relationship and store additional data specific to the relation, such as a timestamp or any other fields. This guide provides a step-by-step walkthrough on how to make that conversion.

解决方案

¥Solution

这将指导你完成在 Prisma 中将隐式多对多关系转换为显式关系的过程：

¥This will guide you through the process of converting an implicit many-to-many relation to an explicit one in Prisma:

考虑通过 posts 和 author 字段具有隐式多对多关系的这些模型：

¥Consider these models with an implicit many-to-many relationship via the posts and author fields:

model User {
  id        Int       @id @default(autoincrement())
  name      String
  posts     Post[]
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  authors   User[]
}

在上述模型中，User 可以有多个帖子，Post 可以有多个作者。

¥In the above models, a User can have multiple posts and a Post can have multiple authors.

要将隐式关系转换为显式关系，我们需要创建一个关系表。关系表将包含引用多对多关系中涉及的两个表的外键。在我们的示例中，我们将创建一个名为 UserPost 的新模型。我们更新的 schema.prisma 文件如下所示：

¥To convert the implicit relation to an explicit one, we need to create a relation table. The relation table will contain foreign keys referencing both tables involved in the many-to-many relation. In our example, we'll create a new model called UserPost. Our updated schema.prisma file would look like this:

model User {
  id        Int        @id @default(autoincrement())
  name      String
  posts     Post[]
  userPosts UserPost[]
}

model Post {
  id        Int        @id @default(autoincrement())
  title     String
  authors   User[]
  userPosts UserPost[]
}

model UserPost {
  id        Int       @id @default(autoincrement())
  userId    Int
  postId    Int
  user      User      @relation(fields: [userId], references: [id])
  post      Post      @relation(fields: [postId], references: [id])
  createdAt DateTime  @default(now())

  @@unique([userId, postId])
}

如果你使用的是 Prisma Migrate，则可以调用此命令：

¥If you are using Prisma Migrate, then you can invoke this command:

npx prisma migrate dev --name "added explicit relation"

迁移将创建 UserPost 表并创建 User 和 Post 模型与 UserPost 模型的一对多关系。

¥The migration will create the UserPost table and create one-to-many relation of User and Post model with UserPost model.

将现有数据从隐式关系表迁移到新创建的关系表

¥Migrating Existing data from implicit relation table to newly created relation table

要将现有数据从隐式关系表迁移到新的显式关系表，你需要编写自定义迁移脚本。你可以使用 Prisma Client 与数据库交互，从隐式关系表中读取数据，并将其写入新的关系表。

¥To migrate the existing data from the implicit relation table to the new explicit relation table, you'll need to write a custom migration script. You can use the Prisma Client to interact with the database, read data from the implicit relation table, and write it to the new relation table.

考虑到上述 User 和 Post 模型，这里有一个可用于迁移数据的示例脚本。

¥Considering the above User and Post models, here’s an example script you can use to migrate data.

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

const prisma = new PrismaClient();

// A `main` function so that you can use async/await
async function main() {
  try {
    // Fetch all users with their related posts
    const users = await prisma.user.findMany({
      include: { posts: true },
    });

    // Iterate over users and their posts, then insert records into the UserPost table
    for (const user of users) {
      for (const post of user.posts) {
        await prisma.userPost.create({
          data: {
            userId: user.id,
            postId: post.id,
          },
        });
      }
    }

    console.log("Data migration completed.");
  } catch (e) {
    console.error(e);
  }
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

将数据迁移到关系表后，你可以删除隐式关系列（User 模型中的 posts 和 Post 模型中的 author），如下所示：

¥Once the data is migrated to the relation table, you can remove the implicit relation columns ( posts in User model and author in Post model ) as shown below:

model User {
  id        Int        @id @default(autoincrement())
  name      String
  posts     Post[]
  userPosts UserPost[]
}

model Post {
  id        Int        @id @default(autoincrement())
  title     String
  authors   User[]
  userPosts UserPost[]
}

在模式文件中进行更改后，你可以调用此命令：

¥After making the change in schema file, you can invoke this command:

npx prisma migrate dev --name "removed implicit relation"

运行上述命令将删除隐式表 _PostToUser

¥Running the above command would drop the implicit table _PostToUser

你现在已成功将 Prisma 中的隐式多对多关系转换为显式多对多关系。

¥You've now successfully converted an implicit many-to-many relation to an explicit one in Prisma.

问题​

解决方案​

将现有数据从隐式关系表迁移到新创建的关系表​

问题

解决方案

将现有数据从隐式关系表迁移到新创建的关系表