使用Prisma编写可变数量的数据库条目

huangapple
go评论129阅读模式
英文:

Writing a variable number of database entries with Prisma

问题

我正在开发一个应用程序，该应用程序为每个人分配每周的工作天数，并将他们的日程安排写入关系数据库中使用Prisma。实际上，伪代码如下所示：

  1. const createTutor = await prisma.tutor.create({
  2.     data: { 
  3.         tutorName: tutorName,
  4.         tutorPhone: +tutorPhone,
  5.         tutorRate: +tutorRate,
  6.         tutorEmail: tutorEmail,
  7.         availability: { **这是我不知道该怎么做的地方。理想情况下，我需要将可用的天数条目（包括开始时间和结束时间）以可变数量写入数据库，并将导师与其可用性条目以及可用性条目与导师连接起来。** }
  8.     }
  9. });

以下是这两个数据库条目的模式：

  1. model Tutor {
  2.     tutorName         String               @unique
  3.     tutorAvailability DailyAvailibility[]
  4.     tutorRate         Int
  5.     assignedStudents  TutoringAssignment[]
  6.     linkedUser        User?
  7.     completedSessions CompletedSession[]
  8.     tutorPhone        BigInt
  9.     tutorEmail        String
  10. }
  12. model DailyAvailibility {
  13.     id                    Int    @id @unique @default(autoincrement())
  14.     tutor                 Tutor  @relation(fields: [tutorName], references: [tutorName])
  15.     tutorName             String
  16.     dayOfWeek             Int //0表示星期日，6表示星期六
  17.     beginningAvailability Int
  18.     endingAvailability     Int
  19. }

我想正确地完成这个任务，目前我已经能够正确地写入所有条目，但只能将tutorName与其dailyAvailibilty条目关联起来，而不能反向关联。以下是我目前用于执行此操作的API代码（如果有助于理解我想要做什么）：

  1. async function createAvailability(availability:any, tutorName:any) {
  2.     for(let x=0; x<6; x++) {
  3.         if(availability[x] != null) {
  4.             let newAvailability = await prisma.dailyAvailibility.create({
  5.                 data: {
  6.                     dayOfWeek:x,
  7.                     beginningAvailability: +availability[x].beginningAvailability,
  8.                     endingAvailability: +availability[x].endingAvailability,
  9.                     tutor: {
  10.                         connect: {
  11.                             tutorName: tutorName
  12.                         }
  13.                     }
  14.                 }
  15.             });
  16.         }
  17.     }
  18. }

我希望以一种正确的方式一次性完成所有操作，使导师条目正确连接到其每日可用性，并且每日可用性正确连接到其导师。我还应该提到，这是一个PostgreSQL数据库。

谢谢您的帮助！

英文:

I am working on an app that takes an individual and assigns them a set number of days per week for them to work, then it writes their schedule to a relational database using Prisma. In practice this looks like the following psudocode:

  1.  const createTutor = await prisma.tutor.create({
  2.         data: { 
  3.             tutorName: tutorName,
  4.             tutorPhone: +tutorPhone,
  5.             tutorRate: +tutorRate,
  6.             tutorEmail: tutorEmail
  7.             availability: { **This is where I don&#39;t know what to do. Ideally I need to write a variable number of available day entries (that include a beginning time and an end time) to the database and connect both the tutor to their availability entries and the availability entries to the tutor.** }

Here is what my schema looks like for these two database entries:

  1. model Tutor {
  2.   tutorName         String               @unique
  3.   tutorAvailability DailyAvailibility[]
  4.   tutorRate         Int
  5.   assignedStudents  TutoringAssignment[]
  6.   linkedUser        User?
  7.   completedSessions CompletedSession[]
  8.   tutorPhone        BigInt
  9.   tutorEmail        String
  10. }
  12. model DailyAvailibility {
  13.   id                    Int    @id @unique @default(autoincrement())
  14.   tutor                 Tutor  @relation(fields: [tutorName], references: [tutorName])
  15.   tutorName             String
  16.   dayOfWeek             Int //0 being Sunday and 6 Saturday
  17.   beginningAvailability Int
  18.   endingAvailability     Int
  19. }

I'd love to know how to do this correctly, I technically have it writing all the correct entries but it only links the tutorName to their dailyAvailibilty entries, it does not link the opposite. Here's the code I have now for my API that does this (if it helps understand what I am trying to do)

  1. async function createAvailability(availability:any, tutorName:any) {
  2.     
  3.     for(let x=0; x&lt;6; x++) {
  4.         if(availability[x] != null) {
  5.           let newAvailability = await prisma.dailyAvailibility.create({
  6.             data: {
  7.                 dayOfWeek:x,
  8.                 beginningAvailability: +availability[x].beginningAvailability,
  9.                 endingAvailability: +availability[x].endingAvailability,
  10.                 tutor: {
  11.                     connect: {
  12.                         tutorName: tutorName
  13.                     }
  14.                 }
  15.             }
  16.           })
  17.         }
  18.     }
  19. }

What I want to do is write this all at once in such a way that the tutor entry is connected correctly to their dailyAvailabilities and the dailyAvailabilities all correctly are linked to their tutors. I should mention this is a postgresql database as well.

Thanks for any help!

答案1

得分: 0

你可以使用create字段或connectOrCreate来创建你的可用性，就像这样：

  1.   const tutor = await prisma.tutor.create({
  2.     data: {
  3.       tutorName: 'tutorName',
  4.       tutorPhone: 1,
  5.       tutorRate: 1,
  6.       tutorEmail: 'tutorEmail',
  7.       tutorAvailability: {
  8.         create: [
  9.           { beginningAvailability: 1, endingAvailability: 1, dayOfWeek: 1 },
  10.           { beginningAvailability: 2, endingAvailability: 2, dayOfWeek: 2 },
  11.         ],
  12.       },
  13.     },
  14.   });

然后，当你查询数据时，只需在查询时使用include来获取关联的另一侧：

  1.   const availabilityWithTutor = await prisma.dailyAvailibility.findFirst({
  2.     include: {
  3.       tutor: true,
  4.     },
  5.   });
  7.   // Tutor
  8.   availabilityWithTutor?.tutor
  9.   
  10.   const tutorWithAvailability = await prisma.tutor.findFirst({
  11.     include: {
  12.       tutorAvailability: true,
  13.     },
  14.   });
  15.  
  16.   // Array<DailyAvailibility>
  17.   tutorWithAvailability?.tutorAvailability
英文:

You can use create field or connectOrCreate to create your availabilities right way, like that:

  1.   const tutor = await prisma.tutor.create({
  2.     data: {
  3.       tutorName: &#39;tutorName&#39;,
  4.       tutorPhone: 1,
  5.       tutorRate: 1,
  6.       tutorEmail: &#39;tutorEmail&#39;,
  7.       tutorAvailability: {
  8.         create: [
  9.           { beginningAvailability: 1, endingAvailability: 1, dayOfWeek: 1 },
  10.           { beginningAvailability: 2, endingAvailability: 2, dayOfWeek: 2 },
  11.         ],
  12.       },
  13.     },
  14.   });

And then to get either side of relation you just need to include it when you query for data:

  2.   const availabilityWithTutor = await prisma.dailyAvailibility.findFirst({
  3.     include: {
  4.       tutor: true,
  5.     },
  6.   });
  8.   // Tutor
  9.   availabilityWithTutor?.tutor
  10.   
  11.   const tutorWithAvailability = await prisma.tutor.findFirst({
  12.     include: {
  13.       tutorAvailability: true,
  14.     },
  15.   });
  16.  
  17.   // Array&lt;DailyAvailibility&gt;
  18.   tutorWithAvailability?.tutorAvailability

huangapple
  • 本文由 发表于 2023年8月9日 01:13:48
  • 转载请务必保留本文链接：https://go.coder-hub.com/76861834.html
  • javascript
  • postgresql
  • prisma
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定