Reputation: 527
I have data like this:
id | employee_id | in_out | time |
1 | EMPLOYEE_01 | in | 08:00 |
2 | EMPLOYEE_01 | out | 04:00 |
How to merge it to become like this? I want to show only one rows in the views.
id | employee_id | in | out |
1 | EMPLOYEE_01 | 08:00 | 04:00 |
I've tried like this:
$attendances = DB::table('attendance')->select('id', 'employee_id', DB::raw('GROUP_CONCAT(in_out, attendance_time) as in_out_time'))
->groupBy('employee_id')
->get()
->map(function ($attendances) {
$in_out = explode(',', $attendances->in_out_time);
foreach ($in_out as $item) {
[$key, $value] = explode('-', $item);
$attendances->{$key} = $value;
}
return $attendances;
});
But the result is not exactly what I want. It become like this:
Illuminate\Support\Collection {#1373 ▼
#items: array:1 [▼
0 => {#1376 ▼
+"id": "68e14924-ad71-44ee-8918-a31e5039170b"
+"employee_id": "b3bfe713-5bda-11ec-ab79-98fa9b511cca"
+"in_out_time": "in2022-01-03 11:01:02,out2022-01-03 11:00:27"
+"in2022": "01"
+"out2022": "01"
}
]
#escapeWhenCastingToString: false
}
And if I'm not explode it, the result from get() is:
DB::table('attendance')->select('id', 'employee_id', DB::raw('GROUP_CONCAT(in_out, attendance_time) as in_out_time'))
->groupBy('employee_id')
->get();
Illuminate\Support\Collection {#1370 ▼
#items: array:1 [▼
0 => {#1376 ▼
+"id": "219ab767-2a2a-40fa-b6c4-b0504a24d410"
+"employee_id": "b3bfe713-5bda-11ec-ab79-98fa9b511cca"
+"in_out_time": "out2022-01-03 11:06:49,in2022-01-03 11:06:18"
}
]
#escapeWhenCastingToString: false
}
Upvotes: 1
Views: 373
Reputation: 36
You can try it like this.
DB::table('attendance as in')->leftJoin('attendance as out', function($join) {
$join->on('in.employee_id, '=', 'out.employee_id)->where('out.in_out', 'out');
})->select('in.id', 'in.employee_id', 'in.time as in_time', 'out.time as out_time')->get();
Upvotes: 2