Knex: Support for Multiple 3-Argument Where Clauses in a Single .where()

[machineghost]

Currently one can specify a single where clause with the three-argument syntax:

.where('foo', '>', 5)

one can also specify multiple two-argument where clauses:

.where({ foo: 5, bar:  6 })

However there is no way, that I'm aware of, to specify multiple three-argument where clauses. It would be easy to add such support though ...

// Knex.where definition
where(...args) {
  if(_.isArray(args[0]) {
    args.forEach(argumentSet => this.where.apply(this, argumentSet);
    return this;
  }
  // ... the normal Knex where definition

The reason this functionality would be handy (or at least one reason) is for where-clause-processing functions. Currently such functions have to operate on the query:

const processWhereClause = (query, whereClause) => {
  const something = transformWhereClause(whereClause);
  // NOTE: transformWhereClause might, for instance, converting:
  // {foo: 'abc'} into ['foo', 'LIKE', '%abc%']
  const somethingElse = transformWhereClauseSomeOtherWay(whereClause); 
  query.where.apply(query, something);
  query.where.apply(query, somethingElse);
}

const addFooWhereClauses = (query) => processWhereClause(query)

Which looks particularly lame if you have multiple processing functions:

const addFooWhereClauses = (query) => processWhereClause(
  processWhereClauseSomeOtherWay(
    processWhereClauseAThirdWay(query, fooWhereClause),
  fooWhereClause),
fooWhereClause);

However, it would make a lot more sense to have that function simply operate on the where clause itself, rather than on the entire query:

let whereClause = processWhereClause(fooWhereClause);
whereClause = processWhereClauseSomeOtherWay(whereClause);
whereClause = processWhereClauseAThirdWay(whereClause);
const addFooWhereClauses = (query) => query.where(whereClause);

The above, or any sort of three-argument pre-Knex where-clause-building, is only possible if the where method can support multiple such clauses (eg. with the array syntax shown above).

Thanks for considering this.

[elhigu]

To me this doesn't look like the common use-case which is accessing DB for your business application and having support, but your case is like meta query builder which creates knex query builder...

Also benefit for having only for example (if I understood correctly what you are proposing)

.where(['a', '<', 1],['b', '<', 2],['c', '<', 3])

is really limited since it still doesn't support any other combining of parameters except and so it would help a little, but benefit would be pretty insignificant since looping over array and do stuff with javascript is really easy.

In your case I would I would just keep an array until you create query out of them and use e.g. reduce to add them to query builder:

let whereClause = processWhereClause(fooWhereClause);
whereClause = processWhereClauseSomeOtherWay(whereClause);
whereClause = processWhereClauseAThirdWay(whereClause);
const addFooWhereClauses = (query) => 
  whereClause.reduce((builder, statement) => builder.where(...statement), query);

This way you can also have some extra information in statements for example if you should combine them with and or or etc. (of course you would need to implement some where-clause grouping stuff also to make it truly flexible)... I don't see the benefit over maintenance burden so for now I'm voting -1 for this one.