Querying many-to-many relation in MongoDB not working as expected

[arnu515]

Hi all,
I've created a many-to-many relation between two collections in MongoDB with prisma. The relation works fine, with changes being reflected, but querying them using the every operator leads to unexpected results.

Here's the schema of the two relevant collections:

model Organisation {
  id          String   @id @default(dbgenerated()) @map("_id") @db.ObjectId
  name        String
  owner_id    String   @db.ObjectId
  // ...

  owner    User                  @relation(name: "owner", fields: [owner_id], references: [id])
  members  User[]                @relation(name: "members", fields: [member_ids])

  @@map("organisations")
}

model User {
  id               String   @id @default(dbgenerated()) @map("_id") @db.ObjectId
  v                Int?     @map("__v")
  email            String   @unique(map: "email_1")
  username         String
  organisation_ids String[] @db.Array(ObjectId)
  created_at       DateTime @default(now())

  owned_organisations Organisation[]        @relation(name: "owner")
  organisations       Organisation[]        @relation(name: "members", fields: [organisation_ids])
  invites             OrganisationInvites[]

  @@map("users")
}

I've written code to query a user's joined organisations (member_ids contains user.id). this.db is the prisma instance.

  async getJoinedOrgs(userId: string) {
    const org = await this.db.organisation.findMany({
      where: {
        member_ids: { has: userId }
      },
      include: {
        owner: true
      }
    });
    return org;
  }

The response returned contains organisations that doesn't have the user's id in the member_ids list.

Can someone help me here? Thanks in advance.

[renenielsendk]

I think the question i asked here is more or less the same.
The has query seems to be working in mysterious ways.

#10954

[arnu515]

For now i fetched all docs and used js's array.filter function

[renenielsendk]

Im guessing i would have to do that also. I will ask one from prisma, if not create a bug report

[renenielsendk]

@arnu515 Can u try hasEvery i did some debugging, i have no clue why. But it seems like it is working as i want when i use hasEvery, even though the documentation seems a bit off.

[arnu515]

Doesnt work

[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 have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

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 ongoing support of the Prisma community!