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.

[mesteche]

Hi @kettanaito, thanks for for the quick reply.

My bad on the date comparators, I relied on the README and didn't check further, sorry for the cold sweat. I made a PR to add it to the doc.

About contributing, I'm all for it, I'll give it a go this week-end even though I'm not versed that much into typescript.

For the other scenario, the only one I can spot for now is comparing properties of an entity between themseves.
For instance, let say you have two entities:

export const configuration = {
  id: primaryKey(() => faker.unique(faker.datatype.uuid)),
  maxDailyConsumption: () => faker.datatype.number(10),
  maxWeeklyConsumption: () => faker.datatype.number(100),
  maxMonthlyConsumption: () => faker.datatype.number(1000),
}
export const consumption = {
  id: primaryKey(() => faker.unique(faker.datatype.uuid)),
  configuration: oneOf('configuration'),
  lastDay: () => faker.datatype.number(100),
  lastWeek: () => faker.datatype.number(1000),
  lastMonth: () => faker.datatype.number(10_000),
}

What I suggest allows you to query consumption over their own configured threshold:

db.consumption.findMany({
  where: ({ lastMonth, configuration }) => lastMonth > configuration.maxMonthlyConsumption,
  take: 15,
  cursor: null,
})

I don't see how you can achieve that with the current querying system.
Although the current system as one advantage: queries are serializable.
Maybe there is a serializable way of expressing the same thing, with some self reference symbol or something, I mean, SQL is pretty serializable itself, it should be doable.

[kettanaito]

Thank you for landing the readme pull request so quickly!

Yeah, I see what you mean. It's quite tricky supporting self-referencing entities but in the case of querying it's safe: the entity is fixed at the moment of querying, as opposed to proposals to support value generators upon model declarations to derive one model property from another.

Although the current system as one advantage: queries are serializable.

Precisely. I will be in favor of an API that keeps queries serializable.

This library draws a tone of inspiration from Prisma and its querying API. I'm quite certain they had stumbled upon complex query criteria like this one, and I'd like to see how they solved them.

I mean, SQL is pretty serializable itself, it should be doable.

That's also a good place for inspiration. How are such queries usually achieved in SQL?

[kettanaito]

I've looked into PrismaClient reference but couldn't find any examples of such complex queries.

[kettanaito]

Update

Since v1.0, you can expand any q.where() filter at any level of your data: on the root, on a nested object, on a leaf property.

const users = new Collection({ schema })

users.findMany((q) => q.where((user) => {
  return user.someProp === 1 && user.anotherProp === null
}))

Learn more about the new Querying syntax.