Laravel统计clients表指定年份各月记录数 解决非首月计数为0问题

Laravel查询构造器为可变对象，你初始化的$clientYear是同一个查询实例，每次调用whereMonth都会在原有查询条件上叠加。执行完第一个月的查询后，查询条件已经变为whereYear('created_at', $selectedYear) and whereMonth('created_at', 1)，后续再叠加其他月份的条件，就变成同时要求月份等于1和2等不可能满足的逻辑，自然返回计数为0。

提供两种可选方案，第一种为最小改动修复原有逻辑，第二种为性能更优的推荐方案。

方案1：克隆查询实例避免条件叠加（最小改动） #

每次计数前克隆基础查询实例，保证每次查询的条件独立，不会互相叠加：

public function clientsCount(Request $request)
{
    $selectedYear = $request->selectedYear ?? now()->format('Y');

    $orderedMonthsData = [
        1 => "January ". $selectedYear,
        2 => "February ". $selectedYear,
        3 => "March ". $selectedYear,
        4 => "April ". $selectedYear,
        5 => "May ". $selectedYear,
        6 => "June ". $selectedYear,
        7 => "July ". $selectedYear,
        8 => "August ". $selectedYear,
        9 => "September ". $selectedYear,
        10 => "October ". $selectedYear,
        11 => "November ". $selectedYear,
        12 => "December ". $selectedYear
    ];

    $clientYearBaseQuery = Client::query()->whereYear('created_at', $selectedYear);

    $orderedClientsData = [
        1 => (clone $clientYearBaseQuery)->whereMonth('created_at', 1)->count(),
        2 => (clone $clientYearBaseQuery)->whereMonth('created_at', 2)->count(),
        3 => (clone $clientYearBaseQuery)->whereMonth('created_at', 3)->count(),
        4 => (clone $clientYearBaseQuery)->whereMonth('created_at', 4)->count(),
        5 => (clone $clientYearBaseQuery)->whereMonth('created_at', 5)->count(),
        6 => (clone $clientYearBaseQuery)->whereMonth('created_at', 6)->count(),
        7 => (clone $clientYearBaseQuery)->whereMonth('created_at', 7)->count(),
        8 => (clone $clientYearBaseQuery)->whereMonth('created_at', 8)->count(),
        9 => (clone $clientYearBaseQuery)->whereMonth('created_at', 9)->count(),
        10 => (clone $clientYearBaseQuery)->whereMonth('created_at', 10)->count(),
        11 => (clone $clientYearBaseQuery)->whereMonth('created_at', 11)->count(),
        12 => (clone $clientYearBaseQuery)->whereMonth('created_at', 12)->count(),
    ];

    dd($orderedMonthsData, $orderedClientsData);
}

方案2：一次性分组统计（性能更优，推荐） #

方案1会执行12次数据库查询，数据量大时性能较差。你可以用分组统计一次性拿到所有月份的计数，再补全没有数据的月份即可：

public function clientsCount(Request $request)
{
    $selectedYear = $request->selectedYear ?? now()->format('Y');

    // 初始化月份标签和默认计数为0的数组
    $orderedMonthsData = [];
    $orderedClientsData = array_fill(1, 12, 0);
    for ($i = 1; $i <= 12; $i++) {
        $monthName = date('F', mktime(0, 0, 0, $i, 1));
        $orderedMonthsData[$i] = "{$monthName} {$selectedYear}";
    }

    // 一次性统计所有有数据的月份的记录数
    $monthlyCounts = Client::query()
        ->whereYear('created_at', $selectedYear)
        ->selectRaw('MONTH(created_at) as month, count(*) as count')
        ->groupBy('month')
        ->pluck('count', 'month');

    // 把查询结果填充到计数数组中
    foreach ($monthlyCounts as $month => $count) {
        $orderedClientsData[$month] = $count;
    }

    dd($orderedMonthsData, $orderedClientsData);
}

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