Drizzle Team•14mo ago

Help migrating a nested Prisma update

Been trying to set this up without going through the raw sql route for some time but cant seem to wrap my head around it. Basically the structure here is I have a Result that can optionally have an alert or a note. If an alert or note already exist then update them. Any help here is appreciated it! thank you

 const updatedResult: Result = await prisma.result
    .update({
      where: {
        id: resultId,
      },
      data: {
        reviewedBy: resultData.reviewedBy,
        note: {
          upsert: {
            create: {
              reason: resultData.reason,
              patientId: resultData.patientId,
              detail: resultData.detail,
            },
            update: {
              reason: resultData.reason,
              detail: resultData.detail,
            },
          },
        },
        alert: alertId
          ? {
              update: {
                acknowledged: true,
              },
            }
          : undefined,
      },
      include: {
        note: true,
        alert: true,
      },
    })

 const updatedResult: Result = await prisma.result
    .update({
      where: {
        id: resultId,
      },
      data: {
        reviewedBy: resultData.reviewedBy,
        note: {
          upsert: {
            create: {
              reason: resultData.reason,
              patientId: resultData.patientId,
              detail: resultData.detail,
            },
            update: {
              reason: resultData.reason,
              detail: resultData.detail,
            },
          },
        },
        alert: alertId
          ? {
              update: {
                acknowledged: true,
              },
            }
          : undefined,
      },
      include: {
        note: true,
        alert: true,
      },
    })

7 Replies

Angelelz•14mo ago

Does note have a result_id foreign key? Result and note have a one to one relation?

imoby•14mo ago

Result has a noteId foreign key It’s an optional relation So basically each result can optionally have one note associated with it. But a note does not have to be associated with an a result. It can be used for non result scenarios

Angelelz•14mo ago

Well, Prisma hides away all the real necessary steps to makes this work so much, that you end up learning prisma, not sql You can't write to 3 different tables at the same time in sql, so you'll need to do the individual queries inside a transaction

db.transaction(async (tx) => {
  await tx.update(result).set({
    reviewedBy: resultData.reviewedBy,
  }).where(eq(result.id, resultId))
  
  await tx.insert(note).values({
    reason: resultData.reason,
    patientId: resultData.patientId,
    detail: resultData.detail
  }).onConflictDoUpdate({
    target: note.patientId,
    set: {
      reason: resultData.reason,
      detail: resultData.detail
    }
  });

  if (alertId) {
    tx.update(alert).set({
      acknowledge: true
    })
  }
})

db.transaction(async (tx) => {
  await tx.update(result).set({
    reviewedBy: resultData.reviewedBy,
  }).where(eq(result.id, resultId))
  
  await tx.insert(note).values({
    reason: resultData.reason,
    patientId: resultData.patientId,
    detail: resultData.detail
  }).onConflictDoUpdate({
    target: note.patientId,
    set: {
      reason: resultData.reason,
      detail: resultData.detail
    }
  });

  if (alertId) {
    tx.update(alert).set({
      acknowledge: true
    })
  }
})

That will do the trick but it won't return back anything. If you want the data, you can either use the returning() method on each query and then construct your object, or do a SELECT and the end and return it inside the transaction

imoby•14mo ago

logically that seems to break it down well but I get a following error when I run it I'm guessing it has to do with the constraint: "there is no unique or exclusion constraint matching the ON CONFLICT specification"

Angelelz•14mo ago

Yeah, the parentId needs to be a unique index in note. Is it not?

imoby•14mo ago

We only have the unique index in the result not the note. Not sure if that needs to be corrected

Angelelz•14mo ago

It's up to your db architecture. But in that case, you might just need to run a select just to check if the note with that patientId exists, update if it does, insert if it doesn't

Gaming

Programming

Help migrating a nested Prisma update