https://www.prisma.io/ logo
Join Slack
Powered by
:alphabet-yellow-question: Is it possible in Prism...
# orm-help
r
alphabet yellow question Is it possible in Prisma to handle 
connectOrCreate
and 
disconnect
in a single query when updating many:many relations?
Context After searching in the Prisma docs & here on Slack, I found this example by @Ryan from a year ago:
Copy code
await prisma.article.update({
    where: { name: 'something' },
    data: {
      tags: {
        disconnect: [{ name: 'b' }],
        connectOrCreate: {
          create: { name: 'c' },
          where: { name: 'c' },
        },
      },
    },
  })
Unfortunately, this examples expects me to previously understand which posts I want to 
disconnect
and which I want to 
connectOrCreate
— so I gather it’s not possible to do this in a single query via prisma? -- Example Schema:
Copy code
model Article {
  id      String          @id @db.VarChar  
  author  String          @db.VarChar

  posts   ArticleToPost[]
}

model Post {
  id       String  @id @db.VarChar
  name     String  @db.VarChar @unique

  articles ArticleToPost[]
}

model ArticleToPost{
  id        Int     @id @default(autoincrement())
  articleId String  @db.VarChar
  postId    String  @db.VarChar

  post      Post    @relation(fields: [postId], references [id])
  article   Article @relation(fields: [articleId], references [id])
}
Input:
Copy code
{
  articleId: 1,
  posts: [
    {name: "What a post"},
    {name: "Another post"},
    {name: "Interesting post"}
  ]  
}
Expected behaviour: connectOrCreate • If a post with the name doesn’t exist 
create
post & 
connect
to article • If a post with the name exists, 
connect
to article disconnect • If an article previously had other posts connected, 
disconnect
Expected result: 
article.posts WHERE articleId = 1
returns
Copy code
// same as input but now I have the postId as well
[
  {id: 1, name: "What a post"},
  {id: 2, name: "Another post"},
  {id: 3, name: "Interesting post"}
]
One solution that comes to mind to simplify things: 1. Disconnect all related records 2. 🔁 For all posts: createOrConnect But this somehow doesn’t feel right? 🤔
n
Hey Richard 👋 It’s indeed true that you are expected to know in advance which posts need to be disconnected and which need to be created or connected. From what I understand you are suggesting that the posts which are not in the input but exist in the database should be disconnected and the ones in input should be either created or connected. It is kind of similar to set
Copy code
const user = await prisma.user.update({
  where: { email: '<mailto:alice@prisma.io|alice@prisma.io>' },
  data: {
    posts: {
      set: [{ id: 32 }, { id: 42 }],
    },
  },
})
In the above example, we update an existing User record by disconnecting any previous Post records and connecting two other existing ones.
r
Oh wow! That’s neat. (and I love how 
set
relates to my thoughts about how I wanted to determine `disconnection`/`createOrConnect` : disconnect the (mathematical) set difference and the createOrConnect the new set)
That means I have to create any non-existing `post`s before I can overwrite the relation. Sounds doable. 👍
Okay, I ended up doing it like this: 1. partitioning the input into 
createPosts
& 
existingPosts
2. Create missing posts 3. update relation (
set […createdPosts, existingPosts]
) This looks much better now:
Copy code
// 1. Partition input
const [createPosts, existingPosts] = data.reduce(...)

// 2. create missing posts
const created = await ctx.prisma.$transaction(
  createPosts.map((createPost) =>
    ctx.prisma.article.create({ data: createPost, select: { id: true } }),
  ),
);

// 3. set relations
const updated = await ctx.prisma.article.update({
  where: { id: programId },
  data: {
    applySteps: {
      set: [...created, ...existingPosts],
    },
  },
});
The only bummer here is that I have to wrap the post creation in a transaction because the 
findMany
doesn’t return the id that I later need. But you solved my use case. Thanks a lot @Nurul 🙏
n
I am glad to hear that Richard! 🙌
3 Views
Open in Slack
PreviousNext
Powered by