how do you delete a record from a multi key unique table ex Prisma #orm-help

how do you delete a record from a multi-key/unique...

Dave Edelhart

08/09/2022, 11:29 PM

how do you delete a record from a multi-key/unique table? ex:

Copy code

model covid_data_pivot {
    iso_alpha_3   String
    field   String
   date       DateTime @db.Date
   increment Int @default(1)
   type    String
   values_int  Int[]
   values_float Int[]
    @@unique([iso_alpha_3, field])
}

want to delete a single case of iso_alpha_3 / field incidence so I can write a new record

✅ 1

Nurul

08/10/2022, 1:21 PM

Hey Dave 👋 Here’s how you can delete a multi-key record Example:

Copy code

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query'],
});

async function main() {
  const insertData = await prisma.covid_data_pivot.create({
    data: {
      date: new Date(),
      field: 'cases',
      iso_alpha_3: 'USA',
      type: 'cumulative',
      increment: 1,
      values_float: [1],
      values_int: [1],
    },
  });
  console.log(insertData);

  const deleteData = await prisma.covid_data_pivot.delete({
    where: {
      iso_alpha_3_field: {
        field: 'cases',
        iso_alpha_3: 'USA',
      },
    },
  });

  console.log(deleteData);
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Nurul

08/10/2022, 1:22 PM

Here’s response:

Copy code

prisma:query BEGIN
prisma:query INSERT INTO "public"."covid_data_pivot" ("iso_alpha_3","field","date","increment","type","values_int","values_float") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "public"."covid_data_pivot"."iso_alpha_3", "public"."covid_data_pivot"."field"
prisma:query SELECT "public"."covid_data_pivot"."iso_alpha_3", "public"."covid_data_pivot"."field", "public"."covid_data_pivot"."date", "public"."covid_data_pivot"."increment", "public"."covid_data_pivot"."type", "public"."covid_data_pivot"."values_int", "public"."covid_data_pivot"."values_float" FROM "public"."covid_data_pivot" WHERE ("public"."covid_data_pivot"."iso_alpha_3" = $1 AND "public"."covid_data_pivot"."field" = $2) LIMIT $3 OFFSET $4
prisma:query COMMIT
{
  iso_alpha_3: 'USA',
  field: 'cases',
  date: 2022-08-10T00:00:00.000Z,
  increment: 1,
  type: 'cumulative',
  values_int: [ 1 ],
  values_float: [ 1 ]
}
prisma:query BEGIN
prisma:query SELECT "public"."covid_data_pivot"."iso_alpha_3", "public"."covid_data_pivot"."field", "public"."covid_data_pivot"."date", "public"."covid_data_pivot"."increment", "public"."covid_data_pivot"."type", "public"."covid_data_pivot"."values_int", "public"."covid_data_pivot"."values_float" FROM "public"."covid_data_pivot" WHERE ("public"."covid_data_pivot"."iso_alpha_3" = $1 AND "public"."covid_data_pivot"."field" = $2) LIMIT $3 OFFSET $4
prisma:query SELECT "public"."covid_data_pivot"."iso_alpha_3", "public"."covid_data_pivot"."field" FROM "public"."covid_data_pivot" WHERE ("public"."covid_data_pivot"."iso_alpha_3" = $1 AND "public"."covid_data_pivot"."field" = $2)
prisma:query DELETE FROM "public"."covid_data_pivot" WHERE ("public"."covid_data_pivot"."iso_alpha_3","public"."covid_data_pivot"."field") IN (($1,$2))
prisma:query COMMIT
{
  iso_alpha_3: 'USA',
  field: 'cases',
  date: 2022-08-10T00:00:00.000Z,
  increment: 1,
  type: 'cumulative',
  values_int: [ 1 ],
  values_float: [ 1 ]
}

Dave Edelhart

08/10/2022, 4:56 PM

ok thanks

🙌 1

3 Views

Open in Slack

Previous Next