我正在尝试实现一个排序的调度系统。因此，我有一个TimeBlock的数据库设计，每行间隔15分钟。实际的数据库会更复杂，下表只是为了说明我的情况。
可用时间表
id | date | start_time | end_time | doctor_id | status
时间块表
id | appointment_id | available_time_id | start_time | end_time | status
可用时间的示例
id | date | start_time | end_time | doctor_id | status
1 | 2018-06-18 | 08:00:00 | 09:00:00 | 1 | Active
时间块的示例
id | appointment_id | available_time_id | start_time | end_time | status
1 | null | 1 | 08:00:00 | 08:15:00 | Active
2 | null | 1 | 08:15:00 | 08:30:00 | Active
因此，当用户想要预订时间时，系统会根据start_time和状态以及列appointment_id是否为null来检查表。如果满足条件，则应进行更新。
当两个用户试图同时选择同一时间时，就会出现我的问题。我的验证将通过，然后其中一个用户条目将覆盖另一个用户的条目。我该如何处理这个问题？我尝试过使用laravel的悲观锁定(sharedLock和lockForUpdate(，但没有用。不确定我用错了什么。
public function create() {
if (request()->has('member_id')) {
$member_id = request('member_id');
} else {
$member_id = get_member_id();
}
try {
DB::beginTransaction();
$member = Member::find($member_id);
$member_treatment = DB::table('Member_Treatment')->where('id', request('member_treatment_id'))->first();
$treatment = Treatment::find($member_treatment->treatment_id);
$date = Carbon::parse(request('date'));
$start_time = Carbon::parse(request('time'));
$end_time = $start_time->copy()->addMinutes($treatment->durationRequired);
$member_package_id = request('member_package_id');
$doctor = Doctor::find(request('doctor_id'));
$available_time = AvailableTime::available($doctor, $treatment, $date, $start_time)->first();
// if (!$this->validate($date, $start_time, $doctor->id)) {
// DB::rollback();
// return false;
// }
$data = [
'doctor_id' => request('doctor_id'),
'member_id' => $member_id,
'treatment_id' => $member_treatment->treatment_id,
'member_treatment_id' => $member_treatment->id,
'member_package_id' => request('member_package_id'),
'available_time_id' => $available_time->id,
'date' => $date->format('Y-m-d'),
'start_time' => $start_time->format('H:i:s'),
'end_time' => $end_time->format('H:i:s'),
'status' => Appointment::$pending,
'created_by' => Auth::id() ?: $member->user_id
];
if (request()->has('remarks'))
$data['remark'] = request('remarks');
if (request()->has('admin_remark'))
$data['admin_remark'] = request('admin_remark');
$appointment = Appointment::create($data);
$this->update_timeblocks($appointment);
$this->update_member_package($appointment, false);
DB::commit();
return $appointment;
} catch (Exception $e){
DB::rollback();
dd($e);
}
}
protected function update_timeblocks(Appointment $appointment) {
$start_time = Carbon::parse($appointment->start_time);
// add buffer
$end_time = Carbon::parse($appointment->end_time)
->addMinutes(Appointment::$buffer);
// subtract interval to get the previous timeblock
$end_time->subMinutes(TimeBlock::$interval);
// retrieve all
$timeblocks = DB::table('TimeBlockMaster')
->where('available_time_id', $appointment->available_time_id)->get();
foreach ($timeblocks as $key => $timeblock) {
$time = Carbon::parse($timeblock->start_time);
$block_starttime = Carbon::parse($timeblock->start_time);
$block_endtime = $start_time->copy()->addMinutes($appointment->treatment->duration);
if ($time >= $start_time && $time < $end_time) {
$timeblock->appointment_id = $appointment->id;
$timeblock->end_time = $block_endtime;
$timeblock->available_duration = $block_starttime->diffInMinutes($block_endtime);
$timeblock->status = TimeBlock::$reserved;
}
if ($time == $end_time) {
$timeblock->appointment_id = $appointment->id;
$timeblock->end_time = $block_endtime;
$timeblock->available_duration = $block_starttime->diffInMinutes($block_endtime);
$timeblock->status = TimeBlock::$buffer;
}
}
$index_range = [];
$start = 0;
$end = 0;
// get the index of timeblocks that have appointment
for ($i = 0; $i < count($timeblocks); $i++) {
if (($i+1) < count($timeblocks)) {
$current = $timeblocks[$i]->appointment_id;
$next = $timeblocks[$i+1]->appointment_id;
if ($current != $next) {
$end = $i;
$index_range[] = ['start' => $start, 'end' => $end];
$start = $i+1;
}
} else {
$index_range[] = ['start' => $start, 'end' => $i];
}
}
$index = 0;
foreach ($index_range as $range) {
$endtime = Carbon::parse($timeblocks[$range['end']]->start_time)->addMinutes(TimeBlock::$interval);
$index = $range['start'];
for ($i = $index; $i <= $range['end']; $i++ ) {
$starttime = Carbon::parse($timeblocks[$i]->start_time);
DB::table('TimeBlockMaster')->where('id', $timeblocks[$i]->id)
->update([
'appointment_id' => $timeblocks[$i]->appointment_id,
'available_duration' => $starttime->diffInMinutes($endtime),
'start_time' => $timeblocks[$i]->start_time,
'end_time' => $endtime->format('H:i:s'),
'status' => $timeblocks[$i]->status
]);
}
}
}
我会尝试添加这样的内容来"保留"约会
$date = Carbon::parse(request('date'));
$start_time = Carbon::parse(request('time'));
$available_time = AvailableTime::join('time_block', 'available_time.id', '=', 'time_block.available_time_id')
->where('available_time.date', $date)
->where('time_block.start_time', $start_time)
->where('available_time.doctor_id', request('doctor_id') )
->value('time_block.id');
if (empty($available_time) || blank($available_time) ) {
return false; // Add your return
}
$time_block= TimeBlock::find($available_time);
$time_block->update([
'appointment_id' => 0
]);
如果你把它添加到开始(在需要的地方更改(，你应该解决大部分问题，因为这将有效地为第一个人保留时间，并减少你可以让另一个人预订相同预约时间的时间。