Laravel eloquent: GroupBy EMPLOYEE then DAY

Question

I am building a Web app with Laravel (6.3)

I have a table appointments which following columns

id
startDateTime
endDateTime
employee_id
client_id

I want to get results grouped by employees, then inside employees grouped by DAY like this:

  0 => array:5 [▼
    "Angela" => array:6 [▼
        "2020-05-25" => array:2 [▼
            0 => array:6 [▼
              "employee_id" => 6
              "appointmentId" => 566
              "startDateTime" => "2020-05-25 08:00:00"
              "endDateTime" => "2020-05-25 09:00:00"
              "employee" => "Angela"
              "img_slug" => "salon-adam_angela.jpg"
            ]
            1 => array:6 [▼
              "employee_id" => 6
              "appointmentId" => 567
              "startDateTime" => "2020-05-25 09:00:00"
              "endDateTime" => "2020-05-25 10:30:00"
              "employee" => "Angela"
              "img_slug" => "salon-adam_angela.jpg"
            ]
        ]
        "2020-05-26" => array:3 
        "2020-05-27" => array:2
        "2020-05-28" => array:4
        "2020-05-29" => array:2
        "2020-05-30" => array:1
    ]
    "Stefanie" => array:6
    "Karolina" => array:6
    "Martina" => array:6
    "Ivonne" => array:1
  ]
]

I am using Laravel, so my eloquent query looks like this

Appointment::where('appointments.employee_id', $employeeId)
    ->join('employees', 'employees.id', 'appointments.employee_id')
    ->join('users', 'users.id', 'employees.user_id')
    ->orderBy('appointments.startDateTime')
    ->select(
        'employees.id as employee_id',
        'appointments.id as appointmentId', 'appointments.startDateTime', 'appointments.endDateTime',
        'users.first_name as employee'
    )                     
    ->get()
    ->groupBy('employee')
    ->groupBy('appointments.startDateTime')
    ->toArray();

However it just groups by employee, but ignores the day.

If I eliminate the ->groupBy('appointments.startDateTime') I get all the appointments correctly grouped by employee, but when I add the second grouping it just get two levels deeper array, but not grouped by dates.

I have tried all known combinations without success

What am i doing wrong here?

user_6632744 · Answer

You're trying to group by a datetime, not a date.

You need to left or substr your datetime into a date so you can then group by it. Here's an example of how to do it in MySQL

Add ->addSelect(DB::raw("left(appointments.startDateTime, 10) as startDate")) onto your query builder (or alternatively include the DB::raw and its contents in your current ->select()).

Then change your group bys to be ->groupBy(['employee', 'startDate']), and you should be golden.

Laravel eloquent: GroupBy EMPLOYEE then DAY

1 Answers