php - 有没有办法将 mysql 转换为 laravel eloquent 查询生成器？

Question

我想获得当天的员工的first_check_in和。last_check_out以下 MySQL 代码运行良好，但我是 eloquent 的新手，我不知道如何编写它。对不起我的英语不好。

SELECT `employees`.`*`, 
`teams`.`description`, 
`time_groups`.`start`, 
`time_groups`.`end`,
 cast(a1.action_time as date) AS date,
 Min(`a1`.`action_time`) AS `first_check_in`, 
 MAX(`a2`.`action_time`) AS `last_check_out` 
 FROM `employees` 
 JOIN `teams` 
 ON `employees`.`team_id` = `teams`.`id`  
 JOIN `time_groups` 
 ON `teams`.`time_group_id` = `time_groups`.`id`  
 JOIN `attendance_employees` AS a1
 JOIN `attendance_employees` AS a2
 ON `employees`.`id` = `a2`.`employee_id` 
 AND `a1`.`employee_id` = `a2`.`employee_id` 
 AND DATE(a1.action_time) = DATE(a2.action_time)
 WHERE  1 = 1
 AND `a1`.`type` = 1 
 AND `a2`.`type` = 2
 AND DATE(a1.action_time) = CURDATE()
 GROUP BY a1.employee_id, `date`

我曾经尝试过这样

DB::table('employees')
    ->join('teams', 'employees.team_id', '=', 'teams.id')
    ->join('time_groups', 'teams.time_group_id', '=', 'time_groups.id')
    ->join('attendance_employees as a1')
    ->join('attendance_employees as a2',
            function($join) {
                $join->on('employees.id', '=', 'a2.employee_id');
                $join->on('a1.employee_id', '=', 'a2.employee_id');
                $join->on('DATE(a1.action_time)', '=', 'DATE(a2.action_time)');
            }
    )
    ->select(
        'employees.*',
        'teams.description',
        'time_groups.start',
        'time_groups.end'
    )
    ->addSelect('cast(A1.action_time as date)')->alias('date')
    ->addSelect('a1.action_time')->alias('check_in')
    ->addSelect('a2.action_time')->alias('check_out')
    ->where(1,1)
    ->where('a1.type', 1)
    ->where('a2.type', 2)
    ->groupBy('employee_id')
    ->groupBy('date')

但得到以下错误：

SQLSTATE [42S02]：未找到基表或视图：1146 表 'db.attendance_employees as a1' 不存在

标签： phpmysqllaravellaravel-query-builder