Mistj's Digital Garden

Appearance

Kenx-LeftJoin写法 

let pm = knex.select(  
    "ck_registinfo.id",  
    "ck_registinfo.name",  
    "ck_registinfo.cardId"
    { isDone: "ck_deviceData.registCode" }  
    )  
        .from('ck_registInfo')  
        .leftJoin('ck_deviceData', 'ck_deviceData.registCode', 'ck_registInfo.registCode')
let pm = knex.select(  
    "ck_registinfo.id",  
    "ck_registinfo.name",  
    "ck_registinfo.cardId"
    { isDone: "ck_deviceData.registCode" }  
    )  
        .from('ck_registInfo')  
        .leftJoin('ck_deviceData', 'ck_deviceData.registCode', 'ck_registInfo.registCode')

对应的SQL语句为

sql
select `ck_registinfo`.`id`,  
       `ck_registinfo`.`name`,  
       `ck_registinfo`.`cardId`,      
       `ck_deviceData`.`registCode` as `isDone`  
from `ck_registInfo`  
         left join `ck_deviceData` on `ck_deviceData`.`registCode` = `ck_registInfo`.`registCode` and checkType = 'BChao'
select `ck_registinfo`.`id`,  
       `ck_registinfo`.`name`,  
       `ck_registinfo`.`cardId`,      
       `ck_deviceData`.`registCode` as `isDone`  
from `ck_registInfo`  
         left join `ck_deviceData` on `ck_deviceData`.`registCode` = `ck_registInfo`.`registCode` and checkType = 'BChao'

现在需要调整为left join a.x = b.x and b.t = 'xx'

sql
select `ck_registinfo`.`id`,  
       `ck_registinfo`.`name`,  
       `ck_registinfo`.`cardId`, 
       `ck_deviceData`.`registCode` as `isDone`  
from `ck_registInfo`  
         left join `ck_deviceData` on `ck_deviceData`.`registCode` = `ck_registInfo`.`registCode` and `ck_deviceData`.`checkType` = 'BChao'
select `ck_registinfo`.`id`,  
       `ck_registinfo`.`name`,  
       `ck_registinfo`.`cardId`, 
       `ck_deviceData`.`registCode` as `isDone`  
from `ck_registInfo`  
         left join `ck_deviceData` on `ck_deviceData`.`registCode` = `ck_registInfo`.`registCode` and `ck_deviceData`.`checkType` = 'BChao'

最终调整代码如下

let pm = knex.select(  
    "ck_registinfo.id",  
    "ck_registinfo.name",  
    "ck_registinfo.cardId",   
    { isDone: "ck_deviceData.registCode" }  
    )  
        .from('ck_registInfo')  
    .leftJoin('ck_deviceData', function () {  
      this.on('ck_deviceData.registCode', '=', 'ck_registInfo.registCode')  
      this.andOnVal('ck_deviceData.checkType', '=', 'BChao')  
    })
let pm = knex.select(  
    "ck_registinfo.id",  
    "ck_registinfo.name",  
    "ck_registinfo.cardId",   
    { isDone: "ck_deviceData.registCode" }  
    )  
        .from('ck_registInfo')  
    .leftJoin('ck_deviceData', function () {  
      this.on('ck_deviceData.registCode', '=', 'ck_registInfo.registCode')  
      this.andOnVal('ck_deviceData.checkType', '=', 'BChao')  
    })

参考: mysql - How to use 'and' with 'on' condition of joins in knex - Stack Overflow