排查关系问题

对模式进行建模有时会带来一些意想不到的结果。本节旨在涵盖其中最突出的内容。

¥Modelling your schema can sometimes offer up some unexpected results. This section aims to cover the most prominent of those.

如果关系字段的顺序发生变化，隐式多对多自关系将返回不正确的数据

¥Implicit many-to-many self-relations return incorrect data if order of relation fields change

问题

¥Problem

在下面的隐式多对多自关系中，a_eats(1)和 b_eatenBy(2)中关系字段的字典顺序：

¥In the following implicit many-to-many self-relation, the lexicographic order of relation fields in a_eats (1) and b_eatenBy (2):

model Animal {
  id        Int      @id @default(autoincrement())
  name      String
  a_eats    Animal[] @relation(name: "FoodChain")
  b_eatenBy Animal[] @relation(name: "FoodChain")
}

SQL 中生成的关系表如下所示，其中 A 代表猎物 (a_eats)，B 代表捕食者 (b_eatenBy)：

¥The resulting relation table in SQL looks as follows, where A represents prey (a_eats) and B represents predators (b_eatenBy):

A	B
8（浮游生物）	7（三文鱼）
7（三文鱼）	9（熊）

以下查询返回鲑鱼的猎物和捕食者：

¥The following query returns a salmon's prey and predators:

const getAnimals = await prisma.animal.findMany({
  where: {
    name: 'Salmon',
  },
  include: {
    b_eats: true,
    a_eatenBy: true,
  },
})

Show query results

{
  "id": 7,
  "name": "Salmon",
  "b_eats": [
    {
      "id": 8,
      "name": "Plankton"
    }
  ],
  "a_eatenBy": [
    {
      "id": 9,
      "name": "Bear"
    }
  ]
}

现在更改关系字段的顺序：

¥Now change the order of the relation fields:

model Animal {
  id        Int      @id @default(autoincrement())
  name      String
  b_eats    Animal[] @relation(name: "FoodChain")
  a_eatenBy Animal[] @relation(name: "FoodChain")
}

迁移你的更改并重新生成 Prisma 客户端。当你使用更新的字段名称运行相同的查询时，Prisma 客户端返回不正确的数据（鲑鱼现在吃熊并被浮游生物吃掉）：

¥Migrate your changes and re-generate Prisma Client. When you run the same query with the updated field names, Prisma Client returns incorrect data (salmon now eats bears and gets eaten by plankton):

const getAnimals = await prisma.animal.findMany({
  where: {
    name: 'Salmon',
  },
  include: {
    b_eats: true,
    a_eatenBy: true,
  },
})

Show query results

{
  "id": 1,
  "name": "Salmon",
  "b_eats": [
    {
      "id": 3,
      "name": "Bear"
    }
  ],
  "a_eatenBy": [
    {
      "id": 2,
      "name": "Plankton"
    }
  ]
}

尽管 Prisma 模式中关系字段的字典顺序发生了变化，但数据库中的 A 和 B 列没有变化（它们没有被重命名，数据也没有移动）。因此，A 现在代表捕食者 (a_eatenBy)，B 代表猎物 (b_eats)：

¥Although the lexicographic order of the relation fields in the Prisma schema changed, columns A and B in the database did not change (they were not renamed and data was not moved). Therefore, A now represents predators (a_eatenBy) and B represents prey (b_eats):

A	B
8（浮游生物）	7（三文鱼）
7（三文鱼）	9（熊）

解决方案

¥Solution

如果你重命名隐式多对多自关系中的关系字段，请确保保持字段的字母顺序 - 例如，添加前缀 a_ 和 b_。

¥If you rename relation fields in an implicit many-to-many self-relations, make sure that you maintain the alphabetic order of the fields - for example, by prefixing with a_ and b_.

如何使用具有多对多关系的关系表

¥How to use a relation table with a many-to-many relationship

有几种方法可以定义 m-n 关系，隐式或显式。隐式意味着让 Prisma ORM 在底层处理关系表（JOIN 表），你所要做的就是为每个模型上的非标量类型定义一个数组/列表，请参见 隐式多对多关系。

¥There are a couple of ways to define an m-n relationship, implicitly or explicitly. Implicitly means letting Prisma ORM handle the relation table (JOIN table) under the hood, all you have to do is define an array/list for the non scalar types on each model, see implicit many-to-many relations.

你可能会遇到麻烦的是创建 显式的 m-n 关系 时，即自己创建和处理关系表。可以忽略的是，Prisma ORM 要求关系的双方都存在。

¥Where you might run into trouble is when creating an explicit m-n relationship, that is, to create and handle the relation table yourself. It can be overlooked that Prisma ORM requires both sides of the relation to be present.

以下面的例子为例，这里创建了一个关系表作为 Post 和 Category 表之间的 JOIN。然而，这不起作用，因为关系表 (PostCategories) 必须分别与其他两个模型形成一对多关系。

¥Take the following example, here a relation table is created to act as the JOIN between the Post and Category tables. This will not work however as the relation table (PostCategories) must form a 1-to-many relationship with the other two models respectively.

Post 至 PostCategories 和 Category 至 PostCategories 模型缺少反向关系字段。

¥The back relation fields are missing from the Post to PostCategories and Category to PostCategories models.

// This example schema shows how NOT to define an explicit m-n relation

model Post {
  id             Int              @id @default(autoincrement())
  title          String
  categories     Category[] // This should refer to PostCategories
}

model PostCategories {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int
  @@id([postId, categoryId])
}

model Category {
  id             Int              @id @default(autoincrement())
  name           String
  posts          Post[] // This should refer to PostCategories
}

为了解决这个问题，Post 模型需要使用关系表 PostCategories 定义多个关系字段。这同样适用于 Category 型号。

¥To fix this the Post model needs to have a many relation field defined with the relation table PostCategories. The same applies to the Category model.

这是因为关系模型与其连接的其他两个模型形成了一对多的关系。

¥This is because the relation model forms a 1-to-many relationship with the other two models its joining.

model Post {
  id             Int              @id @default(autoincrement())
  title          String
  categories     Category[]
  postCategories PostCategories[]
}

model PostCategories {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int

  @@id([postId, categoryId])
}

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

使用具有多对多关系的 @relation 属性

¥Using the @relation attribute with a many-to-many relationship

在构建隐式多对多关系时，将 @relation("Post") 注释添加到模型上的关系字段似乎是合乎逻辑的。

¥It might seem logical to add a @relation("Post") annotation to a relation field on your model when composing an implicit many-to-many relationship.

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[] @relation("Category")
  Category   Category?  @relation("Post", fields: [categoryId], references: [id])
  categoryId Int?
}

model Category {
  id     Int    @id @default(autoincrement())
  name   String
  posts  Post[] @relation("Post")
  Post   Post?  @relation("Category", fields: [postId], references: [id])
  postId Int?
}

然而，这告诉 Prisma ORM 期望有两个独立的一对多关系。有关使用 @relation 属性的更多信息，请参阅 消除关系歧义。

¥This however tells Prisma ORM to expect two separate one-to-many relationships. See disambiguating relations for more information on using the @relation attribute.

以下示例是定义隐式多对多关系的正确方法。

¥The following example is the correct way to define an implicit many-to-many relationship.

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[] @relation("Category")
  categories Category[]
}

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

@relation 注释也可用于在隐式多对多关系上创建的 命名底层关系表。

¥The @relation annotation can also be used to name the underlying relation table created on a implicit many-to-many relationship.

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[] @relation("CategoryPostRelation")
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[] @relation("CategoryPostRelation")
}

在具有强制主键的数据库中使用 m-n 关系

¥Using m-n relations in databases with enforced primary keys

问题

¥Problem

一些云提供商强制所有表中都存在主键。但是，Prisma ORM（通过 @relation 表示）使用隐式语法为多对多关系创建的任何关系表（JOIN 表）都没有主键。

¥Some cloud providers enforce the existence of primary keys in all tables. However, any relation tables (JOIN tables) created by Prisma ORM (expressed via @relation) for many-to-many relations using implicit syntax do not have primary keys.

解决方案

¥Solution

你需要使用 显式关系语法，手动创建联接模型，并验证该联接模型是否具有主键。

¥You need to use explicit relation syntax, manually create the join model, and verify that this join model has a primary key.