findMany works but deleteMany doesn't-CodePudding

I have a simple Note table and a Tag table with an implicit many-to-many relation.

model Tag {
  id    Int    @id @default(autoincrement())
  tag   String @unique
  notes Note[]
}

model Note {
  id    Int    @id @default(autoincrement())
  note  String @unique
  tags  Note[]
  ...
}

When I delete a note I want to delete the tags which was only in that note. I wrote this:

await prisma.tag.deleteMany({
    where: {
      notes: { none: { tags: { some: { tag: { in: tags } } } } }, // tags: string[] coming from deleted note
    },
  });

but it gives me the error:

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1093, message: "You can't specify target table 'Tags' for update in FROM clause", state: "HY000" })) })

but when I change it to findMany it finds them without any problem. What is the problem?

Currently I'm running another deleteMany on the data from findMany. I've read that in pure SQL you can wrap it in another SELECT but is there anything I can do in prisma?

CodePudding user response：

I searched a lot to find this.

Your problem is coming from mysql, I try with postgresql and I couldn't reproduce it.

But by chance, there is a solution.

Your new prisma query looks like:

await prisma.tag.deleteMany({
  where: {
    // Search tag with no notes
    notes: { none: {} },
    // and in your list
    tag: { in: tags }
  },
});

FYI Here, you weren't doing what you thought

notes: { none: { tags: { some: { tag: tags } } } }

You ask to search tags where notes doesn't have tags in tags

So if you had these relations

 ------------ 
| Note1 Tag1 |
| Note1 Tag2 |
| Note2 Tag3 |
| Note3 Tag3 |
 ------------

And you delete Note3 and then search for Tag3 with this query, Tag3 linked to Note2 does have some Tag3, so not deleted, but Tag2 doesn't have some notes with some Tag3, so it is deleted, same for Tag1