How to use 'and' with 'on' condition of joins in knex

Question

I am trying to generate a query like:

select ifnull(t1.name, ‘default’) as name
from tab1 as t1
left join tab2 as t2 
 on t1.id=t2.id and t2.code=“someValue”

I wrote this in knex:

var query = knex().from(’tab1’).join(’tab2', function() {

    this.on('tab1.id', '=', 'tab2.id').andOn('tab2.code', '=', 'someValue')
}, 

‘left')
.column([

knex.raw(‘IFNULL(tab1.name, "no name") as name')

]);

This does not execute as it treats 'someValue' as a column. How can I apply 'and' condition in this case?

Answer 1

Not well documented but you can now use onVal, and any of its and / or variants.

So, instead of

.on('tab2.code', '=', knex.raw('?', ['someValue']))

You can simply write:

const query = knex()
  .from('tab1')
  .join('tab2', function() {
     this.on('tab1.id', '=', 'tab2.id')
     this.andOnVal('tab2.code', '=', 'someValue')
}, 'left')
.column([knex.raw('IFNULL(tab1.name, "no name") as name')]);