sql语句，在TP里不会拼接了。。。LEFT JOIN 加 and

Question

我是需要这样的，

SELECT
b.NAME province,
c.NAME addressCity,
d.NAME cityCode,
`a`.`realName`,
`a`.`address`,
`a`.`phone`,
`a`.`remark`,
`a`.`create_time` 
FROM
`order` `a`
LEFT JOIN `address_code` `b` ON `a`.`provinceCode` = `b`.`code` and `b`.`type` = 1
LEFT JOIN `address_code` `c` ON `a`.`eparchyCode` = `c`.`code`and `c`.`type` = 2
LEFT JOIN `address_code` `d` ON `a`.`cityCode` = `d`.`code` and `d`.`type` = 3
WHERE
`channel_id` = '1' 
AND `is_successful` = 0 
ORDER BY
`create_time` DESC 
LIMIT 0,
10

我写成下面是不对的。

$data['list'] =  Db::name("order")->alias('a')
    ->leftJoin('address_code b','a.provinceCode = b.code' and 'b.type = 1' )
    ->leftJoin('address_code c','a.eparchyCode = c.code' and 'c.type = 2')
    ->leftJoin('address_code d','a.cityCode = d.code' and 'd.type = 3')
    ->where($where)->field($field)->page($params['page'], $params['size'])->order('create_time','desc')->fetchSql(true)->select();

打印出来是

SELECT
b.NAME province,
c.NAME addressCity,
d.NAME cityCode,
`a`.`realName`,
`a`.`address`,
`a`.`phone`,
`a`.`remark`,
`a`.`create_time` 
FROM
`order` `a`
LEFT JOIN `address_code` `b` ON 1
LEFT JOIN `address_code` `c` ON 1
LEFT JOIN `address_code` `d` ON 1 
WHERE
`channel_id` = '1' 
AND `is_successful` = 0 
ORDER BY
`create_time` DESC 
LIMIT 0,
10

主要我想问的是

LEFT JOIN address_code b ON a.provinceCode = b.code and b.type = 1

LEFT JOIN `address_code` `c` ON `a`.`eparchyCode` = `c`.`code`and `c`.`type` = 2
LEFT JOIN `address_code` `d` ON `a`.`cityCode` = `d`.`code` and `d`.`type` = 3

tp要怎么写

Answer 1

语法出问题了 and 在php是语法关键字 类似 && (两者不完全相同哦)
来康康

    ->leftJoin('address_code b','a.provinceCode = b.code' and 'b.type = 1' )

第二个参数为'a.provinceCode = b.code' and 'b.type = 1' 再php 就是 string and string = 就是true 在tp转义成的就是 1

正确写法

    ->leftJoin('address_code b','a.provinceCode = b.code')

'b.type = 1' 放在where条件里

Answer 2

自问自答，
and 前后面的’ ‘号去掉就好了

->leftJoin('address_code b','a.provinceCode = b.code and b.type = 1' )
    ->leftJoin('address_code c','a.eparchyCode = c.code and c.type = 2')
    ->leftJoin('address_code d','a.cityCode = d.code and d.type = 3')