Filling empty items in a date sequence with Laravel's query builder

Im using Chart.js to show the number of bookings for each month. This is my query. It is working fine:

$graph = DB::table('bookings')
    ->selectRaw('MONTHNAME(created_at) as month')
    ->selectRaw('DATE_FORMAT(created_at, "%M %Y") as monthNum')
    ->selectRaw('count(*) as totalbook'))
    ->groupBy('monthNum')
    ->orderBy('created_at')
    ->get();

My problem is if there is no booking in a month then it doesnt show anything. No zero value. On the chart I get the values like

• January: 23
• February: 34
• April: 23
• May: 25
• July: 42

But March and June are not shown on the chart at all. How can I get a zero value for months where there is no booking?

MySQL will not fill in the months that aren't available, it will simply group by what you have available and then give you those results.

What you could do is use DatePeriod and Laravel's Collection class:

$results = DB::table('bookings')
    ->selectRaw("DATE_FORMAT(created_At,'%Y-%m-01') as monthNum, count(*) as totalbook")
    ->orderBy('monthNum')
    ->groupBy('monthNum')
    ->get();

$results = collect($results)->keyBy('monthNum')->map(function ($item) {
    $item->monthNum = \Carbon\Carbon::parse($item->monthNum);

    return $item;
});

$periods = new DatePeriod($results->min('monthNum'), \Carbon\CarbonInterval::month(), $results->max('monthNum')->addMonth());

$graph = array_map(function ($period) use ($results) {

    $month = $period->format('Y-m-d');

    return (object)[
        'monthNum'  => $period->format('M Y'),
        'totalbook' => $results->has($month) ? $results->get($month)->totalbook : 0,
    ];

}, iterator_to_array($periods));

Please note I have updated monthNum in the query as it will be reformatted later.

Also, you should need to use the blade raw tags ({!! !!}) for this as {{ $dat->totalbook }} should work absolutely fine.

Hope this helps!