I am using Prisma and Express.js to make requests to my MySQL database table.
I have one-to-many relationship between my Contest-Round tables and am trying to write a query which would allow me to differently update rounds for a given contest. Given the schema:
model Contest {
id Int @id @default(autoincrement())
name String @unique
rounds Round[]
... other fields
}
model Round {
id Int @id @default(autoincrement())
name String
contestId Int
contest Contest @relation(fields: [contestId], references: [id], onDelete: Cascade)
... other fields
}
What I want to achieve is to update the contest from
{
id: 100,
name: 'contest1',
rounds: [
{
id: 1,
name: 'round1',
contestId: 100,
},
{
id: 2,
name: 'round2',
contestId: 100,
}
]
to for example
{
id: 100,
name: 'contest1',
rounds: [
{
id: 1,
name: 'round1Updated',
contestId: 100,
},
{
id: 2,
name: 'round2UpdatedDifferently',
contestId: 100,
}
]
where the round names I get from the form value from HTML.
I haven't found any examples on updating different nested entities, so I'm expecting it to be something like this:
var updated = await prisma.contest.update({
where: {
id: 100
},
data: {
name: data.name,
rounds: {
update: {
where: {
id: { in: [1, 2] },
},
data: {
name: ['round1Updated', 'round2UpdatedDifferently']
},
},
}
},
include: {
rounds: true,
}
});
Any ideas or clues would be appreciated.
Will something like this work?
const updated = await prisma.contest.update({
where: { id: 100 },
data: {
name: data.name,
rounds: {
deleteMany: { id: { in: [1, 2] } }, // Delete existing records first
createMany: { // Update by creating new records
data: [
{ id: 1, name: "round1Updated" },
{ id: 2, name: "round2UpdatedDifferently" },
]
}
}
},
include: { rounds: true }
});
From form data
const updated = await prisma.contest.update({
where: { id: data.id },
data: {
name: data.name,
rounds: {
deleteMany: { id: { in: data.rounds.map(({ id }) => id) } },
createMany: { data: rounds }
}
},
include: { rounds: true }
});
Note that the ordering of operations do matter:
https://github.com/prisma/prisma/discussions/6263
const updated = await prisma.contest.update({
where: {id: data.id},
data: {
name: data.name,
rounds: {
deleteMany: {},
createMany: {data: rounds},
}
},
include: {rounds: true},
});