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

user_1516866 0
Viewed 16249

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?

knex.js mysql
3 Answers

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')]);
user_21179960

I had to use knex.raw('?', ['someValue']) instead of knex.raw('someValue').

For me (using Knex 0.13.0) the accepted solution .on('tab2.code', '=', knex.raw('someValue') produced the query: tab2.code = someValue (without quotes) which then caused an Unknown column someValue error.

Using .on('tab2.code', '=', knex.raw('?', ['someValue'])) produced the intended tab2.code = 'someValue' (with quotes) comparing tab2.code to a string literal rather than a column.

user_60618040
Related