Laravel collection – Group by months in a year and show the missing months from table with a count of 0

I am trying to create a collection in laravel which groups the current year into months with a supplied count.

So far so good.

The thing is, which indeed is obvious, collection is not showing the months that doesn’t exist in the table.

By using the following code

$logActivity = $customer->users()->select(DB::raw("DATE_FORMAT(last_login_at, '%b') as month, (COUNT(*)) as total"))
    ->whereBetween('last_login_at', [$this->loginStart, $this->loginEnd])
    ->whereYear('last_login_at', date('Y'))
    ->orderBy('last_login_at', 'ASC')
    ->groupBy('month')
    ->pluck('total', 'month');

I get an output as follows

array:5 [▼
  "Jan" => 1
  "Mar" => 3
  "Apr" => 5
  "May" => 2
  "Jun" => 1
]

The collection output I am trying to achieve is

array:12 [▼
  "Jan" => 1
  "Feb" => 0
  "Mar" => 3
  "Apr" => 5
  "May" => 2
  "Jun" => 0
  "Jul" => 0
  "Aug" => 0
  "Sep" => 0
  "Oct" => 0
  "Nov" => 0
  "Dec" => 0
]

Can anybody help me out?

Best regards.

Answer

As @Aless55 pointed out in the comments, I think you should create a default collection with all months mapped to 0, and then merge the collection with your query’s results. https://laravel.com/docs/8.x/collections#method-merge Basically when you merge the two collections, the values of the former will be overwritten by the values of the latter

$default = collect([
  "Jan" => 0,
  "Feb" => 0,
  "Mar" => 0,
  "Apr" => 0,
  "May" => 0,
  "Jun" => 0,
  "Jul" => 0,
  "Aug" => 0,
  "Sep" => 0,
  "Oct" => 0,
  "Nov" => 0,
  "Dec" => 0
]);

$result = $default->merge($logActivity);