建模和查询多对多关系

问题

¥Problem

在关系数据库中建模和查询多对多关系可能具有挑战性。本文展示了如何使用 Prisma ORM 实现这一点的两个示例。第一个示例使用 implicit，第二个示例使用 explicit 多对多关系。

¥Modeling and querying many-to-many relations in relational databases can be challenging. This article shows two examples how this can be approached with Prisma ORM. The first example uses an implicit and the second one uses an explicit many-to-many relation.

解决方案

¥Solution

隐含关系

¥Implicit relations

这是一种多对多关系，Prisma ORM 在内部处理关系表。隐式多对多关系的基本示例如下所示：

¥This is a type of many-to-many relation where Prisma ORM handles the relation table internally. A basic example for an implicit many-to-many relation would look like this:

model Post {
  id    Int    @id @default(autoincrement())
  title String
  tags  Tag[]
}

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

要创建帖子及其标签，可以使用 Prisma 客户端编写以下内容：

¥To create a post and its tags, one can write this with Prisma Client:

await prisma.post.create({
  data: {
    title: 'Types of relations',
    tags: { create: [{ name: 'dev' }, { name: 'prisma' }] },
  },
})

在上面的示例中，我们可以直接查询帖子及其标签，如下所示：

¥In the above example, we can directly query for posts along with their tags as follows:

await prisma.post.findMany({
  include: { tags: true },
})

获得的响应将是：

¥And the response obtained would be:

[
  {
    "id": 1,
    "title": "Types of relations",
    "tags": [
      {
        "id": 1,
        "name": "dev"
      },
      {
        "id": 2,
        "name": "prisma"
      }
    ]
  }
]

另一个用例是如果你想要添加新标签以及将现有标签连接到帖子。例如，用户为其帖子创建了新标签，并且还选择了要添加的现有标签。在这种情况下，我们可以通过以下方式执行此操作：

¥Another use case for this is if you want to add new tags as well as connect to existing tags to a post. An example for this is where a user has created new tags for their post and has also selected existing tags to be added as well. In this case, we can perform this in the following way:

await prisma.post.update({
  where: { id: 1 },
  data: {
    title: 'Prisma is awesome!',
    tags: { set: [{ id: 1 }, { id: 2 }], create: { name: 'typescript' } },
  },
})

显式关系

¥Explicit relations

如果你需要在关系表中存储额外的字段，或者如果你是 introspecting 已经具有多对多关系设置的现有数据库，则通常需要创建显式关系。这与上面使用的模式相同，但具有显式关系表：

¥Explicit relations mostly need to be created in cases where you need to store extra fields in the relation table or if you're introspecting an existing database that already has many-to-many relations setup. This is the same schema used above but with an explicit relation table:

model Post {
  id    Int        @id @default(autoincrement())
  title String
  tags  PostTags[]
}

model PostTags {
  id     Int   @id @default(autoincrement())
  post   Post? @relation(fields: [postId], references: [id])
  tag    Tag?  @relation(fields: [tagId], references: [id])
  postId Int?
  tagId  Int?

  @@index([postId, tagId])
}

model Tag {
  id    Int        @id @default(autoincrement())
  name  String     @unique
  posts PostTags[]
}

向帖子添加标签将创建关系表 (PostTags) 以及标签表 (Tag)：

¥Adding tags to a post would be a create into the relation table (PostTags) as well as into the tags table (Tag):

await prisma.post.create({
  data: {
    title: 'Types of relations',
    tags: {
      create: [
        { tag: { create: { name: 'dev' } } },
        { tag: { create: { name: 'prisma' } } },
      ],
    },
  },
})

另外，查询帖子及其标签将需要额外的 include，如下所示：

¥Also querying for posts along with their tags would require an extra include as follows:

await prisma.post.findMany({
  include: { tags: { include: { tag: true } } },
})

这将提供以下输出：

¥This will provide the following output:

[
  {
    "id": 1,
    "title": "Types of relations",
    "tags": [
      {
        "id": 1,
        "postId": 1,
        "tagId": 1,
        "tag": {
          "id": 1,
          "name": "prisma"
        }
      },
      {
        "id": 2,
        "postId": 1,
        "tagId": 2,
        "tag": {
          "id": 2,
          "name": "dev"
        }
      }
    ]
  }
]

有时，在 UI 中显示关系表的数据并不理想。在这种情况下，最好在服务器本身获取数据并将响应发送到前端后映射数据。

¥Sometimes, it's not ideal to show the data for the relation table in your UI. In this case, it's best to map the data after fetching it on the server itself and sending that response to the frontend.

const result = posts.map((post) => {
  return { ...post, tags: post.tags.map((tag) => tag.tag) }
})

这将提供类似于你通过隐式关系收到的输出。

¥This will provide an output similar to the one you received with implicit relations.

[
  {
    "id": 1,
    "title": "Types of relations",
    "tags": [
      {
        "id": 1,
        "name": "prisma"
      },
      {
        "id": 2,
        "name": "dev"
      }
    ]
  }
]

本文展示了如何实现隐式和显式多对多关系并使用 Prisma 客户端对其进行查询。

¥This article showed how you can implement implicit and explicit many-to-many relations and query them using Prisma Client.

问题​

解决方案​

隐含关系​

显式关系​

问题

解决方案

隐含关系

显式关系