Laravel中如何合并同一患者的多条预约记录（合并service.id）

需求说明 #

需要合并除service.id外其他数据完全一致的记录，将这些记录的service.id合并为逗号分隔的字符串。

示例输入表 #

当前使用的Eloquent查询代码 #

$reservations = Reservation::
        selectRaw('patients.*,services.*, reservations.id as id, reservations.*')
        ->join('patients', 'patients.id', '=', 'reservations.patient_id')
        ->join('services', 'services.id', '=', 'reservations.service_id')
        ->when($request->date, function ($query, $date) {
            return $query->whereDate('reservations.date', $date);
        })
        ->where('status',$status)
        ->orderBy('date','ASC')
        ->orderBy('time','ASC')
        ->paginate(5);
$reservations->appends($request->all());
return view('admin.reservations.manage', compact('reservations'));

期望输出表 #

Reservations表Schema #

Schema::create('reservations', function (Blueprint $table) {
    $table->id();
    $table->integer('patient_id');
    $table->foreign('patient_id')->references('id')->on('patients');
    $table->integer('user_id');
    $table->foreign('user_id')->references('id')->on('users');
    $table->integer('service_id');
    $table->foreign('service_id')->references('id')->on('services');
    $table->date('date');
    $table->time('time');
    $table->string('status')->default('Pending');
    $table->timestamp('cancelled_at')->nullable();
    $table->string('cancelled_by')->nullable();
    $table->timestamp('cleared_at')->nullable();
    $table->timestamps();
});

解决方案 #

要实现这个需求，核心是用SQL的GROUP_CONCAT函数拼接service ID，同时按所有需要去重的字段分组。具体调整如下：

1. 修改查询语句 #

替换原有的selectRaw和分组逻辑，确保按所有重复判断字段分组，并用GROUP_CONCAT拼接service ID：

$reservations = Reservation::
    selectRaw('
        patients.*,
        reservations.id as reservation_id,
        reservations.date,
        reservations.time,
        reservations.status,
        reservations.user_id,
        reservations.cancelled_at,
        reservations.cancelled_by,
        reservations.cleared_at,
        reservations.created_at,
        reservations.updated_at,
        GROUP_CONCAT(DISTINCT services.id SEPARATOR \', \') as service_ids
    ')
    ->join('patients', 'patients.id', '=', 'reservations.patient_id')
    ->join('services', 'services.id', '=', 'reservations.service_id')
    ->when($request->date, function ($query, $date) {
        return $query->whereDate('reservations.date', $date);
    })
    ->where('status', $status)
    // 按所有需要去重的字段分组，避免ONLY_FULL_GROUP_BY模式报错
    ->groupBy(
        'patients.id',
        'reservations.date',
        'reservations.time',
        'reservations.status',
        'reservations.user_id',
        'reservations.cancelled_at',
        'reservations.cancelled_by',
        'reservations.cleared_at',
        'reservations.created_at',
        'reservations.updated_at'
    )
    ->orderBy('reservations.date', 'ASC')
    ->orderBy('reservations.time', 'ASC')
    ->paginate(5);

$reservations->appends($request->all());
return view('admin.reservations.manage', compact('reservations'));

2. 关键注意事项 #

• 分组字段完整性：必须将所有作为“重复判断依据”的字段加入groupBy，否则在开启ONLY_FULL_GROUP_BY的SQL环境下会报错。
• 去重处理：DISTINCT关键字可以避免同一个service ID被重复拼接（如果存在重复关联的情况）。
• 视图适配：前端视图中需要将原本调用$reservation->service->id的地方，改为$reservation->service_ids来显示拼接后的字符串。
• 分页兼容性：GROUP_CONCAT与分页功能兼容，但要注意分组后的结果数量是否符合分页预期。

3. 可选优化：利用Eloquent关联简化查询 #

如果已在模型中定义了关联关系，可以简化查询逻辑：

// 假设Reservation模型已定义patient()关联
$reservations = Reservation::with(['patient'])
    ->selectRaw('
        reservations.*,
        GROUP_CONCAT(DISTINCT services.id SEPARATOR \', \') as service_ids
    ')
    ->join('services', 'services.id', '=', 'reservations.service_id')
    ->when($request->date, function ($query, $date) {
        return $query->whereDate('reservations.date', $date);
    })
    ->where('status', $status)
    ->groupBy('reservations.patient_id', 'reservations.date', 'reservations.time', 'reservations.status', 'reservations.user_id')
    ->orderBy('reservations.date', 'ASC')
    ->orderBy('reservations.time', 'ASC')
    ->paginate(5);

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