RafaelMunoznl
5 days ago

Trying to get time difference (gap) between endTime and startTime following record

Posted 5 days ago by RafaelMunoznl

I have a table appointments which following columns

  • id
  • startTime
  • endTime
  • employee_id
  • client_id

In order to manage the free time of an employee, I need to get the time between one appointment and the next one. In plain words:

  • I need to join the appointment with itself: join('appointments', 'appointments.id as A', 'appointments.id + 1 as B')
  • I need to select the appointments, orderBy startTime, asc
  • Substract appointmentsB.startTime - appointmentsA.endTime of previous appointment to get the gap.

I did this:

$query = Appointment::where('employee_id', $employeeId);
   ->join('employees', 'employees.id', 'appointments.employee_id')
   ->join('appointments', 'appointments.id as A', 'appointments.id + 1 as B')
   ->where('startTime', '>', now())
   ->orderBy('appointments.startTime')
   ->select(
      'employees.id as employee_id',
      'appointments.id as appointmentId', 'appointments.startTime', 'appointments.endTime',
   )
   ->selectRaw(
      'TIMESTAMPDIFF(minute, B.startTime, A.endTime) as gap')
   ->get()
   ->groupBy('employee');

However, trying to join the table with itself give me the following error:

```SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'appointments' ```

I have tried all possible combinations to join the table with its succesor appointment.id+1

What am i doing wrong here?

Please sign in or create an account to participate in this conversation.