How can I create an explicit many-to-many connection, if it doesn't already exist?

[lumenwrites]

Hi! I'm working on a platform for selling courses. I have a Course model, Profile model, and a Purchase model - an explicit many-to-many connection between the Course and Profile representing the fact that the user has purchased the course.

My goal is to create this connection, but only if it doesn't already exist (adding the course to the list of courses purchased by users, but only if he hasn't purchased it already).

Here are my models:

model Profile {
  id        String   @id @default(cuid()) // cuid and uuid are the same, but cuid is shorter
  [...]
  boughtCourses Purchase[]
}

model Course {
  id     String     @id @default(cuid())
  buyers Purchase[]
}

model Purchase {
  createdAt DateTime @default(now())

  profile   Profile @relation(fields: [profileId], references: [id])
  profileId String
  course    Course  @relation(fields: [courseId], references: [id])
  courseId  String

  @@id([profileId, courseId])
}

And here's how I'm making the connection right now:

const purchasedCourse = await prisma.course.update({
  where: { id: courseSlug },
  data: {
    buyers: {
      create: [
        {
          createdAt: new Date(),
          profileId: profileId,
        },
      ],
    },
  },
})

The problem is that this code throws an error if the connection already exists (if the user has purchased the course before).

Does Prisma have some sort of command that creates a connection if one isn't already in place?

[austincrim]

Hey @lumenwrites 👋,

Have you taken a look at our connect option? It sounds like what you are looking for here.

[lumenwrites]

When I was using implicit many-to-many connections, code like this worked:

const purchasedCourse = await prisma.course.update({
  where: { id: courseSlug },
  data: {
    buyers: {
      connect: {
        id: profileId
      }
    },
  },
})

But now that I'm using the explicit many-to-many table (Purchase, as shown in the post above), this code gives me an error:

Type '{ id: any; }' is not assignable to type 'Enumerable<PurchaseWhereUniqueInput>'. Object literal may only specify known properties, and 'id' does not exist in type 'Enumerable<PurchaseWhereUniqueInput>'.

Now this code works:

const purchasedCourse = await prisma.course.update({
  where: { id: courseSlug },
  data: {
    buyers: {
      create: [
        {
          createdAt: new Date(),
          profileId: profileId,
        },
      ],
    },
  },
})

But it throws an error when the connection already exists.

Can you please help me figure out what am I doing wrong? How can I modify any of the code above to make connect work?

[austincrim]

When you switch to the explicit many-to-many, it is required to pass both parts of the composite id of the join table like so:

const purchasedCourse = await prisma.course.update({
  where: { id: courseSlug },
  data: {
    buyers: {
      connect: {
        profileId_courseId: {
          courseId: courseSlug,
          profileId: 'someProfileId'
        }
      }
    }
  }
})

Let me know if that works!

[lumenwrites]

Thank you very much for your reply!

This code:

  const purchasedCourse = await prisma.course.update({
    where: { id: courseSlug },
    data: {
      buyers: {
        connect: {
          profileId_courseId: {
            courseId: courseSlug,
            profileId: profileId
          }
        }
      }
    },
  })

Gives me an error:

Invalid `prisma.course.update()` invocation: The required connected records were not found. Expected 1 records to be connected after connect operation on one-to-many relation 'CourseToPurchase', found 0.

Could you tell me what am I doing wrong?

Edit:

I also tried doing this:

    const purchasedCourse = await prisma.course.update({
      where: { id: courseSlug },
      data: {
        buyers: {
          connectOrCreate: {
            where: {
              profileId_courseId: {
                courseId: courseSlug,
                profileId: profileId
              }
            },
            create: {
              createdAt: new Date(),
              profileId: profileId
            }
          }
        }
      },
    })

But it gives me an error:

Invalid `prisma.course.update()` invocation: Unique constraint failed on the fields: (`profileId`,`courseId`)

[pantharshit00]

Hello @lumenwrites,

Sorry for late follow up here. I tried to reproduce this locally and I wasn't able to reproduce it. I tried with the following script:

import { PrismaClient } from "@prisma/client";

async function main() {
  const prisma = new PrismaClient();

  console.log("Courses")
  console.dir(await prisma.course.findMany(), { depth: Infinity });
  console.log("\n\nProfiles")
  console.dir(await prisma.profile.findMany(), { depth: Infinity });
  console.log("\n\nPurchases")
  console.dir(await prisma.purchase.findMany(), { depth: Infinity });

  const data = await prisma.course.update({
    where: { id: "clb4q6514000011283yafw903" },
    data: {
      buyers: {
        connectOrCreate: {
          where: {
            profileId_courseId: {
              profileId: "clb4qjovp00061128f45hpbm0",
              courseId: "clb4q6514000011283yafw903",
            },
          },
          create: {
            createdAt: new Date(),
            profileId: "clb4qjovp00061128f45hpbm0",
          },
        },
      },
    },
  });
  console.log(data);
  prisma.$disconnect();
}

main();

Result(on two execution to detect conflicts):

 ~/code/reproductions/issue-13964                                                                                                                                  ✔ ─╮
╰─ yarn start                                                                                                                                                            ─╯
yarn run v1.22.17
$ ts-node main.ts
Courses
[
  { id: 'clb4q6514000011283yafw903' },
  { id: 'clb4qeouc0004112893coone2' }
]


Profiles
[
  { id: 'clb4q6u0p00021128e7zafxeb' },
  { id: 'clb4qjovp00061128f45hpbm0' }
]


Purchases
[
  {
    createdAt: 2022-12-01T06:58:29.265Z,
    profileId: 'clb4q6u0p00021128e7zafxeb',
    courseId: 'clb4q6514000011283yafw903'
  }
]
{ id: 'clb4q6514000011283yafw903' }
✨  Done in 0.74s.

╭─ ~/code/reproductions/issue-13964                                                                                                                                  ✔ ─╮
╰─ yarn start                                                                                                                                                            ─╯
yarn run v1.22.17
$ ts-node main.ts
Courses
[
  { id: 'clb4q6514000011283yafw903' },
  { id: 'clb4qeouc0004112893coone2' }
]


Profiles
[
  { id: 'clb4q6u0p00021128e7zafxeb' },
  { id: 'clb4qjovp00061128f45hpbm0' }
]


Purchases
[
  {
    createdAt: 2022-12-01T06:58:29.265Z,
    profileId: 'clb4q6u0p00021128e7zafxeb',
    courseId: 'clb4q6514000011283yafw903'
  },
  {
    createdAt: 2022-12-01T07:37:46.060Z,
    profileId: 'clb4qjovp00061128f45hpbm0',
    courseId: 'clb4q6514000011283yafw903'
  }
]
{ id: 'clb4q6514000011283yafw903' }
✨  Done in 0.76s.

If you are still experiencing this, can you please provide a reproduction like this with data that you are using?

[pantharshit00]

Hello,

I am going through old discussions again and saw this is still open. Can you please confirm if this is still relevant?

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!