How to include "zero" / "0" results in COUNT aggregate with eloquent

In @drehimself course Laravel Cookbook Ep 1 he shows how to get the count for each month of the year.

The only issue is that if you have a month with no data, the array will not return data for that month.

array:6 [▼
  1 => 90
  2 => 81
  3 => 94
  4 => 69
  5 => 81
  7 => 57
]
"6" or "June" has no value.

This is how you can manipulate the array.

If anyone has a better solution, you are more than welcome to paste it here.

migsAV

3 years ago

Best Answer

Level 31

$months = [
    1 => "Jan",
    2 => "Feb",
    3 => "Mar",
    4 => "Apr",
    5 => "May",
    6 => "Jun",
    7 => "Jul",
    8 => "Aug",
    9 => "Sep",
    10 => "Oct",
    11 => "Nov",
    12 => "Dec",
];

$thisYearOrders = Order::query()
    ->whereYear('created_at', date('Y'))
    ->selectRaw('month(created_at) as month')
    ->selectRaw('count(*) as count')
    ->groupBy('month')
    ->orderBy('month')
    ->pluck('count', 'month')
    ->toArray();


$thisYearMonths = collect($months)->map(function ($value, $key) use ($thisYearOrders) {
    return $thisYearOrders[$key] ?? 0;
});

$lastYearOrders = Order::query()
    ->whereYear('created_at', date('Y') - 1)
    ->selectRaw('month(created_at) as month')
    ->selectRaw('count(*) as count')
    ->groupBy('month')
    ->orderBy('month')
    ->pluck('count', 'month')
    ->toArray();

$lastYearMonths = collect($months)->map(function ($value, $key) use ($lastYearOrders) {
    return $lastYearOrders[$key] ?? 0;
});

then in your data array for your graph you use

datasets: [{
	label: 'Last Year Orders',
	data: {{ $lastYearMonths->values() }},
} , {
	label: 'This Year Orders',
	data: {{ $thisYearMonths->values() }},
 }]

2 likes

Please or to participate in this conversation.