Prisma delete many to many relationship with Composite Key

user_13122517 0
Viewed 4415

I have this schema here:

model label {
  title         String          @id @db.VarChar(16)
  color         String          @db.VarChar(16)
  labelplaylist labelplaylist[]
}

model labelplaylist {
  playlistId Int
  labelId    String   @db.VarChar(16)
  label      label    @relation(fields: [labelId], references: [title])
  playlist   playlist @relation(fields: [playlistId], references: [id])

  @@id([playlistId, labelId])
  @@index([labelId], name: "labelId")
}

model playlist {
  id              Int             @id @default(autoincrement())
  createdAt       DateTime?       @default(now()) @db.DateTime(0)
  title           String          @db.VarChar(100)
  labelplaylist   labelplaylist[]

  @@index([userId], name: "userId")
}

And I would like to delete only the relation between the label and the playlist table. I tried it to do like this:

const deleteRelation = await prisma.labelplaylist.delete({
    where: {
        playlistId_labelId: 
    },
})

I have the primary key of the label and playlist table, but I don't know how I get the primary key => playlistId_labelId.

Thank's for helping out.

prisma2 prisma mysql
2 Answers

Here's the syntax for queries with composite key

    const deleteRelation = await prisma.labelplaylist.delete({
        where: {
            playlistId_labelId: {
                playlistId: playListIdVariable, //replace with appropriate variable
                labelId: labelIdVariable, //replace with appropriate variable
            },
        },
    });

You can read more in the get record by compound ID or compound unique identifier subsection of the CRUD Reference Guide in the Prisma docs. This reference shows reading data, but the where condition is similar for delete and update as well.

user_88558440

Since it's an explicit many-to-many relationship, nested deleteMany only deletes relation table records, acting like a disconnect. You can then write your query like that:

await req.db.playlist.update({
  data: {
    labels: {
      deleteMany: {},
    },
  },
  where: {
    id: labelId,
  },
})

or the other way around.

It won't delete your related table records but only the links between them.

user_27362330
Related