P
Prisma2mo ago
testas

Help Needed with Nested CreateMany!

Hi everyone, I’m currently working on an API route to handle a POST request that does the following: 1. Accepts a single classroomId. 2. Retrieves all students in the classroom via memberships. 3. Creates an AssignmentSet for the classroom. 4. Iterates over the students to: • Create an Assignment for each student. • Create a Portfolio for each student. • Connect the Assignment to the AssignmentSet. I’m encountering issues with Prisma’s lack of support for nested CreateMany. Could anyone provide guidance or workarounds to achieve this functionality? Creating the records one by one is too slow when there are potentially hundreds of students to iterate over. Thanks for any suggestion/help!
7 Replies
testas
testas2mo ago
Here’s the minimal version of my Prisma schema:
generator client {
provider = "prisma-client-js"
}

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

model Classroom {
id String @id @default(cuid())
teacherId String
students ClassroomMembership[]
assignmentSets AssignmentSet[]
teacher User @relation(fields: [teacherId], references: [id], onDelete: Cascade)
}

model ClassroomMembership {
id String @id @default(cuid())
classroomId String
userId String
classroom Classroom @relation(fields: [classroomId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Assignment {
id String @id @default(cuid())
classroomId String
studentId String
assignmentSetId String
portfolios Portfolio[]
classroom Classroom @relation(fields: [classroomId], references: [id], onDelete: Cascade)
student User @relation(fields: [studentId], references: [id], onDelete: Cascade)
assignmentSet AssignmentSet @relation(fields: [assignmentSetId], references: [id], onDelete: Cascade)
}

model AssignmentSet {
id String @id @default(cuid())
classroomId String
classroom Classroom @relation(fields: [classroomId], references: [id], onDelete: Cascade)
assignments Assignment[]
}

model Portfolio {
id String @id @default(cuid())
assignmentId String
studentId String
assignment Assignment @relation(fields: [assignmentId], references: [id], onDelete: Cascade)
student User @relation(fields: [studentId], references: [id], onDelete: Cascade)
}

model User {
id String @id @default(cuid())
assignments Assignment[]
portfolios Portfolio[]
classroomMembership ClassroomMembership[]
}
generator client {
provider = "prisma-client-js"
}

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}

model Classroom {
id String @id @default(cuid())
teacherId String
students ClassroomMembership[]
assignmentSets AssignmentSet[]
teacher User @relation(fields: [teacherId], references: [id], onDelete: Cascade)
}

model ClassroomMembership {
id String @id @default(cuid())
classroomId String
userId String
classroom Classroom @relation(fields: [classroomId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Assignment {
id String @id @default(cuid())
classroomId String
studentId String
assignmentSetId String
portfolios Portfolio[]
classroom Classroom @relation(fields: [classroomId], references: [id], onDelete: Cascade)
student User @relation(fields: [studentId], references: [id], onDelete: Cascade)
assignmentSet AssignmentSet @relation(fields: [assignmentSetId], references: [id], onDelete: Cascade)
}

model AssignmentSet {
id String @id @default(cuid())
classroomId String
classroom Classroom @relation(fields: [classroomId], references: [id], onDelete: Cascade)
assignments Assignment[]
}

model Portfolio {
id String @id @default(cuid())
assignmentId String
studentId String
assignment Assignment @relation(fields: [assignmentId], references: [id], onDelete: Cascade)
student User @relation(fields: [studentId], references: [id], onDelete: Cascade)
}

model User {
id String @id @default(cuid())
assignments Assignment[]
portfolios Portfolio[]
classroomMembership ClassroomMembership[]
}
I had to separate it due to too much text limitation.
moosthuizen
moosthuizen2mo ago
Consider creating two bulk operations: - Create all the students' assignments - Create all the students' portfolios The flow of your code could then look something like this:
const students_in_classroom = await ClassroomMembership.findMany(...);

const new_assignment_set = await AssignmentSet.create(...);

const new_assignments = await Assignment.createMany({ data:
students_in_classroom .map((student) => {
... // classroom, student ID, etc.
assignmentSetId: new_assignment_set.id
})
})

await Portfolio.createMany( /* Map over new_assignments */ )

const students_in_classroom = await ClassroomMembership.findMany(...);

const new_assignment_set = await AssignmentSet.create(...);

const new_assignments = await Assignment.createMany({ data:
students_in_classroom .map((student) => {
... // classroom, student ID, etc.
assignmentSetId: new_assignment_set.id
})
})

await Portfolio.createMany( /* Map over new_assignments */ )

There will still be four DB calls, but better than N calls. Let me know if this helps. @testas
testas
testas2mo ago
Thanks for the suggestion. The cavieat with your approach is, createMany doesn't return the records. B\ut I re-read the docs and found this https://www.prisma.io/docs/orm/prisma-client/queries/crud#create-records-and-connect-or-create-related-records It seems like a more efficient way to go about it! It seems like a new thing, as it's only available at 5.14
CRUD (Reference) | Prisma Documentation
How to perform CRUD with Prisma Client.
jonfanz
jonfanz2mo ago
Yes, createManyAndReturn is new as of 5.14.0 🙂
testas
testas2mo ago
I'm concerned that returning tons of records can be very costly/inefficient. I don't see an option to select specific properties to return using createManyAndReturn, but maybe i'm missing it? is there a way to for example select to return only IDs?
jonfanz
jonfanz2mo ago
Ah, we should probably make that more clear. select is available: https://www.prisma.io/docs/orm/reference/prisma-client-reference#createmanyandreturn
Prisma Client API | Prisma Documentation
API reference documentation for Prisma Client.
jonfanz
jonfanz2mo ago
super pseudocode:
const newAssignmentIds = await prisma.assignment.createManyAndReturn({
select: { id: true },
data: [...]
})
const newAssignmentIds = await prisma.assignment.createManyAndReturn({
select: { id: true },
data: [...]
})