I have 3 tables:

1. Shifts {vehicled_id, user_id, subdriver_id)
2. Users {user_id, subdriver_id)
3. Vehicles {vehicle_id}

I want to group Shifts when I join with Users and Vehicles but some shifts only have user_id or subdriver_id and some have both.

This is the picture of shifts tables.

The result I want is a groupBy of vehicle_id and: If it has only user_id or subdriver_id it will return {user_id, vehicle_id, shift_id (it can be an array)} or {subdriver_id, vehicle_id, shift_id (it can be an array)}. If it has both user and subdriver it will return {user_id, vehicle_id, shift_id(it can be array)}

shift_id is based on user_id and vehicle_id or subdriver_id and vehicle_id.

How can I groupBy and return a result like this?

CodePudding user response：

You have to use CASE statement which goes through multiple conditions and return a value based on respective condition.

DB::table('shifts')
->select(DB::raw('CASE WHEN shifts.user_id IS NULL THEN shifts.subdriver_id WHEN shifts.subdriver_id IS NULL THEN shifts.user_id ELSE shifts.user_id END as user_id, shifts.vehicle_id, GROUP_CONCAT(shifts.id) as shift_id'))
->leftJoin('users as u1','u1.id','shifts.user_id')
->leftJoin('users as u2','u2.subdriver_id','shifts.subdriver_id')
->groupBy('shifts.vehicle_id')
->get();