-1

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Lody Chi
  • 3
  • 3
  • [mre] [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) PS Your table design is a common anti-pattern for DB subtypes. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [How do you effectively model inheritance in a database?](https://stackoverflow.com/q/190296/3404097) – philipxy Aug 16 '22 at 15:20
  • "I" is always capitalized. – philipxy Aug 16 '22 at 15:29

1 Answers1

2

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();
Sushant Yadav
  • 121
  • 5