MySQL查询转Laravel Eloquent遇阻：WHERE条件写法求助

原MySQL查询 #

SELECT CONCAT(e.lastname,', ',e.firstname,' ',e.middlename) as full_name,
e.emp_id,
t.date,
t.time_in,
t.time_out,
TIME_FORMAT(time_in, "%r") as Ctime_in,    
TIME_FORMAT(time_out, "%r") as Ctime_out,    
TIMEDIFF(time_out,time_in) as number_of_hrs    
FROM `employees` as e LEFT JOIN time_records as t  on e.emp_id = t.emp_id
where t.date = '2024-03-01' OR t.date IS NULL 
GROUP by e.emp_id; 

其中日期'2024-03-01'在Eloquent中对应变量$day。

当前问题代码 #

DB::table('employees')
    ->select(
        DB::raw("CONCAT(employees.lastname,', ',employees.firstname,' ',employees.middlename) as full_name"),
        'employees.emp_id',
        'time_records.date',
        'time_records.time_in',
        'time_records.time_out',
        DB::raw('TIME_FORMAT(time_in, "%r") as Ctime_in'),
        DB::raw('TIME_FORMAT(time_out, "%r") as Ctime_out'),
        DB::raw('TIMEDIFF(time_out,time_in) as number_of_hrs')
    )
    ->leftJoin('time_records', 'time_records.emp_id', '=', 'employees.emp_id')
    ->whereDate('date',$day)
    ->orwhereNull('date',$day)
    ->get();

问题分析 #

当前代码的核心问题有两个：

1. WHERE条件逻辑错误：直接使用whereDate()加orWhereNull()会导致条件逻辑不符合原SQL——Laravel的查询构造器中，orWhere会和前面的所有条件形成平级的OR关系，而原SQL是要筛选出time_records.date = $day 或者 time_records.date IS NULL的记录，需要把这两个条件作为一组来处理。
2. 缺少GROUP BY子句：原SQL中存在GROUP by e.emp_id，当前代码未添加，会导致结果分组逻辑不一致。

修正后的Eloquent代码 #

DB::table('employees')
    ->select(
        DB::raw("CONCAT(employees.lastname,', ',employees.firstname,' ',employees.middlename) as full_name"),
        'employees.emp_id',
        'time_records.date',
        'time_records.time_in',
        'time_records.time_out',
        DB::raw('TIME_FORMAT(time_in, "%r") as Ctime_in'),
        DB::raw('TIME_FORMAT(time_out, "%r") as Ctime_out'),
        DB::raw('TIMEDIFF(time_out,time_in) as number_of_hrs')
    )
    ->leftJoin('time_records', 'time_records.emp_id', '=', 'employees.emp_id')
    ->where(function ($query) use ($day) {
        $query->whereDate('time_records.date', $day)
              ->orWhereNull('time_records.date');
    })
    ->groupBy('employees.emp_id')
    ->get();

关键改动说明 #

• 条件分组：用闭包包裹whereDate和orWhereNull，确保这两个条件是一组逻辑，和原SQL的(t.date = '2024-03-01' OR t.date IS NULL)完全对应。同时明确指定time_records.date字段，避免字段歧义。
• 添加GROUP BY：补上groupBy('employees.emp_id')，和原SQL的分组逻辑保持一致。

内容的提问来源于stack exchange，提问作者Raymart Calinao