Hi @nicolas-goudry 👋

Thank you for raising this question.

Using an explicit many-to-many relation instead of an implicit one can make the Prisma Client API more complex. However, the advantage of using explicit many-to-many relations is that you can define extra fields on the relation table, which can be useful for storing additional information about the relation.

In your case, you're now connecting Pet to Owner through the OwnersOfPets model, which requires you to specify the petId and ownerId in the connect clause. This is why you can't use the unique name field of Owner to connect to the Pet anymore, because the connection is now being managed through the OwnersOfPets model. While the developer experience (DX) might be different when using explicit many-to-many relations compared to implicit ones, this is the expected behavior and not something you're doing wrong.

If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved.

Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏

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

Prisma's documentation covers this exact case with some very good examples so I recommend going over it. As mentioned in the docs, MongoDB doesn't support implicit many-to-many relations, so what you have to do is make it an explicit relation; this means adding a link table.

Here's a complete example that should work for your use case:

model User {
  id           String      @id @default(auto()) @map("_id") @db.ObjectId
  name         String?
  email        String      @unique
  username     String      @unique
  password     String
  profileImage String?     @default("https://cdn.pixabay.com/photo/2015/10/05/22/37/blank-profile-picture-973460_640.png")
  bio          String?
  createdAt    DateTime    @default(now())
  updatedAt    DateTime    @updatedAt
  /// Reference the link table
  userPosts    UserPosts[]
}

model Post {
  id        String      @id @default(auto()) @map("_id") @db.ObjectId
  slug      String[]
  caption   String?
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt
  /// Reference the link table
  userPosts UserPosts[]
}

/// Link table
model UserPosts {
  id     String @id @default(auto()) @map("_id") @db.ObjectId
  user   User   @relation(fields: [userId], references: [id])
  userId String @db.ObjectId
  post   Post   @relation(fields: [postId], references: [id])
  postId String @db.ObjectId
}

Firstly, your many to many relation has no join table. Any many to many relation requires a separate join table, for example "FixturesOnTeams" model which contains a compound primary key of both the primary keys of Fixture and Team.

Prisma shows an example here : https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations

Secondly, I question whether you need the away team and home team modelled in two separate relations. This is something that can be added as a field on the Team model. You could create a compound primary key on teamId and type (? you decide what field name is suitable here) and put all away teams and home teams into one relationship. You are complicating the design of your DB model by trying to put logic into the design of your model. This type of logic should be something you query for, not embed into the design of your table.

model FixturesOnTeams {
  teamId     Int                 @id @default(autoincrement())
  teamType   String
  Team       @relation(fields: [teamId, teamType], references: [id, type])
  fixtureId  Int
  Fixture    @relation(fields: [fixtureId], references: [id])

  @@id([teamId, teamType, fixtureId])
}

model Team {
  id         Int                 @unique
  type       String
  Fixtures   FixturesOnTeams[]

  @@id([id, type])
}

model Fixture {
    id         Int                 @id @default(autoincrement())
    Teams      FixturesOnTeams[]      
}

This will return all posts which have the category of id abc123. Note that posts may include categories other than id abc123.

const posts = await prisma.post.findMany({
    where: {
        category: {
            some: {
                id: 'abc123',
            },
        },
    },
});

some: Returns all records where one or more ("some") related records match filtering criteria.

https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#some