使用 Laravel 8 查询填充缺失月份的 SQL 数据

使用 Laravel 8 查询填充缺失月份的 SQL 数据

laravel 8 项目中,我们经常需要统计按月分组的数据,例如每月删除用户总数。然而,当某些月份没有数据时,查询结果会跳过这些月份,导致图表数据不完整。本文将介绍一种在查询结果后进行后处理的方法,通过循环遍历指定时间范围内的月份,将缺失月份的数据补全,确保图表数据完整性。

首先,回顾一下原始的查询语句:

$data = User::selectRaw('date_FORMAT(created_at, "%Y-%m") as date, COUNT(*) as total')                 ->groupByRaw('DATE_FORMAT(created_at, "%Y-%m")')                 ->withTrashed()                 ->whereNotNull('deleted_at')                 ->get();

这段代码会返回一个包含 date (yyYY-MM 格式) 和 total (删除用户总数) 的集合。如果某个月份没有删除用户,那么这个月份就不会出现在结果集中。

解决这个问题的关键在于,在查询结果返回后,对结果进行后处理,填充缺失的月份数据。以下是一种实现方法:

use carbonCarbon; use IlluminateSupportCollection; use stdClass;  // 假设 $data 是上面查询语句返回的结果  $start = Carbon::today()->subYear(); // 设置起始日期,例如一年前 $end = Carbon::today(); // 设置结束日期,例如今天  $loop = $start->copy();  while ($loop->lessThanOrEqualTo($end)) {     $exists = $data->first(function($item) use ($loop) {         return $item->date == $loop->format('Y-m');     });      if (!$exists) {         $row = new stdClass();         $row->date = $loop->copy()->format('Y-m');         $row->total = 0;         $data->push($row);     }      $loop->addMonth(); // 循环到下个月 }

这段代码首先定义了起始日期和结束日期,然后循环遍历这两个日期之间的每个月。对于每个月,它会检查 $data 集合中是否存在该月份的数据。如果不存在,则创建一个新的对象,包含 date 和 total 属性,并将 total 设置为 0,然后将该对象添加到 $data 集合中。

为了提高代码的可重用性,可以将上述逻辑封装成一个函数:

use CarbonCarbon; use IlluminateSupportCollection; use stdClass;  function fillEmptyMonths(Collection $data, Carbon $start, Carbon $end): Collection {     $loop = $start->copy();      // Loop using diff in months rather than running comparison over and over.     for ($months = 0; $months <= $start->diffInMonths($end); $months++) {          if ($data->where('date', '=', $loop->format('Y-m'))->isEmpty()) {             $row = new stdClass();             $row->date = $loop->copy()->format('Y-m');             $row->total = 0;             $data->push($row);         }          $loop->addMonth();     }      return $data; }

使用这个函数非常简单:

$data = User::selectRaw('DATE_FORMAT(created_at, "%Y-%m") as date, COUNT(*) as total')                 ->groupByRaw('DATE_FORMAT(created_at, "%Y-%m")')                 ->withTrashed()                 ->whereNotNull('deleted_at')                 ->get();  $start = Carbon::today()->subYear(); $end = Carbon::today();  $data = fillEmptyMonths($data, $start, $end);

注意事项:

  • 确保安装了 Carbon 库。可以使用 composer require nesbot/carbon 命令进行安装。
  • 起始日期和结束日期可以根据实际需求进行调整。
  • 可以根据需要扩展该函数,以支持不同的时间间隔(例如,天、年)。

总结:

通过在查询结果后进行后处理,我们可以轻松地填充缺失的月份数据,确保图表数据的完整性。这种方法简单易懂,并且具有很高的灵活性和可重用性。 这种方法避免了复杂的 sql 查询,使得代码更加清晰易于维护。

© 版权声明
THE END
喜欢就支持一下吧
点赞5 分享