https://www.prisma.io/ logo
Join Slack
Powered by
Please help understand the usage of `some` and `ev...
# orm-help
d
Please help understand the usage of 
some
and 
every
with a more complex use-case for filtering on relations. Is this a bug with Prisma or I am not using it correctly? Goal: Given an organization id, provide all forms associated with it. Reality: when using 
every
clause the filtering doesn't work as expected, we get all forms from all organization. When using 
some
, we get forms from only that organization (expected). Code:
Copy code
prisma.applicationFormConfig.findMany({
        where: { organizations: { every: { id: organization.id } } },
})// yield all forms from all organization
prisma.applicationFormConfig.findMany({
        where: { organizations: { some: { id: organization.id } } },
      }),// works as expected
Why? Please help. More context: After inspecting SQL, it becomes obvious why, this is raw query for `every`:
Copy code
prisma:query SELECT "public"."ApplicationFormConfig"."id", ... FROM "public"."ApplicationFormConfig" WHERE ("public"."ApplicationFormConfig"."id") 
NOT IN (SELECT "t0"."A" FROM "public"."_ApplicationFormConfigToOrganization" AS "t0" 
INNER JOIN "public"."Organization" AS "j0" ON ("j0"."id") = ("t0"."B") 
WHERE ((NOT "j0"."id" = $1) AND "t0"."A" IS NOT NULL)) OFFSET $2
look at 
WHERE ((NOT "j0"."id" = $1)
which will yield all other records. 
$1
is the 
organization.id
in the prisma query. When using 
some
, it is fixed: 
WHERE ("j0"."id" = $1
 Schema:
Copy code
model ApplicationFormConfig {
  id            String         @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
...
  organizations Organization[]
}

model Organization {
  id                      String                  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
...
  applicationFormConfigs  ApplicationFormConfig[]
...
}
r
@Danil Kolesnikov 👋 This section in our docs explains it well 🙂
some
means at least and every means 
all
.
d
@Ryan Thank you for a quick followup! I've read this and I am not following exactly. Look, in the code, we have the filtering clause and matching on a specific 
organization.id
. Are you saying that every negates the filtering clause? Because thats what is happening. All 
applicationFormConfig
for ALL 
Organization
are returned. But we are looking for only the forms from a particular organization id.
Copy code
prisma.applicationFormConfig.findMany({
        where: { organizations: { every: { id: organization.id } } },
})// yield all forms from all organization
r
For yours and most of the use cases, 
some
is the way to go. 
some
will find at least one record matching the criteria where as 
every
will check for all records that match the criteria and only return those in which all relations match. If you’re familiar with JavaScript’s some and every, then this would be easier to understand.
d
I see so if I use 
every
and there is 1 match in the table, it will return all records from the table and not only the ones that match. Correct?
r
Nope. Consider this schema:
Copy code
model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  user   User?  @relation(fields: [userId], references: [id])
  userId Int?
}
I seed this with the following data:
Copy code
await prisma.user.create({
    data: {
      name: 'user 1',
      posts: {
        create: [{ title: 'prisma ORM' }, { title: 'prisma ORM again' }],
      },
    },
  })

  await prisma.user.create({
    data: {
      name: 'user 2',
      posts: {
        create: [{ title: 'prisma ORM' }, { title: 'another one again' }],
      },
    },
  })

  await prisma.user.create({
    data: {
      name: 'user 3',
    },
  })
If I query for the following with `every`:
Copy code
await prisma.user.findMany({
      where: { posts: { every: { title: { contains: 'prisma' } } } },
    })
I will get 
user 1
and 
user 3
back as the match the criteria. One has all posts with 
prisma
while the other has no posts. In your case, you want at least 1 record that matches so you need to use 
some
.
d
oh wow, I am confused now because in my query with 
every: { id: organization.id }
all rows get returned no matter the organization with filter not working but changing to 
some
fixes it. Why does that happen? a bug?
So in your case, all posts would get returned. I think the MDN articles you've sent confused me since JS functions return a boolean value, not sure how that translates to relational filtering in Prisma.
Also, in your case, would would changing to 
some
do? Will that yield the same result?
r
oh wow, I am confused now because in my query with 
every: { id: organization.id }
  all rows get returned no matter the organization with filter not working but changing to 
some
 fixes it. Why does that happen? a bug?
Would it be possible to send a simple reproduction so that I can check? Also as per your use case you need 
some
and not 
every
as I had said before.
So in your case, all posts would get returned. I think the MDN articles you’ve sent confused me since JS functions return a boolean value, not sure how that translates to relational filtering in Prisma.
Yeah 
every
makes sure that only those items are sent back in which each value of the relation matches the criteria. The JS function returns a boolean but the concept is same as Prisma returns the values that match instead of the boolean. 
some
would yield User 1 and User 2 as User 2 has a post that matches with Prisma.
9 Views
Open in Slack
PreviousNext
Powered by