whereIn crashes on empty list

[IlyaSemenov]

Similar to #260, I am putting this boilerplate here and there:

return postIds.length ? await db.post.whereIn("id", postIds) : []

because if I don't, it crashes with:

SELECT ... FROM "post" WHERE "post"."id" IN ()
Error: syntax error at or near ")"

Do you think it's worth a similar shortcut on the ORM level (empty list = no query), or that's too magicky? What about subqueries? Technically whereIn([]) could be translated internally into whereSql`false` but that's probably taking on too much...

[romeerez]

I agree, implementing the same "none" query as in #260 is a bit magical (non-obvious), but anyway it's better than crashing with an SQL error.

Good point regarding sub-queries, need to support expected behavior in sub-queries as well.

db.person.find(123).select({
  pets: (q) => q.pets.whereIn('name', []),
})

Here I'd expect that person is returned with empty array of pets.

But there is a join that makes an INNER JOIN internally, so the relation becomes required:

db.person.find(123).select({
  pets: (q) => q.pets.join().whereIn('name', []),
})

And this would need to throw a not found, since it's what find does when can't find.
Or to return undefined for findOptional.

And need to consider cases when joining and using sub-queries in different ways.

[romeerez]

Done, published in new orchid-orm version.

Empty whereIn will be resolved into none query.
The none now will handle sub-selects and joins better, see the updated docs.