Query to get a value next to all months of the year

I have this currently working. Just interested to see if I can clean it up at all. I'm sure there's an easier way to intersect the months with the month from the query. I just set them all to 0 then replaced the values based on the month key.

$report1_data = Application::select([
    DB::raw('MONTH(created_at) AS month'),
    DB::raw('COUNT(*) AS count'),
])
    ->whereBetween('created_at', [Carbon::now()->startOfYear(), Carbon::now()->endOfYear()])
    ->groupBy('month')
    ->orderBy('month', 'ASC')
    ->get()
    ->toArray();

$months = range(1, 12);
$month_values = [];
foreach ($months as $month) {
    $month_values[$month] = 0;
}

foreach ($report1_data as $key => $single_month) {
    $month_values[$single_month['month']] = $single_month['count'];
}

$filtered_report1_data = json_encode(array_values($month_values)); // spits out [0, 23, 76, 12, 0, 4, 32, 0, 0, 56, 89, 0]

usman

10 years ago

Best Answer

Level 27

@bashy for a collection that has the following data items:

=> Illuminate\Support\Collection {#822
     all: [
       [
         "month" => 4,
         "count" => 10,
       ],
       [
         "month" => 6,
         "count" => 8,
       ],
       [
         "month" => 10,
         "count" => 11,
       ],
     ],
   }

You can achieve the same result using (code is copied form the terminal):

$array_months = $collection->pluck('count', 'month')->toArray() + array_fill(1, 12, 0);
=> [
     4 => 10,
     6 => 8,
     10 => 11,
     1 => 0,
     2 => 0,
     3 => 0,
     5 => 0,
     7 => 0,
     8 => 0,
     9 => 0,
     11 => 0,
     12 => 0,
   ]
>>> ksort($array_months);
=> true
>>> $array_months 
=> [
     1 => 0,
     2 => 0,
     3 => 0,
     4 => 10,
     5 => 0,
     6 => 8,
     7 => 0,
     8 => 0,
     9 => 0,
     10 => 11,
     11 => 0,
     12 => 0,
   ]
>>>

Usman.

bashy

10 years ago

Level 65

@usman Amazing thank you! I was trying to think about how to do it and I was thinking too complex. I'll mark it as best answer :)

usman

10 years ago

Level 27

@bashy You are welcome :)!

Please or to participate in this conversation.