Clock in/Clock out功能更新时数据库记录重复问题排查

问题描述 #

开发Clock In和Clock Out功能时，执行Clock Out（更新Time Clock Out与Location Clock Out）操作后，数据库的AttendanceRecord表出现额外重复记录：原有记录未被正确更新，同时新增了一条包含更新后time_checkOut和location_checkOut的记录。

此前手动注入时间时功能正常，代码如下：

$date = date('Y-m-d');
$date = $r->date_checkIn;
$time = date('H:i:s');
$time = $r->time_checkIn;

改用Carbon::now()生成时间后，Clock Out操作开始出现重复记录问题。

控制器代码 #

Clock In方法 #

public function userClockIn(Request $r)
{
    $result = [];
    $result['status'] = false;
    $result['message'] = "something error";

    $users = User::where('staff_id', $r->staff_id)->select(['staff_id', 'date_checkIn', 'time_checkIn', 'location_checkIn'])->first();

    $mytime = Carbon::now();
    $date = $mytime->format('Y-m-d');
    $time = $mytime->format('H:i:s');

    $users->date_checkIn = $date;
    $users->time_checkIn = $time;
    $users->location_checkIn = $r->location_checkIn;

    $users->save();

    // Retrieve current data
    $currentData = $users->toArray();

    // Store current data into attendace record table
    $attendanceRecord = new AttendanceRecord();
    $attendanceRecord->fill($currentData);
    $attendanceRecord->save();

    $result['data'] = $users;
    $result['status'] = true;
    $result['message'] = "suksess add data";

    return response()->json($result);
}

Clock Out方法 #

public function userClockOut(Request $r)
{
    $result = [];
    $result['status'] = false;
    $result['message'] = "something error";

    $users = User::where('staff_id', $r->staff_id)->select(['staff_id', 'time_checkOut', 'location_checkOut'])->first();

    $mytime = Carbon::now();
    $date = $mytime->format('Y-m-d');
    $time = $mytime->format('H:i:s');

    $users->date_checkIn = $date;
    $users->time_checkOut = $time;
    $users->location_checkOut = $r->location_checkOut;

    // Save the updated data to the database
    AttendanceRecord::updateOrCreate(
        ['staff_id' => $users->staff_id, 'date_checkIn' => $date],
        $users->toArray()
    );

    // Retrieve current data
    $currentData = $users->toArray();

    // Store current data into attendace record table
    $attendanceRecord = new AttendanceRecord();
    $attendanceRecord->fill($currentData);
    $attendanceRecord->save();

    $result['data'] = $users;
    $result['status'] = true;
    $result['message'] = "suksess add data";

    return response()->json($result);
}

问题原因分析 #

1. 重复写入操作：Clock Out方法中同时执行了updateOrCreate和new AttendanceRecord()->save()两个写入逻辑。updateOrCreate本身已完成"更新或创建"的操作，后续手动新建并保存记录直接导致重复。
2. User模型查询字段缺失：Clock Out查询User时未包含date_checkIn字段，后续给$users->date_checkIn赋值当前日期属于未预加载字段的赋值，导致updateOrCreate的匹配条件（date_checkIn => 当前日期）与Clock In时的实际日期不匹配，触发新建记录。
3. 日期匹配逻辑错误：使用Carbon::now()的当前日期作为updateOrCreate的匹配条件，若Clock In和Clock Out跨天，会无法找到原有AttendanceRecord记录，进而新建一条，叠加重复写入操作导致双重重复。

修复方案 #

1. 移除重复的save操作：保留updateOrCreate即可，删除Clock Out方法中以下代码段：

// Retrieve current data
$currentData = $users->toArray();

// Store current data into attendace record table
$attendanceRecord = new AttendanceRecord();
$attendanceRecord->fill($currentData);
$attendanceRecord->save();

2. 修正User查询字段：查询User时包含date_checkIn字段，确保获取Clock In时的原始日期：

$users = User::where('staff_id', $r->staff_id)
    ->select(['staff_id', 'date_checkIn', 'time_checkOut', 'location_checkOut'])
    ->first();

3. 使用Clock In日期作为匹配条件：避免修改User的date_checkIn，直接用原有Clock In日期匹配AttendanceRecord：

// 移除不必要的date_checkIn赋值
// $users->date_checkIn = $date;

AttendanceRecord::updateOrCreate(
    ['staff_id' => $users->staff_id, 'date_checkIn' => $users->date_checkIn],
    [
        'time_checkOut' => $time,
        'location_checkOut' => $r->location_checkOut
    ]
);

内容的提问来源于stack exchange，提问作者jabs93