Filtering based on nested relation from one direction (many to many)

[SheaBelsky]

Consider these two models:

model User {
  id           String   @id
  options Option[]
}

model Option {
  id           String   @id
  label      String
  users     User[]
}

I want to be able to write a prisma.user.findMany query that returns all users whose options entry exist in an array of IDs. For example, ["testId1", "testId2", "testId3"]. From my understanding, the existing relation filters (every/some/none) don't work as-is for this purpose because they iterate over every User to see if they pass a given condition.

Consider;

prisma.user.findMany({
  where: {
    options: {
      every: {
        id: {
          in: ["testId1", "testId2", "testId3"]
        }
      }
    }
  }
});

What this means is if a given User has options equal to ["testId1", "testId2", "testId3", "testId4"], I would not be able to use every because testId4 fails the "every" condition (every options ID for this user does not exist in the provided array.)

some doesn't work in this scenario because a User will be returned even if a given User does not have every one of the provided Options - in this scenario, a User must have all of them, but it's perfectly acceptable if they have other Options too. Is this possible only with prisma.user.findMany, or do I have to use prisma.option.findMany for this purpose?

[nurul3101]

Hello @SheaBelsky 👋

For your use case, if

it's perfectly acceptable if they have other Options too

then wouldn't the every query that you have shared work? Because even if there is an extra option of "testId4" then to it's acceptable, right? Let me know if I misunderstood your statement.

Also, I believe going through the option model would be much easier.

[SheaBelsky]

From my experience, using every does not work. Because every one of the User's options are checked to see if they meet some condition. The example that I listed above does not appear to work because the test4 option does not satisfy the every condition (test4 is not in ["testId1", "testId2", "testId3"].)

[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!