Laravel中如何获取月份内缺失日期数据并补0

问题根源 #

你的现有查询以daily_enters作为主表做左连接，当某个日期没有对应daily_inputs或daily_enters记录时，该日期会被过滤掉无法返回。要实现所有日期都显示，需要以完整的日期序列为主表，左连接业务表来补全数据。

解决方案一：Laravel集合处理（简单直观） #

先获取有数据的记录，再和完整日期集合合并，补全缺失日期的crops_actual为0：

步骤1：生成目标月份的完整日期数组 #

use Carbon\Carbon;

// 定义目标月份，示例为2022年1月
$targetMonth = Carbon::create(2022, 1, 1);
$startDate = $targetMonth->copy()->startOfMonth();
$endDate = $targetMonth->copy()->endOfMonth();

// 生成该月所有日期的数组
$dates = [];
while ($startDate->lte($endDate)) {
    $dates[] = $startDate->toDateString();
    $startDate->addDay();
}

步骤2：查询已有数据并合并到完整日期集合 #

// 查询有记录的日期及对应的crops_actual
$existingRecords = DB::table('daily_enters')
    ->join('daily_inputs', 'daily_enters.daily_input_id', '=', 'daily_inputs.id')
    ->select('daily_inputs.date', DB::raw('sum(daily_enters.crops) as crops_actual'))
    ->whereIn('daily_inputs.date', $dates)
    ->where('daily_inputs.divisi', 'A')
    ->groupBy('daily_inputs.date')
    ->get()
    ->keyBy('date'); // 以日期为键，方便快速查找

// 构建包含所有日期的完整结果
$fullResult = collect($dates)->map(function ($date) use ($existingRecords) {
    return [
        'date' => $date,
        'crops_actual' => $existingRecords->has($date) ? $existingRecords[$date]->crops_actual : 0
    ];
});

// $fullResult即为包含所有日期的最终数据

解决方案二：纯SQL递归CTE实现（性能更优） #

利用MySQL的递归CTE生成完整日期序列，再左连接业务表，直接从数据库层面获取完整结果：

use Carbon\Carbon;

$targetMonth = Carbon::create(2022, 1, 1);
$startDateStr = $targetMonth->startOfMonth()->toDateString();
$endDateStr = $targetMonth->endOfMonth()->toDateString();

$fullResult = DB::select(DB::raw("
    WITH RECURSIVE date_range AS (
        SELECT ? AS date
        UNION ALL
        SELECT DATE_ADD(date, INTERVAL 1 DAY)
        FROM date_range
        WHERE date < ?
    )
    SELECT 
        dr.date,
        COALESCE(SUM(de.crops), 0) AS crops_actual
    FROM date_range dr
    LEFT JOIN daily_inputs di 
        ON dr.date = di.date 
        AND di.divisi = 'A'
    LEFT JOIN daily_enters de 
        ON de.daily_input_id = di.id
    GROUP BY dr.date
    ORDER BY dr.date
"), [$startDateStr, $endDateStr]);

关键说明 #

• RECURSIVE date_range：生成从起始日期到结束日期的所有日期序列。
• COALESCE(SUM(de.crops), 0)：当某个日期没有对应记录时，SUM(de.crops)会返回null，用COALESCE将其转换为0。
• 以date_range为主表左连接业务表，确保所有日期都会出现在结果中。

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