I have a table appointments which following columns
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:
join('appointments', 'appointments.id as A', 'appointments.id + 1 as B')
appointmentsB.startTime - appointmentsA.endTimeof 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: 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?