Catto
Catto

Reputation: 527

How to merge multiple rows with same id into one rows in Laravel

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

Answers (1)

SirRagex
SirRagex

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

Related Questions