Prisma可以从关系表创建一对多关系吗

Question

我正在使用Prisma和MySQL创建一个锻炼应用程序。

我在用户和指定程序之间有一种多对多的关系，如"ProgramEnrollment"模型所示。我的问题是，我希望用户记录他们的个人锻炼设置。

我的想法是创建一个新模型"LoggedWorkoutSet"，然后通过一对多关系将其连接到模型"ProgramEnrollment"，这意味着一个"ProgramEnollment"可以有多个"LoggedWorkoutSet"。这似乎对我不起作用，问题是在试图定义这里的关系时：

program   ProgramEnrollment @relation(fields: [programId], references: [id])
programId Int // relation scalar field  (used in the `@relation` attribute above)

我得到的错误是"验证错误：参数references必须仅引用相关模型ProgramEnrollment中的现有字段。"。相关模型中不存在以下字段：id‘

我不知道该怎么办，任何帮助都将不胜感激。

model ProgramEnrollment {
program                 Program     @relation(fields: [programId], references: [id])
programId               Int // relation scalar field (used in the `@relation` attribute above)
user                    User @relation(fields: [userId], references: [id])
userId                  Int // relation scalar field (used in the `@relation` attribute above)
assignedAt              DateTime @default(now())
loggedWorkoutSet        LoggedWorkoutSet[]
@@id([programId, userId])
}

model LoggedWorkoutSet {
id              Int  @id @default(autoincrement())
reps            Int
weight          Int
completed       Boolean     @default(false)
author   ProgramEnrollment @relation(fields: [programId], references: [id])
programId Int // relation scalar field  (used in the `@relation` attribute above)
}

Answer 1

CREATE TABLE Users (
user_id ...,
...
PRIMARY KEY (user_id)
) ENGINE=InnoDB;
CREATE TABLE Programs (
pgm_id ...,
part_of_body ... -- legs/shoulders/...
...
PRIMARY KEY (pgm_id)
) ENGINE=InnoDB;
-- Many-to-many, with stats:
CREATE TABLE UserPgm (
user_id ...,
pgm_id ...,
latest_date DATE ...,
reps SMALLINT UNSIGNED NOT NULL,
weight SMALLINT UNSIGNED NOT NULL,  -- in the normal units for this device
PRIMARY KEY(user_id, pgm_id),
INDEX(pgm_id, user_id)
) ENGINE=InnoDB;

使用所有3个表的查询：

-- The latest workout for a user:
SELECT u.name,
up.latest_date,
up.reps,
p.device_name
FROM Users    AS u
JOIN UserPgm  AS up  USING(user_id)
JOIN Programs AS p   USING(pgm_id)
WHERE user_id = 123