Laravel Eloquent 补全月份数据的实用方法

理解数据缺失的根本原因

在使用 SQL 的 GROUP BY 子句进行按月统计时，例如统计每月删除的用户总数，数据库只会对实际存在的记录进行分组。这意味着，如果某个月份没有任何符合条件的记录，该月份将不会出现在最终的查询结果中。例如，如果1月有数据，2月没有，3月有数据，那么查询结果只会显示1月和3月的数据，2月的数据则会“跳过”，这对于需要连续时间序列数据的图表展示来说，是不可接受的。

初始查询示例

假设我们有一个 User 模型，并希望统计每月被删除的用户数量：

use App\Models\User;
use Carbon\Carbon;

$data = User::selectRaw('DATE_FORMAT(deleted_at, "%Y-%m") as date, COUNT(*) as total')
                ->withTrashed() // 包含软删除的用户
                ->whereNotNull('deleted_at') // 只统计已删除的用户
                ->groupByRaw('DATE_FORMAT(deleted_at, "%Y-%m")')
                ->get();

上述查询会返回一个集合，其中包含 date (格式为 YYYY-MM) 和 total 字段。如果2021年2月没有用户被删除，那么 2021-02 将不会出现在 $data 集合中。

采用后处理策略填充缺失月份

为了解决数据不连续的问题，最推荐且最易于维护的方法是在获取数据库结果后，利用 PHP 和 Carbon 库进行后处理。这种方法避免了在 SQL 层面构建复杂的日历表或使用递归 CTE，从而简化了逻辑并提高了可读性。

核心思想是：

1. 确定一个起始日期和一个结束日期，定义我们感兴趣的时间范围。
2. 遍历这个时间范围内的每一个月份。
3. 对于每个月份，检查它是否已存在于从数据库获取的数据集合中。
4. 如果不存在，则创建一个新的数据项，将该月份的 total 值设为0，并将其添加到集合中。

步骤详解与代码实现

首先，定义数据的时间范围。你可以根据业务需求设定起始日期，例如从一年以前开始，或者从数据库中最早的记录日期开始。结束日期通常是当前日期。

use Carbon\Carbon;
use Illuminate\Support\Collection; // 确保引入 Collection 类

// 获取原始数据（如上文所示）
$data = User::selectRaw('DATE_FORMAT(deleted_at, "%Y-%m") as date, COUNT(*) as total')
                ->withTrashed()
                ->whereNotNull('deleted_at')
                ->groupByRaw('DATE_FORMAT(deleted_at, "%Y-%m")')
                ->get();

// 定义时间范围
$startDate = Carbon::parse('2021-01-01'); // 示例：从2021年1月开始
// 或者：$startDate = User::orderBy('deleted_at', 'asc')->first()->deleted_at->startOfMonth(); // 从最早的删除日期开始
$endDate = Carbon::now()->endOfMonth(); // 结束日期为当前月份的月末

// 创建一个可复用的函数来填充缺失月份
function fillEmptyMonths(Collection $data, Carbon $start, Carbon $end): Collection
{
    $loopDate = $start->copy()->startOfMonth(); // 从起始月份的第一天开始循环

    // 遍历从起始月份到结束月份的所有月份
    // 使用 diffInMonths 确保循环覆盖所有月份
    for ($months = 0; $months <= $start->diffInMonths($end); $months++) {
        $currentMonthFormat = $loopDate->format('Y-m');

        // 检查当前月份是否已存在于数据集合中
        if ($data->where('date', '=', $currentMonthFormat)->isEmpty()) {
            // 如果不存在，则创建一个新的 stdClass 对象作为占位符
            $row = new \stdClass();
            $row->date = $currentMonthFormat;
            $row->total = 0; // 设置默认值为 0
            $data->push($row); // 将新创建的行添加到集合中
        }

        $loopDate->addMonth(); // 移动到下一个月
    }

    // 最后，为了确保图表数据按时间顺序排列，对集合进行排序
    return $data->sortBy('date')->values();
}

// 调用函数填充数据
$filledData = fillEmptyMonths($data, $startDate, $endDate);

// $filledData 现在包含所有月份的数据，缺失月份的 total 为 0
// 例如：
// [
//     { "date": "2021-01", "total": 15 },
//     { "date": "2021-02", "total": 0 },
//     { "date": "2021-03", "total": 22 },
//     // ... 更多月份
// ]

代码解析

• $start->copy()->startOfMonth(): 确保我们操作的是 Carbon 实例的副本，并且将日期设置为月份的第一天，以便进行准确的月份比较和递增。
• $start->diffInMonths($end): 计算起始日期和结束日期之间相隔的完整月份数，这决定了循环的次数，确保覆盖所有月份。
• $data->where('date', '=', $currentMonthFormat)->isEmpty(): 这是检查当前月份是否已存在于原始数据集合中的关键。where() 方法在这里用于过滤集合，isEmpty() 判断过滤后的结果是否为空。
• new \stdClass(): 当一个月份的数据缺失时，我们创建一个匿名的标准 PHP 对象 (stdClass) 来作为占位符。它的结构与数据库返回的对象相似，包含 date 和 total 属性。
• $data->push($row): 将新创建的占位符对象添加到原始数据集合中。
• $loopDate->addMonth(): 将循环日期推进到下一个月。
• $data->sortBy('date')->values(): 在填充所有缺失月份后，原始集合的顺序可能被打乱。为了保证数据按时间顺序排列（这对于图表展示至关重要），我们使用 sortBy('date') 对集合进行排序，并使用 values() 重置集合的键。

注意事项与扩展

1. 数据类型一致性: 确保填充的 total 字段的数据类型与数据库返回的 total 字段类型兼容（例如，都为整数）。
2. 起始日期选择: startDate 的选择应根据你的业务需求。你可以硬编码一个日期，或者从数据库中查询最早的记录日期。
3. 结束日期选择: endDate 通常是当前日期或用户选择的某个日期。
4. 时间粒度: 本教程以“月”为粒度进行填充。如果你需要按“天”或“年”填充，可以修改 addMonth() 为 addDay() 或 addYear()，并相应调整 DATE_FORMAT 和 diffInMonths 为 diffInDays 或 diffInYears。
5. 性能: 这种后处理方法通常比复杂的 SQL 查询更高效，因为它避免了数据库层面的复杂连接和聚合操作，尤其是在数据量不是特别庞大的情况下。它不会引起 N+1 查询问题，因为所有原始数据都在一次数据库查询中获取。
6. 通用性: fillEmptyMonths 函数是高度可复用的。你可以将其放置在辅助函数文件、服务类或自定义的集合宏中，以便在整个应用中方便地使用。

总结

通过在 Laravel 中结合 Carbon 库对数据库查询结果进行后处理，我们可以优雅且高效地解决按时间分组数据时因数据缺失导致的序列不连续问题。这种方法不仅逻辑清晰、易于维护，而且能生成完整的、适用于各种图表展示的连续时间序列数据，极大地提升了数据分析和可视化的用户体验。