Clarify `every` and `NULL`

[janpio]

More details: https://github.com/prisma/prisma/issues/11819#issuecomment-1258160223

[asfaltboy]

Just encountered the same issue as in the comment, and I think it's a particular gotcha for an m-to-n relationship, where some of the records had no related entries in the other side.

I've been thinking how to express the issue so it's easy to understand by readers with varying degrees of experience in the area of SQL, programming and boolean logic.

Taking inspiration from the discussion in the thread, I propose this note to caution users:

[!CAUTION] Using every when querying a model with a reverse relationship, of which there are related records that do not relate to any records of the queried model, can lead to surprising results.

For example, consider a query for categories, where every post matches a given postId. When there are any categories without assignments (CategoriesOnPosts), the following query would also return those categorties in addition to categories that are related to the requested Post. I.e:

await prisma.category.findMany({
  where: {
    posts: {
      every: { postId },
    },
  },
});

This behaviour is intentional, and is designed to be naturally aligned with the Javascript Array.every() function. For instance, consider filtering in-memory objects in a JS runtime such as Node.js or a browser:

// GIVEN category.posts is either an empty array [] or is an array with some Post ids
categories = [
  {name: 'Category A', posts: []},
  {name: 'Category B', posts: [1, 2]}
];
categories.every(category => category.posts.includes(postId)).length
// outputs 2

I reckon we should add this note (or a similar one to it) in one, or in possibly more than one, of these sections:

• https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations
• https://www.prisma.io/docs/orm/reference/prisma-client-reference#every
• https://www.prisma.io/docs/orm/prisma-client/queries/relation-queries#filter-on--to-many-relations

@janpio @meletj what are your thoughts? Is the wording good? Is the proposed section to add it to good?

[asfaltboy]

We could also add a recommendation to achieve the desired outcome with. I can think of a number of options, each generating a slightly different query:

1. Query the categories of a given post - useful when you only want to get the post and the category
2. Query the relation table directly - useful when you need info on the assignment
3. Query the categories as before, but add the some: {} relation filter alongside the every filter - this filters out any categories with no posts.

I think either of these may be apropriate in different situations, so I think we should add all of them. If you know of any other options, which are more recommended, we can add them