prisma: "every" relation filter not working as expected

@janpio

would be to add or do not ahve this relation set (= database NULL) or something similar to the explanation, did I understand that correctly?

Yes, because I would assume that the majority of people wouldn’t anticipate that every would return records with no relations. I for sure didn’t

Hi, please do like this:

prisma.admin.findMany({
            select: {
                id: true,
                name: true,
                email: true,
                roles: {
                    select: {name: true},
                },
                permissions: {
                    select: {name: true},
                },
            },
            where: {
                permissions: {
                    some: {},
                    every: {
                        id: {
                            in: [1, 2, 3]
                        }
                    }
                }
            }
        })

Add one more some just before every.

The question is exactly about “in” + “every” (read my first comment) and that it doesn’t work the way people expect and may cause unexpected bugs. At the end I don’t use prisma anymore because I like to have control and to work logical. If you think that it is logically correct the “in” + “every” to return also records having no relations at all and should work in this way then it’s not a bug and you should close the issue. I have nothing more to say.

In support of the existing API behaviour: it’s mathematically correct because any statement about the members of an empty set is true https://en.wikipedia.org/wiki/Empty_set

Every: “There are no items in this set for which this statement is false” Some: “There is at least one item in this set for which this statement is true” This is why combining every and some results in the desired behaviour, which is “There is at least one item in this set AND there are no items in this set for which this statement is false”

Prisma’s query language is an abstraction over SQL/noSQL so shouldn’t conform to their query language conventions. It’s an ORM for JS so absolutely should align more closely with native JS APIs. For what it’s worth Mongo does exhibit this behaviour too: https://www.mongodb.com/docs/manual/reference/operator/aggregation/allElementsTrue/

Some clarity in the docs here would be great though

Hey, any progress on this? Started migrating my project to prisma and just ran into this

I don’t think it’s a bug, it is an expected behavior. Think of it as of following JS equivalent:

@SevInf If this is the expected behavior, then the docs are kinda misleading:

every

Returns all records where all (“every”) related records match filtering criteria.

It says: returns all records where match, but it also returns records that don’t have this relation, so they do not match the criteria

And imho every: [1,2] should only return records that have both 1+2 relations

And again if this is the expected behavior then why is this issue still open?

@janpio Give me an example using SQL language and “in” operator that will return users that have either no permissions, permission 1, permission 2 or permissions 1+2. At the end Prisma is an layer between JS and SQL or noSQL right? The expected behavior should be like SQL or noSQL and not like JS .every function.

agreee with @Tronikelis 。 Are there any plans to fix this problem? Or you don’t think it’s a problem at all。

Hey, any progress on this? Started migrating my project to prisma and just ran into this

I don’t think it’s a bug, it is an expected behavior. Think of it as of following JS equivalent:

@SevInf If this is the expected behavior, then the docs are kinda misleading:

every

Returns all records where all (“every”) related records match filtering criteria.

It says: returns all records where match, but it also returns records that don’t have this relation, so they do not match the criteria

And imho every: [1,2] should only return records that have both 1+2 relations

And again if this is the expected behavior then why is this issue still open?

@apecollector every means that condition individually applies to every item in a relationship. What you query does is basically, “Give me all posts, where every tag is both { name: 'Development', color: 'Blue' } and { name: 'Programming', color: 'Pink' }”. Since conditions are mutually exclusive, you won’t get anything as a response. What you want is: give me all posts, where one of the tags is { name: 'Development', color: 'Blue' } and some other tag is { name: 'Programming', color: 'Pink' }. Which could be done with a following query:

const posts = await prisma.post.findMany({
    where: {
      AND: [
          { tags: { some: { name: 'Development', color: 'Blue' } }},
          { tags: { some: { name: 'Programming', color: 'Pink' } }},
      ]
    },
  });

@janpio those few days are actually 43. You don’t need confirmation from @adventurini to reproduce this bug, just take a look at my example. It’s clear enought.

@jdkdev that JS code is exactly how this query would behave (I think you meant to have .includes(permission) in this case, not .includes(permission.id)) with exactly the same behavior described here. You can check this by using following list of admins:

const admins = [
  {
    permissions: [1, 2, 3],
  },
  {
    permissions: [1, 2],
  },

  {
    permissions: [],
  },
  {
    permissions: [4],
  },
];

in this case, all but the last admin will be returned.

I tried to achieve what you want without using raw queries and the best I could come up with 4.3.1 version is:

await prisma.admin.findMany({
    where: {
      AND: [
        { permissions: { some: { id: 1 } } },
        { permissions: { some: { id: 2 } } },
        { permissions: { some: { id: 3 } } },
        { permissions: { none: { id: { notIn: [1, 2, 3] } } } },
      ],
    },
  })

This is definitely suboptimal and are where we could’ve done better. If you’d like to stick with raw queries until we can provide something better, here is a better raw query (assuming postgres):

SELECT * FROM admins 
WHERE adminId IN (
  SELECT adminId FROM admins_permissions
  GROUP BY adminId,
  HAVING array_agg(permissionId) @> array[1, 2, 3]
)

@jdkdev will something like work for you?

prisma.admin.findMany({
            where: {
                permissions: {
                    none: {
                        id: {
                            notIn: [1, 2, 3]
                        }
                    }
                }
            }
        })

I don’t think it’s a bug, it is an expected behavior. Think of it as of following JS equivalent:

admins.filter(admin => { 
  return admin.permissions.every(permission => [1, 2, 3].includes(permission.id)) 
})

1. every on an empty list is always true. This is consistent with JS, as well with SQL.
2. Right way to read every: { id: { in: [1, 2, 3]}} is “return the admins, where every permission id is either 1, 2, 3” which is exactly what happens.