How to convert this raw sql query to laravel eloquent or query builder?-CodePudding

Hello Laravel Developers, Good day. Please how can I convert a raw sql query to laravel query builder or eloquent ORM. I want to generate attendance report and display the report using data table. I want to show every employees data with his/her daily attendance log. I have two table employees and attendance_log where attendance_log belongs to emp_id from employees table.

My problem is:

I can't write this query in laravel query builder or eloquent ORM.

How to filter or search data from data table?

 $emp_id = $request->input('emp_id');
 $start_date = $request->start_date;
 $end_date = $request->end_date;

 $data = DB::select("
             SELECT 
             emp.*, 
             dep.department_name,
             al.emp_id,
             al.auth_date,
             min(al.auth_time) as 'check_in', 
             max(al.auth_time) as 'check_out'
             FROM employees as emp
             LEFT JOIN attendance_log as al ON emp.device_emp_id = al.emp_id
             JOIN departments as dep ON emp.department_id = dep.id
             WHERE al.emp_id IN $emp_id   // $emp_id is an array of employee's ID
             WHERE date(al.auth_date) BETWEEN $start_date AND $end_date
             GROUP BY emp.device_emp_id, date(al.auth_date_time)
         ");

my data table will be like this image

CodePudding user response：

You could use DB::raw()

$data = DB::select(DB::raw("
        SELECT 
        emp.*, 
        dep.department_name,
        al.emp_id,
        al.auth_date,
        min(al.auth_time) as 'check_in', 
        max(al.auth_time) as 'check_out'
        FROM employees as emp
        LEFT JOIN attendance_log as al ON emp.device_emp_id = al.emp_id
        JOIN departments as dep ON emp.department_id = dep.id
        WHERE al.emp_id IN $emp_id   // $emp_id is an array of employee's ID
        WHERE date(al.auth_date) BETWEEN $start_date AND $end_date
        GROUP BY emp.device_emp_id, date(al.auth_date_time)
    "));

Updated answer

    Employee::select(
            'employees.*',
            'departments.department_name',
            'attendance_log.emp_id',
            'attendance_log.auth_date',
            'min(attendance_log.auth_time) as check_in',
            'max(attendance_log.auth_time) as check_in'
        )
        ->leftJoin('attendance_log', 'employees.emp_id', '=', 'attendance_log.emp_id')
        ->join('departments', 'employees.department_id', '=', 'departments.id')
        ->whereIn('attendance_log.emp_id', $emp_id)
        ->whereBetween("date('attendance_log.auth_date')", $start_date, $end_date)
        ->groupBy('employees.device_emp_id')
        ->groupBy('attendance_log.auth_date_time')
        ->get();

CodePudding user response：

Try in this way. For more details Check: https://laravel.com/docs/8.x/queries

$query  = DB::table('employees as emp')
    ->leftjoin('attendance_log as al','al.emp_id','emp.device_emp_id')
    ->join('departments as dep','dep.id','emp.department_id')
    ->select('emp.*', 'dep.department_name', 'al.auth_date' , DB::raw("MIN(al.auth_time) AS check_in, MAX(al.auth_time) AS check_out"))
    ->whereIn('al.emp_id', [1,2,3]) // use your array
    ->groupBy('emp.device_emp_id', DB::raw('DATE(al.auth_date_time)'));

    if($start_date){
        $query  = $query->where('al.auth_date' , '>=' , $start_date);
    }
    if($end_date){
        $query  = $query->where('al.auth_date' , '<=' , $end_date);
    }
  $data = $query->get();