15

In my Prisma schema, I have a many-to-many relationship between posts and categories. I've added @map options to match the Postgres snake_case naming convention:

model Post {
  id         Int            @id @default(autoincrement())
  title      String
  body       String?
  categories PostCategory[]

  @@map("post")
}

model Category {
  id    Int            @id @default(autoincrement())
  name  String
  posts PostCategory[]

  @@map("category")
}

model PostCategory {
  categoryId Int      @map("category_id")
  postId     Int      @map("post_id")
  category   Category @relation(fields: [categoryId], references: [id])
  post       Post     @relation(fields: [postId], references: [id])

  @@id([categoryId, postId])
  @@map("post_category")
}

I'm trying to create a post with multiple categories at the same time. If a category exists, I'd like to connect the category to the post. If the category doesn't exist, I'd like to create it. The creation part is working well, but the connection part is problematic:

  await prisma.post.create({
    data: {
      title: 'Hello',
      categories: {
        create: [{ category: { create: { name: 'News' } } }],
        connect: {
          categoryId_postId: { categoryId: 1, postId: ? }, // This doesn't work, even if I had the postId
        },
      },
    },
  });

How can I connect an existing category to a new post with the schema that I have?

Johnny Oshika
  • 54,741
  • 40
  • 181
  • 275

5 Answers5

18

What you need here is connectOrCreate.

So something like this should work:

      await prisma.post.create({
        data: {
          title: 'Hello',
          categories: {
            create: [
              {
                category: {
                  create: {
                    name: 'category-1',
                  },
                },
              },
              { category: { connect: { id: 10 } } },
            ],
          },
        },
      });

You can also read more about this in the docs here

Johnny Oshika
  • 54,741
  • 40
  • 181
  • 275
Ryan
  • 5,229
  • 1
  • 20
  • 31
  • 1
    This exhibits strange behavior. If the `{ categoryId: 1, postId: 1 }` bridge record exists in the `post_category` table, then it replaces the bridge record with `{ categoryId: 1, postId: new_id }`. In other words, it re-assigns another post's category to this new post. I don't want any other post to be impacted when I create a new post. I'd like to create a new category if the category doesn't exist, or add a new bridge record if it does. There's a good example using `create` and `set` on implicit relations here: (url to follow), but it doesn't work b/c I'm using explicit relation. – Johnny Oshika Jan 29 '21 at 16:09
  • Here's the URL that I was referring to in my previous comment: https://www.prisma.io/docs/support/help-articles/working-with-many-to-many-relations#implicit-relations The example there uses `tags: { set: [{ id: 1 }, { id: 2 }], create: { name: 'typescript' } }`, which is what I'd like to do, but can't seem to get it to work with my explicit relationship. – Johnny Oshika Jan 29 '21 at 16:09
  • Based on the docs you linked to, I think I want something like this, but this throws an exception because I can't link from `categoryId_postId` to `category` (sorry about the formatting): `connectOrCreate: { create: { category: { create: { name: 'category-1' } }, }, where: { categoryId_postId: { category: { name: 'category-1' }, }, }` – Johnny Oshika Jan 29 '21 at 16:18
  • In that case, this should work: ```await prisma.post.create({ data: { title: 'title', categories: { create: { category: { connect: { id: 1 } } } }, }, })``` This should connect the post to an existing category and will create the relation in the join table as well. – Ryan Feb 01 '21 at 09:38
4

This problem drove me up the wall so I'll contribute my solution here incase it helps someone else. First, read this: https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations

My schema is similar to the one in question, only difference is I have Files instead of Posts and Tags instead of Categories. To link the created file's tags to existing tags I used this:

  await Promise.all(DEFAULT_FILES[2].map(file => prisma.file.create({
    data: {
      ...file,
      user_id: userId,
      parent_id: homeFolder.id,
      tags: {
        create: file.tags?.map(name => ({
          tag: {
            connect: {
              id: tags.find(t => t.name === name)?.id
            }
          }
        }))
      },
    }
  })))
TeemuK
  • 2,095
  • 1
  • 18
  • 17
  • Could you tell me the type of file in your solution? I got it to work, but don't know the type of the input. I thought it's Prisma.FileCreateInput but tags has no map property. – Robin-Whg Nov 24 '22 at 15:18
  • It's a `seed.ts` that I execute with `"seed": "ts-node ./prisma/seed.ts"` – TeemuK Nov 28 '22 at 10:39
2

After hours of trying I finally came up with the following solution for my use case.

park[] <-> exercise[]

// create parks
const parks = await prisma.park.createMany({data: [...]});

// create and connect exercises
const exercises = [...];
await Promise.all(
  exercises.map(async (exercise) => {
    await prisma.exercise.create({
      data: <any>{
        ...exercise,
        parks: {
          connect: parks.map((park) => ({ id: park.id })),
        },
      },
    });
  }),
);
Behemoth
  • 5,389
  • 4
  • 16
  • 40
0
await prisma.postCategory.create({
  data: {
    category: {
      connectOrCreate: {
        id: categoryId
      }
    },
    posts: {
      create: [
        {
          title: 'g3xxxxxxx',
          body: 'body g3xxxxx'
        }
      ],
    },
  },
})

You should connect to the existing record and then create the record that you want to be related with the first one.

https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries (Connect an existing record)

Rafael Carvalho
  • 11
  • 2
0

Explicit many-to-many create/update existing tags read more here 

let args =[1,2,3,4]
tags: {
      create: args.tags?.map(tagId=>({
          tag:{
              connect:{
                  id:tagId
              }
          }
      }))
    },
 }
mrhoseah
  • 1
  • 1