mysql - 加入 ON AND 与 laravel Eloquent 查询构建器

Question

我正在尝试在 laravel 中编写以下 sql 查询:

    select 
    `workers`.`first_name`, 
    `workers`.`last_name`, 
    `occupations`.`approval_date`, 
    `occupations`.`certification_date`, 
    `occupations`.`expiration_date`, 
    `occupations`.`type`, 
    `workers`.`photo` 

    from `worker_print_queue` 
    inner join `workers` on `worker_print_queue`.`worker_id` = `workers`.`id` 
    inner join `occupations` on `worker_print_queue`.`certification` = `occupations`.`type` AND `workers`.`id` = `occupations`.`worker_id`

我用下面的方式 Eloquent 地写了它:

$records = \DB::table('worker_print_queue')
                ->join('workers', 'worker_print_queue.worker_id', '=', 'workers.id')
                ->join('occupations', function($join){
                    $join->on('workers.id', '=', 'occupations.worker_id')
                            ->where('worker_print_queue.certification', '=', 'occupations.type');
                })
                ->select('workers.first_name', 'workers.last_name', 'occupations.approval_date',
                        'occupations.certification_date', 'occupations.expiration_date', 'occupations.type', 'workers.photo')
                ->get();

但是我没有收到任何结果。我确定我遗漏了什么，只是不确定是什么。

Answer 1

问题是这一行

->where('worker_print_queue.certification', '=', 'occupations.type');

正在将列 'worker_print_queue.certification' 与值 'occupations.type' 进行比较。

如果您将其更改为使用 on 子句，您应该没问题:

->on('worker_print_queue.certification', '=', 'occupations.type');