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

Question

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
}

SirRagex · Accepted Answer · 2022-01-03 04:48:22Z

2

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();

answered Jan 3, 2022 at 4:48

SirRagex

361 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Catto Over a year ago

Just wow... this is more simple than I expected. Thanks

Catto Over a year ago

Anyway, how to get employee data from employee table? Becuase I'm getting error when trying to do something like this $attendance->employee->name

Catto Over a year ago

Nevermind, I just need to create another join

Catto Over a year ago

My bad, it works. But the result become multiple.

#items: array:2 [▼     0 => {#1378 ▼       +"id": "2d8628f0-6c60-11ec-981c-97ce5b9329e9"       +"employee_id": "f3c1b988-5bda-11ec-ab79-98fa9b511cca"       +"in_time": "2022-01-03 15:12:34"       +"out_time": "2022-01-03 15:12:34"     }     1 => {#1376 ▼       +"id": "5f4886c2-c986-4371-8c05-0aa1a8c88d7c"       +"employee_id": "f3c1b988-5bda-11ec-ab79-98fa9b511cca"       +"in_time": "2022-01-03 13:37:34"       +"out_time": "2022-01-03 15:12:34"     }   ] }

SirRagex Over a year ago

you can add another where()-> before select() for conditions. Or if you want to get just the first record then change ->get() to ->first().

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related