Implicit many to many relationship always returns all records from other model

[thw0rted]

I have an implicit many-to-many relationship between two models. Records in model X get associated with records in model Y using connect or create. When I create records of each model using Prismock, then query the result using an include to populate related-record properties, each model-X record comes back with an array containing every record from model-Y.

---

It might be easier to explain with examples, check out my reproduction and follow the README to see this in action.

In this example, use of a "widget" can be subject to approval. There is a table of approval types -- maybe you need a "license" for it, or "security" has to approve access to it. A relationship captures which widgets need which approval, and also which users are allowed to sign off on a particular type of approval. So, initially a user would not be an approver for any type, then we could make them a "security" approver like prisma.user.update({where: {...}, data: {approverFor: {connect: {name: 'security'}}}).

The bug I think I've found is that if I create a approval types "security" and "license", then create a new user with no approverFor argument, the new user record has an "approverFor" array with both these types. I tried to craft prisma.user.update(...) invocations to remove these "phantom" values but couldn't -- I don't know if entries are actually being made in whatever Prismock uses in-memory as an equivalent of an implicit-relationship table, or if the "include" handler that populates the related records has a bug that makes it ignore the contents of the implicit-table and always return all values from the other table.

UPDATE: I just updated my linked repro to include a new relationship between the Widget and User models, both of which have an autoincrement ID. I previous believed that the issue might be that the model in the "middle" here, ApprovalType, has a String primary key (@id) rather than an auto-increment integer, which I think is unusual. Apparently it happens for auto-ID models too.

[moonwave99]

Same for me and for #668 .

I was trying to implement disconnect myself, when I encountered the same behaviour you mentioned.

Inside the findOne method for instance, the connect middleware call returns { where: { undefined: undefined }, leading to returning all the entries of the related table.

The solution would be to create the intermediate join table/hashmap when the schema is parsed (One to many relationships work because they rely on direct foreign key referencing).

Let's see if I can report any progress soon!

[moonwave99]

We have some progress! Given the following implicit PostToTag relationship:

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @unique @db.VarChar(255)
  createdAt DateTime @default(now())
  imprint   String   @default(uuid())
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  blog      Blog     @relation(fields: [blogId], references: [id], onDelete: Cascade)
  blogId    Int
  tags      Tag[]
}

model Tag {
  id    Int    @id @default(autoincrement())
  title String @unique @db.VarChar(255)
  posts Post[]
}

and given that there is a bunch of tags (n > 2) store in the db, the following test passes:

const updatePayload = {
  where: { id: 1 },
  data: {
    tags: {
      connect: [{ id: 1 }, { id: 2 }],
    },
  },
};
await prisma.post.update(updatePayload);
await prismock.post.update(updatePayload);

const findPostPayload = { where: { id: 1 }, include: { tags: true } };

const updatedPost = await prisma.post.findFirst(findPostPayload);
const updatedMockedPost = await prismock.post.findFirst(findPostPayload);

const findTagsPayload = {
  where: {
    posts: {
      some: {
        id: 1,
      },
    },
  },
  include: { posts: true },
};

const updatedTags = await prisma.tag.findMany(findTagsPayload);
const updatedMockedTags = await prismock.tag.findMany(findTagsPayload);

expect(updatedPost).toMatchObject(updatedMockedPost);
expect(updatedTags).toMatchObject(updatedMockedTags);

TL;DR

• just the connected entries are retrieved;
• I also implemented the connect on create case and the disconnect;
• the whole prismock test suite runs successfully.

I am running it locally against a personal project to see if the cases are properly handled. As soon as things are stable I'll polish them up and prepare a PR.

[moonwave99]

You can find the current PR state here.

Btw I tested it locally against your issue repo and:

 PASS  ./issues.spec.ts
  My issues
    ✓ No default uuid on creation (2 ms)
    ✓ No

Which I think is good!