Query to get available slot from appointments table

Question

I have employees, working_hours, & appointments table. with the following details:

Employees | working_hours                                                     | appointment
id, name  | id, day, start_time, end_time, employeeable_id, employeeable_type | id, employee_id, start_date_time, end_date_time

Relation:

class Employee extends Model
{
   public function workingHours()
   {
      return $this->morphMany(WorkingHour::class, 'employeeable');
   }
}

class WorkingHour extends Model
{
   public function employeeable()
   {
      return $this->morphTo();
   }
}

class Appointment extends Model
{
   public function employee()
   {
      return $this->belongsTo(Employee::class);
   }
}

Employee A has the following working hours:

[
   { day: 1, start_time: '08:00:00', end_time: '17:00:00' },
   ...
   { day: 5, start_time: '08:00:00', end_time: '17:00:00 }
]

Employee A has an appointment on May 23, 2022 09:00:00 till 09:30:00 (each appointment duration is 30 minutes)

Question:

If admin requests for available slots from May 22, 2022 to June 1, 2022 for Employee A, I expect response like this:

[
   { '2022-05-22': ['08:00', '08:30', '09:00', ..., '17:00'] },
   { '2022-05-23': ['08:00', '08:30', '09:30'] } // 09:00 notice excluded.
   ...
   { '2022-06-01, [] }
]

How to define the above query? All I can think of is to loop every working hours time from employee A and check if the time is available or not.

Answer 1

I suggest you don't handle time with queries. this is my solution:

public function index()
{
    $appointment = [
        'id' => 1,
        'name' => 'Appointment 1',
        'start_date_time' => '2022-05-23 09:00:00',
        'end_date_time' => '2022-05-23 09:30:00'
    ];

    // Employee A working hours
    $workingHours = collect([
        ['day' => 1, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 2, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 3, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 4, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 5, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 6, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 0, 'start_time' => '08:00:00', 'end_time' => '17:00:00'], // carbon for sunday default is 0
    ]);

    $dateArray = [];
    $startDate = Carbon::parse('2022-05-22');
    $endDate = Carbon::parse('2022-06-01');

    while ($startDate->lte($endDate)) {
        // seach for working hours that match the day of the week
        $workingHour = (array) $workingHours->firstWhere('day', $startDate->dayOfWeek);

        // generate time for each day
        $times = $this->generateTimes($workingHour);

        // extract date from appoint start date time
        $appointmentDate = Carbon::parse($appointment['start_date_time'])->format('Y-m-d');

        if ($appointmentDate === $startDate->format('Y-m-d')) {
            // remove time according to appointment time
            $times = $this->removeTime($times, $appointment);
        }

        // add time to date array
        $dateArray[$startDate->format('Y-m-d')] = $times;

        // increment date
        $startDate->addDay();
    }

    dd($dateArray);
}

private function generateTimes(array $workingHour)
{
    // the working time of the workers must be reduced by at least 1 hour.
    // because there is no way for you to have an appointment on your end working time.
    $startTime = Carbon::parse($workingHour['start_time']);
    $endTime = Carbon::parse($workingHour['end_time'])->subHour();

    $times = [];
    while ($startTime->lte($endTime)) {
        $times[] = $startTime->format('H:i');
        $startTime->addMinutes(30);
    }

    return $times;
}

private function removeTime($times, $appointment)
{
    $startTime = Carbon::parse($appointment['start_date_time']);
    $endTime = Carbon::parse($appointment['end_date_time']);

    $startTime = $startTime->format('H:i');
    $endTime = $endTime->format('H:i');

    $times = array_diff($times, [$startTime, $endTime]);

    return $times;
}