Laravel里程追踪器开发求助：基于上条里程数据计算行驶公里数

一、MySQL 实现方案 #

1. MySQL 8.0+（推荐，支持窗口函数） #

直接用LAG()窗口函数获取上一行的odometer_stop值，计算行驶里程：

SELECT 
    id,
    -- 首行用手动输入的odometer_start，后续行用上一行的odometer_stop
    IFNULL(LAG(odometer_stop) OVER (ORDER BY created_at ASC), odometer_start) AS odometer_start,
    odometer_stop,
    fuel,
    -- 计算行驶里程
    odometer_stop - IFNULL(LAG(odometer_stop) OVER (ORDER BY created_at ASC), odometer_start) AS km_travelled,
    created_at
FROM mileage_records;

如果是多车辆分开计算，加上PARTITION BY分组即可：

LAG(odometer_stop) OVER (PARTITION BY vehicle_id ORDER BY created_at ASC)

2. MySQL 5.x 兼容方案（无窗口函数） #

用用户变量存储上一行的里程值：

SELECT 
    id,
    @prev_stop AS odometer_start,
    odometer_stop,
    fuel,
    odometer_stop - @prev_stop AS km_travelled,
    -- 更新变量为当前行的结束里程，供下一行使用
    @prev_stop := odometer_stop
FROM mileage_records,
(SELECT @prev_stop := (SELECT odometer_start FROM mileage_records ORDER BY created_at ASC LIMIT 1)) AS init
ORDER BY created_at ASC;

二、Laravel 实现方案 #

1. 新增记录时自动填充odometer_start #

在模型的creating事件中自动获取上一条记录的结束里程作为当前记录的起始里程，不需要用户手动输入（首条记录除外）：
首先在MileageRecord模型中添加如下代码：

protected static function booted()
{
    static::creating(function ($record) {
        // 如果是多车辆，追加 ->where('vehicle_id', $record->vehicle_id) 条件
        $lastRecord = static::latest('created_at')->first();
        if ($lastRecord) {
            $record->odometer_start = $lastRecord->odometer_stop;
        }
        // 自动计算行驶里程
        $record->km_travelled = $record->odometer_stop - $record->odometer_start;
    });
}

新增记录时只需要传入odometer_stop和fuel即可，首条记录需要额外传入手动填写的odometer_start。

2. 查询时动态计算行驶里程 #

如果不想在数据库存储km_travelled字段，查询时动态计算：

// MySQL 8.0+ 版本
$records = MileageRecord::select([
    '*',
    DB::raw('IFNULL(LAG(odometer_stop) OVER (ORDER BY created_at ASC), odometer_start) as calculated_odometer_start'),
    DB::raw('odometer_stop - IFNULL(LAG(odometer_stop) OVER (ORDER BY created_at ASC), odometer_start) as km_travelled'),
])->get();

如果数据量较小，也可以用Laravel集合处理，兼容所有数据库版本：

$records = MileageRecord::oldest('created_at')->get();
$prevStop = $records[0]->odometer_start;
$records->transform(function ($item) use (&$prevStop) {
    $item->km_travelled = $item->odometer_stop - $prevStop;
    $prevStop = $item->odometer_stop;
    return $item;
});

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