P
Prisma•9mo ago
snchmt

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
snchmt
snchmtOP•9mo ago
snchmt
snchmtOP•9mo ago
I let this thread open, I used the run raw query but I would like something cleaner if someone has
Olyno
Olyno•9mo 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,
      },
    }),
 ]);
snchmt
snchmtOP•9mo 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
Olyno•9mo ago
What do you mean recursive?
snchmt
snchmtOP•9mo ago
it means that a tag can have children, and these children may also have children, etc
Olyno
Olyno•9mo ago
Children can have children? 🤔
snchmt
snchmtOP•9mo 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
Olyno•9mo 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
snchmt
snchmtOP•9mo 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
snchmt
snchmtOP•9mo 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
Olyno•9mo ago
Did you fix it?
snchmt
snchmtOP•9mo ago
How can I fix it ? For now I used a raw query which disables prisma verifications
Olyno
Olyno•9mo ago
Well, if you're using Mongo, it should be an object. If you delete the parent object, it should delete everything
snchmt
snchmtOP•9mo 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

Did you find this page helpful?