Support custom where filters

[mesteche]

Currently there are some query that are not possible to make directly and the dataset needs to be filtered manually afterward.
For instance, there is no way to quey on null or dates:

const now = new Date()
db.entity.findMany({ where: {
  someNullableProperty: { equals: null },
  someDate: { lte: now },
  someNumber: { gte: 10 },
} })

The only way I found to do this is to filter the results afterward:

const now = new Date()
db.entity.findMany({ where: { someNumber: { gte: 10 }} }).filter(
  ({ someNullableProperty, someDate }) => (
    someNullableProperty === null &&
    someDate <= now
  )
)

But we can't use pagination then and we have to manually implement it as well.

Instead of implementing every possible missing feature, I suggest allowing custom filter function to be used in where queries:

const now = new Date()
db.entity.findMany({ where: {
  someNullableProperty: (value) => (value === null),
  someDate: (date) => (date <= now),
  someNumber: { gte: 10 },
} })

It would be great if it was also possible to specify it at the where level, as it would allow to compare properties of a single entity, in addition to be able to use the existing pagination:

const now = new Date()
db.entity.findMany({
  where: ({ someNullableProperty, someDate, someNumber, maxValue }) => (
    someNullableProperty === null &&
    someDate <= now &&
    someNumber >= maxValue
  )),
  take: 15,
  cursor: null,
})

[kettanaito]

Hey, @mesteche. Thanks for the suggestion.

I wonder what goes wrong with date querying in your case. Could you take a look at our Date test suite and see if you can spot any difference? As far as I know, querying by Date is supported and functional but I may be missing some scenarios.

Querying by null is indeed a bug and we have a test confirmation added for that in #187. If you feel interested, you can lend me a hand in solving it. Otherwise, I will address it when I have time.

Are there any other scenarios that the library does not satisfy apart from those two? If not, I'd close this issue. It should be possible to query data by its properties and their respective comparators.