用2列左联接

Question

我想用两列连接两个表，它在phpmyadmin中工作。但这不适用于拉拉。那么，如何将其转换为拉拉结构。

SELECT 
    `j`.*,
    `ts`.*,
    `ps`.*,
    `pn`.*,
    `c`.*,
    `planner`.*,
    `ps`.`name` AS `section_name`,
    `ps`.`id` AS `section_id`,
    `planner`.`date` AS `planner_date`,
    `pn`.`id` AS `planner_note_id`
FROM
    `planner`
        INNER JOIN
    `timeslots` AS `ts` ON `ts`.`id` = `planner`.`timeslot_id`
        INNER JOIN
    `planner_sections` AS `ps` ON `ps`.`id` = `planner`.`planner_section_id`
        LEFT JOIN
    `planner_notes` AS `pn` ON `pn`.`date` = `planner`.`date` and `pn`.`contract_id` = `planner`.`contract_id`
        INNER JOIN
    `contracts` AS `c` ON `c`.`id` = `planner`.`contract_id`
        INNER JOIN
    `jobs` AS `j` ON `j`.`contract_id` = `c`.`id`
WHERE
    `c`.`id` = 57        
        AND MONTH(`planner`.`date`) = 6
        AND `planner`.`date` >= '2017-06-13'
        AND `planner`.`deleted_at` IS NULL
ORDER BY `planner`.`date` ASC

但是当我把它放在laravel查询结构中时，它不起作用了。

$planner_list = Planner::join("timeslots as ts","ts.id","planner.timeslot_id")
                ->join("planner_sections as ps","ps.id","planner.planner_section_id")
                ->leftJoin("planner_notes as pn","pn.date","planner.date")
                ->leftJoin("planner_notes as pn","pn.contract_id","planner.contract_id")
                ->join("contracts as c","c.id","planner.contract_id")
                ->join("jobs as j","j.contract_id","c.id")
                ->where('c.id','=',$contract_id)
                ->where('planner.date','>=',$current_date)
                ->whereMonth('planner.date',$current_month)
                ->select(['j.*','ts.*','ps.*','pn.*','c.*','planner.*','ps.name as section_name','ps.id as section_id','planner.date as planner_date','pn.id as planner_note_id'])
                ->orderBy('planner.date','ASC')
                ->orderBy('planner.timeslot_id','ASC')
                ->orderBy('section_id','ASC')
                ->get();

上面的对话对我没用。

也不起作用，

->leftJoin("planner_notes as pn","pn.date","planner.date","pn.contract_id","planner.contract_id")

编辑:-，不，我没有收到任何错误。只是考虑到最后一个条件。

ON `pn`.`contract_id` = `planner`.`contract_id`

我还有一个困惑，如果我写了两个左联接，那么在条件下是否可以覆盖？

编辑-2 :-它只在条件下返回最后一个。查询日志的查询是:-

SELECT 
    `j`.*,
    `ts`.*,
    `ps`.*,
    `pn`.*,
    `c`.*,
    `planner`.*,
    `ps`.`name` AS `section_name`,
    `ps`.`id` AS `section_id`,
    `planner`.`date` AS `planner_date`,
    `pn`.`id` AS `planner_note_id`
FROM
    `planner`
        INNER JOIN
    `timeslots` AS `ts` ON `ts`.`id` = `planner`.`timeslot_id`
        INNER JOIN
    `planner_sections` AS `ps` ON `ps`.`id` = `planner`.`planner_section_id`
        LEFT JOIN
    `planner_notes` AS `pn` ON `pn`.`contract_id` = `planner`.`contract_id`
        INNER JOIN
    `contracts` AS `c` ON `c`.`id` = `planner`.`contract_id`
        INNER JOIN
    `jobs` AS `j` ON `j`.`contract_id` = `c`.`id`
WHERE
    `c`.`id` = 57        
        AND MONTH(`planner`.`date`) = 6
        AND `planner`.`date` >= '2017-06-13'
        AND `planner`.`deleted_at` IS NULL
ORDER BY `planner`.`date` ASC

​

​

Answer 1

你可以

->leftJoin('planner_notes as pn', function($join){
            $join->on('pn.date', '=', 'planner.date');
            $join->on('pn.contract_id','=','planner.contract_id');    
        })

或

将表别名更改为

->leftJoin("planner_notes as pn","pn.date",'=',"planner.date")
->leftJoin("planner_notes as pnotes","pnotes.contract_id",'=',"planner.contract_id")

Answer 2

Laravel与多条件连接

LEFT JOIN
    `planner_notes` AS `pn` ON `pn`.`date` = `planner`.`date` and `pn`.`contract_id` = `planner`.`contract_id`

相当于：

->leftJoin('planner_notes as pn', function($join){
            $join->on('pn.date', '=', 'planner.date');
            $join->on('pn.contract_id','=','planner.contract_id'); 
        })

laravel.com参考文献：在……上面

在联接中添加"on“子句。

On子句可以链接在一起。

$join->on('contacts.userid'，'='，'users.id') ->on('contacts.infoid'，'='，'info.id')

将产生以下SQL：

关于contacts.user_id = users.id和contacts.info_id = info.id