Prisma•8mo ago

How to cascade delete properly in a self-relation with mongodb ?

It is written in the doc that we need to set NoAction on self-relation database but how are we supposed to do it ? I tried getting all ids and using a deleteMany to delete parent and children together but I got a PrismaClientKnownRequestError.

generator client {
  provider = "prisma-client-js"
}

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

model Tag {
  id           String   @id @default(auto()) @map("_id") @db.ObjectId
  name         String
  Parent       Tag?     @relation("Children", fields: [parentId], references: [id], onUpdate: NoAction, onDelete: NoAction)
  parentId     String?  @db.ObjectId
  Children     Tag[]    @relation("Children")
}

generator client {
  provider = "prisma-client-js"
}

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

model Tag {
  id           String   @id @default(auto()) @map("_id") @db.ObjectId
  name         String
  Parent       Tag?     @relation("Children", fields: [parentId], references: [id], onUpdate: NoAction, onDelete: NoAction)
  parentId     String?  @db.ObjectId
  Children     Tag[]    @relation("Children")
}

async function getAllChildren(
  prisma: PrismaClient,
  tag?: Prisma.TagGetPayload<object>
): Promise<Prisma.TagGetPayload<object>[]> {
  if (!tag) return [];
  const childrenTags = await prisma.tag.findMany({
    where: { parentId: tag.id },
  });
  const subTags = (
    await Promise.all(childrenTags.map((tag) => getAllChildren(prisma, tag)))
  ).flat();

  return [tag, ...subTags];
}

const allTags = await getAllChildren(
prisma,
await prisma.tag.findUnique({
  where: { id: request.params.tagId },
})
);

const tagsIds = allTags.map((t) => t.id);
return prisma.tag.deleteMany({
where: {
  id: { in: tagsIds },
},
});

async function getAllChildren(
  prisma: PrismaClient,
  tag?: Prisma.TagGetPayload<object>
): Promise<Prisma.TagGetPayload<object>[]> {
  if (!tag) return [];
  const childrenTags = await prisma.tag.findMany({
    where: { parentId: tag.id },
  });
  const subTags = (
    await Promise.all(childrenTags.map((tag) => getAllChildren(prisma, tag)))
  ).flat();

  return [tag, ...subTags];
}

const allTags = await getAllChildren(
prisma,
await prisma.tag.findUnique({
  where: { id: request.params.tagId },
})
);

const tagsIds = allTags.map((t) => t.id);
return prisma.tag.deleteMany({
where: {
  id: { in: tagsIds },
},
});

15 Replies

snchmtOP•8mo ago

https://tenor.com/view/dog-ocean-pensive-thinking-sad-gif-27364531

Tenor

snchmtOP•8mo ago

I let this thread open, I used the run raw query but I would like something cleaner if someone has

Olyno•8mo ago

Hi :vmathi: You could try this:

const deletedRecords = await prisma.$transaction([
    prisma.tag.deleteMany({
      where: {
        parentId: parentId,
      },
    }),
    prisma.tag.delete({
      where: {
        id: parentId,
      },
    }),
 ]);

const deletedRecords = await prisma.$transaction([
    prisma.tag.deleteMany({
      where: {
        parentId: parentId,
      },
    }),
    prisma.tag.delete({
      where: {
        id: parentId,
      },
    }),
 ]);

snchmtOP•7mo ago

My tags are recursive, do you mean that I should create a query per level ? I remember that using deleteMany already creates a transaction, what's new with this way ?

Olyno•7mo ago

What do you mean recursive?

snchmtOP•7mo ago

it means that a tag can have children, and these children may also have children, etc

Olyno•7mo ago

Children can have children? 🤔

snchmtOP•7mo ago

A tag can have children tags, and these tags can also have children tags That's why I created a recursive function to get all the children ids

Olyno•7mo ago

Oh, something like that?

|- Tags
  |- Tags
    |- Tags
      |- ...

|- Tags
  |- Tags
    |- Tags
      |- ...

Not sure how you managed that, but my best guess would be to recommend you to use the on delete cascade and so, if one is deleted, the others are also deleted By doing that, you only need to specify 1 tag id and all its children will be deleted

snchmtOP•7mo ago

yes it's something like that I just used a childrenId like in the model above, the on on delete cascade is not authorized on mongodb, that's the issue here

snchmtOP•7mo ago

See https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/referential-actions/special-rules-for-referential-actions

Special rules for referential actions in SQL Server and MongoDB | P...

Circular references or multiple cascade paths can cause validation errors on Microsoft SQL Server and MongoDB. Since the database does not handle these situations out of the box, learn how to solve this problem.

Olyno•7mo ago

Did you fix it?

snchmtOP•7mo ago

How can I fix it ? For now I used a raw query which disables prisma verifications

Olyno•7mo ago

Well, if you're using Mongo, it should be an object. If you delete the parent object, it should delete everything

snchmtOP•7mo ago

If I use objects it means that I should normalize on the front-end or the back-end, and if I want to modify a child element I need to search in the elements or modify the whole object I know that I'm trying to use a document database for relational purposes but most of my data is in document format except this part Also it means that I should duplice all my fields into a schema and a type

Gaming

Programming

How to cascade delete properly in a self-relation with mongodb ?