Prisma 'set' with explicit many-to-many relation?

Question

As in this document of Prisma, set can be used to override the value of a relation.

const user = await prisma.user.update({
  where: { email: '[email protected]' },
  data: {
    posts: {
      set: [{ id: 32 }, { id: 42 }],
    },
  },
})

But when I tried it with explicit many-to-many relation, it does not work.

model Product {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name        String
  description String?
  price       Decimal
  sku         String  @unique
  published   Boolean @default(false)

  tags ProductTag[]
}

model Tag {
  id   Int    @id @default(autoincrement())
  name String

  products ProductTag[]
}

model ProductTag {
  productId String
  tagId     Int

  createdAt DateTime @default(now())

  product Product @relation(fields: [productId], references: [id])
  tag     Tag     @relation(fields: [tagId], references: [id])

  @@id([productId, tagId])
}

My code to update Product

update(id: string, updateProductDto: UpdateProductDto) {
    const tags = updateProductDto.tags.map((tag) => ({
      productId_tagId: {
        productId: id,
        tagId: tag.id,
      },
    }));

    console.log(JSON.stringify(tags));

    return this.prisma.product.update({
      where: { id: id },
      data: {
        ...updateProductDto,
        tags: {
          set: [...tags],
        },
      },
    });
  }

I want to update the product's tag with the product's information.

How could I implement this correctly?

Scintillometer

Please provide the error messages, and your ProductTagWhereUniqueInput from node_modules/.prisma/client/index.d.ts – Popovich

Answer 1

Preamble

I came across this question when I was struggling with this myself. I assume that you've probably figured it out by now, but I wanted to post the solution I came up with in case anyone else comes across this in the future like me.

I found your GitHub discussion, which then led me to this other GitHub issue that finally helped me figure out what I think is a pretty good solution.

Problem Explanation

The problem here is that with an explicit many-to-many relationship, the relation is represented by creating/deleting records within a relation table, described in the Prisma docs here. set does not create or delete records, it simply disconnects existing relationships and connects the provided models.

Solution

Because set doesn't create or delete records, we need to do both of these steps. Luckily, you can do both of these steps in a single query. I've adapted your code to show what I do in my code:

// BEFORE -- NOT WORKING
update(id: string, updateProductDto: UpdateProductDto) {
    const tags = updateProductDto.tags.map((tag) => ({
      productId_tagId: {
        productId: id,
        tagId: tag.id,
      },
    }));

    return this.prisma.product.update({
      where: { id: id },
      data: {
        ...updateProductDto,
        tags: {
          set: [...tags],
        },
      },
    });
  }

// AFTER -- WORKING
update(id: string, updateProductDto: UpdateProductDto) {
    return this.prisma.product.update({
      where: { id },
      data: {
        ...updateProductDto,
        tags: {
          deleteMany: {},
          create: updateProductDto.tags.map((t) => ({ tag: { connect: { t.id } } }))
        },
      },
    });
  }

Notes

A couple things to take into account with this approach:

• This will completely reset the relationships between the two models, even if your new set of tags contains tags that were already present. So if you have extra properties in your relation table records that you want to keep intact (such as the createdAt property), you'll need to account for copying those properties over.
• I have only tested this using Prisma 4.1.0 and higher, I'm not sure what version you were using then or now.

I hope this helps anyone else that makes it to this post!

Babblement

From reading the schema in the OP, it looks like the entire reason to have a ProductTag model is to associate a timestamp -- this product had that tag applied at a particular time. If you don't care about tracking "time of tagging", you don't need the many to many table in the middle at all. I think you can fix this with deleteMany: { where: { tagId: { not: { in: { updateDto.tags.map(t => t.id) } } } }, then your createMany can set the ignoreDuplicates option. (In fairness, that might not have been an option back in 2022?) – Grishilda

@Grishilda That's a cool way to do it! If that option was available back then, I wasn't aware of it. But I may have just been ignorant to it. :) I did mention in the original post that you'd have to account for bringing the createdAt field over using the approach I wrote though, in the Notes section. – Babblement

I also disagree that you wouldn't need the many-to-many table if you don't care about tracking the time of creation. As long as you need to be able to associate multiple tags with multiple products, you'll need the many-to-many table. – Babblement

Answer 2

You could delete existing productTag records and then create new ones, in two separate queries. Then you could run the two queries as a transaction.

This is what it would look like

    // do other updates to Tags with values from updateProductDto. 

    const deleteOldtags = prisma.productTag.deleteMany({
        where: { productId: "__PRODUCT_ID__" },
    });

    const addNewTags = prisma.productTag.createMany({
        data: [
            {
                productId: "__PRODUCT_ID__",
                tagId: TAG_ID_VALUE

            },
            // ...array of productID and tagId objects. 
        ]
    })

    let updateTags = await prisma.$transaction([deleteOldtags, addNewTags])

This is a workaround, that is slightly different from set as it will create new records every time.

Woven

This might be the only way I could think about too. I will mark this as an answer if there is no better. – Amador

This will not update the relation, it will create new objects, which is quite different comportment. – Popovich

@RomainTAILLANDIER Yes, it will create new objects, but it is the workaround for this problem. – Amador

Answer 3

I try a reply, but i would ask you to add the error messages, and your ProductTagWhereUniqueInput from node_modules/.prisma/client/index.d.ts

update(id: string, updateProductDto: UpdateProductDto) {
    const tags = updateProductDto.tags.map((tag) => ({
        // no need to encapsulate  
        productId: id,
        tagId: tag.id,
    }));
     // prevent the tags property to be update in product table, because you want to use set. 
    delete updateProductDto.tags;

    console.log(JSON.stringify(tags));

    return this.prisma.product.update({
      where: { id: id },
      data: {
        ...updateProductDto,
        tags: {
          set: tags, // no need to rebuild the array
        },
      },
    });
  }

Popovich

I have copied your code but the type of 'set' is invalid. So I think it has to be encapsulated. – Amador