Feature requests: Pass multiple `Whereable` to `select` shortcuts for easy `OR` queries

[andybarron]

Trying to do something like this:

const conditions = [] // dynamic
if (something) {
  conditions.push({email: "[email protected]"})
}
if (somethingElse) {
  conditions.push({phone: "+12345678910"})
}
db.selectOne("person", conditions)

Unless I'm missing something, we can only do this with SQLFragments right now, which is a bit of a bear :)

To clarify, this would merge all the different top-level conditions using OR, since you can already use Whereables for AND.

[jawj]

Sorry for the slow reply. Interesting to see a bunch of people on board with this.

It seems like a reasonable idea and quite feasible, but how about using the or helper from conditions.ts instead?

That would make your example:

db.selectOne("person", db.conditions.or(conditions));

Or I tend to make a shortcut to conditions, which then just makes it dc.or(conditions).

I appreciate that the functions in conditions.ts aren’t currently well documented.

EDIT: OK, I realise that this doesn't currently work! But I'll look into making it work ...

[jawj]

Just had another look at this, prompted by #124.

The original suggestion is a very easy change, but I'm not sure about it. First, it's not very explicit: you kind of have to guess what the library's going to do with a Whereable[]. Second, the type-checking isn't very good, unless you write your conditions as object literals within the query.

For example, this is a runtime error but compiles fine (read up on excess property checks to find out why):

const cond = [
  { name: 'Jane Austen' },
  { xyz: 12 },
];

await db.select('authors', cond).run(pool);

On the other hand, the implementation work (all two lines of it!) is done. I'll have another look at the or helper, and have a think ...

[jawj]

OK, with a couple of tweaks, the or, and and not conditions can all take Whereables, which gives us stuff like this:

import { conditions as dc } from 'zapatos/db';

const cond = [
  { name: 'Jane Austen' },
  { isLiving: true },
];

await db.select('authors', dc.or(...cond)).run(pool);
await db.select('authors', dc.and(...cond)).run(pool);
await db.select('authors', dc.not({ name: 'Jane Austen' })).run(pool);

The and option isn't useful in this context, but could be useful if you were using weaker conditions via other condition helpers (lt, gt, not, etc). For example:

const letterRangeCond = [
  { name: dc.gt('H') },
  { name: dc.lt('K') },
];
await db.select('authors', dc.and(...letterRangeCond)).run(pool);

Thinking about it, I guess this is what worries me about using Whereable[] for OR queries: there are pretty reasonable applications for having a Whereable[] turn out as an AND query instead.

Anyway, the typing story is actually worse here than in the Whereable[] case, because there's currently no mechanism for automatically restricting the Whereables to the right table. So it would certainly be a good idea to explicitly type cond as s.authors.Whereable[].

In the long run, it would be nice to find ways to tighten up the type checking. But unless anyone has good objections, I guess I'll release these tweaks soon.

[jawj]

This is released in 6.1.

[remidewitte]

Many thanks !

[vmingam]

It's my turn to thank you @jawj for this new feature and @andybarron to have open this issue.