0

I am using Nodejs version 12.14.1 and Knex version 0.20.8

In Knex documentation they tell if in a where query a binding is undefined knex will throw an error. Is there a way to ignore or by pass it?

I tried using "useNullAsDefault": true, parameter in my knex file, but it doesn't help

SQL Example

knex('accounts')
  .where('login', undefined)
  .select()
  .toSQL()

ERROR

Undefined binding(s) detected when compiling SELECT. Undefined column(s): [login] query: select * from accounts where login = ?

Alexandra
  • 75
  • 1
  • 11

2 Answers2

0

You can send a function as parameter of where, they you can just check if your variable is not undefined so you don't generate a where without a value to compare:

let login = undefined

knex('accounts')
  .where((builder) => {
    if (login) {
      builder.where('login', login)
    }  
  })
  .select()
  .toSQL()

If login is undefined, the sql will be:

select *

And if you set login = 'test', the sql will be:

select * where ("login" = ?)

With test value in the bindings

Mickael B.
  • 4,755
  • 4
  • 24
  • 48
  • any reason why "useNullAsDefault": true property is not working? – Alexandra Jan 30 '20 at 19:46
  • `useNullAsDefault` is for insert query only. It is used to replace the `DEFAULT` value with `NULL` if you don't define a value. So it is not for select query with where clauses. – Mickael B. Jan 30 '20 at 19:49
  • ok, is there another way to ignore error instead, this was not present in previous versions of knex 0.14.2 – Alexandra Jan 30 '20 at 20:03
0

Please see this question from which I am basing my answer. You can use the modify method to conditionally add the where clause when needed.

knex('accounts')
  .select()
  .modify(function(queryBuilder) {
      if (req.query.param) {
          queryBuilder.where('login', req.query.param);
      }
   })
   .toSQL()
EternalHour
  • 8,308
  • 6
  • 38
  • 57