Explicit m-n relationships are creating extra tables and not executing queries properly

[crtr0]

Bug description

When I create models that have an explicit m-n relationship, two things happen:

• Two tables (_CategoryToPost and CategoriesOnPosts) are created to manage the relationship instead of one
• Creating and Querying the relationships does not work as expected

The reason I can't use an implicit m-n relationship is because I need to store meta-data about the relationship.

How to reproduce

• Use the following schema.prisma, which is taken directly from the Prisma docs.
• npx prisma migrate save --experimental
• npx prisma migrate up --experimental
• npx prisma generate
• use Prisma client to create a Category, a Post and a CategoriesOnPosts
• use Prisma client to query the categories on a Post

Expected behavior

• I expect a single join table to be created that contains foreign keys postId and categoryId and a column to capture the createdAt metadata.
• I expect to be able to create an association between a Post and a Category and access that association through both the Post and Category models.

Prisma information

datasource DS {
  provider = "postgres"
  url      = "xxx"
}

generator client {
  provider      = "prisma-client-js"
}

model Post {
  id         Int        @default(autoincrement()) @id
  title      String
  categories Category[] @relation(references: [id])
}

model Category {
  id    Int    @default(autoincrement()) @id
  name  String
  posts Post[] @relation(references: [id])
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  // relation scalar field (used in the `@relation` attribute above)
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  // relation scalar field (used in the `@relation` attribute above)
  categoryId Int
  createdAt  DateTime @default(now())

  @@id([postId, categoryId])
}

  const r1 = await prisma.category.create({
    data: {
      name: "Test Category"
    }
  })

  const r2 = await prisma.post.create({
    data: {
      title: "Test Post"
    }
  })

  const r3 = await prisma.categoriesOnPosts.create({
    data: {
      post: {
        connect: {
          id: r2.id
        }
      },
      category: {
        connect: {
          id: r1.id
        }
      },
      createdAt: new Date()
    }
  })

  const r4 = await prisma.post.findOne({
    where: {
      id: r2.id
    }
  }).categories()

  console.log(r4)

Output: []

Environment & setup

• OS: Mac OSX
• Database: PostgreSQL
• Prisma version: 2.0.0-beta.1
• Node.js version: v12.14.0

[pantharshit00]

You will need to reference the custom relation table in the posts and categories instead of referencing Post or Category field. If you don't do that, it would make the implicit m:n and the join table will be just another table.

Here is the correct schema for this use case:

model Post {
  id         Int                 @default(autoincrement()) @id
  title      String
  categories CategoriesOnPosts[]
}

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

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int
  createdAt  DateTime @default(now())

  @@id([postId, categoryId])
}

After that script returns the correct result: