findMany works but deleteMany doesn't

Question

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  Tags[]
  ...
}

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?

Answer 1

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 }
  },
});

EDIT In reality, if you don't want any tags that are not linked to a note you can do only

await prisma.tag.deleteMany({
  where: {
    notes: { none: {} },
  },
});

because it will search every tags with no notes linked to. The only thing is that it will check for all your tags in the Tag table so it won't be really efficient

To explain a little more notes: { none: {} }

Using mocked tables

Relationship Note / Tag table:

╔═════════╦════════╗
║ node_id ║ tag_id ║
╠═════════╬════════╣
║ Note1   ║ Tag1   ║
║ Note1   ║ Tag2   ║
║ Note2   ║ Tag2   ║
║ Note3   ║ Tag3   ║
║ Note4   ║        ║
║ Note5   ║        ║
║         ║ Tag4   ║
║         ║ Tag5   ║
║         ║ Tag6   ║
║         ║ Tag7   ║
╚═════════╩════════╝

Note table

╔═══════╦══════╗
║  id   ║ note ║
╠═══════╬══════╣
║ Note1 ║ a    ║
║ Note2 ║ b    ║
║ Note3 ║ c    ║
║ Note4 ║ d    ║
║ Note5 ║ e    ║
╚═══════╩══════╝

First case

When you add the filter notes: { none: {} }, you ask Prisma to find every tags that have none notes, so no relation in the Relationship Note / Tag table

So with a find many with this filter:

const tags = await prisma.tag.findMany({
  where: {
    notes: { none: {} },
  },
});

tags will contain the tags for ids Tag4, Tag5,Tag6 and Tag7 because they are not related to any note

Second case

When you do notes: { none: { note: 'a' } } you ask Prisma to find any tags that have none notes with note value = a

So with a find many with this filter:

const tags = await prisma.tag.findMany({
  where: {
    notes: { none: { note: 'a' } },
  },
});

tags will contain all tags except Tag1 and Tag2 that are related to Note1 with note value = a

END EDIT

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

╔═════════╦════════╗
║ node_id ║ tag_id ║
╠═════════╬════════╣
║ 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