如何计算和按月分组，月份为零

Question

I am trying to set up a small statistical module. I am looking for an array containing for each month the number of events of my 'evenement' table. With the query below I get the results but only if there is a result in the month. IF there is none, it does not appear. My request:

$data = DB::table("evenements")
        ->select(DB::raw('EXTRACT(MONTH FROM datedevenement) AS month, COUNT(id) as id'),
         DB::raw('ifnull(count(id),0) as id')
        )
         ->where('typeevenement_id', '1')
        ->whereYear('datedevenement', Carbon::now()->year)
        ->orderBy('datedevenement')

        ->groupBy(DB::raw('month'))
        ->get();



    return $data;

My evenement table :

id | datedevenement | typeevenement_id

I understand that my request can not invent months that do not exist. I wonder if carbon or laravel does not have something to list by month or year in a continuous way.

Result must be an array or collection with monthORyears->count(evenement)

Answer 1

Thanks you. With this 2 answers and a small serach i arrived to this solution (i have add somathing for order with current month for start point) :

$monthly_uploaded_product = Evenement::select(DB::raw('COUNT(id) as total, EXTRACT(MONTH FROM datedevenement) AS month')
    )
        ->where('typeevenement_id', '1')
        ->wheredate('datedevenement', '>=', Carbon::now()->lastOfMonth()->subyears(1))
       /// ->whereYear('datedevenement', Carbon::now()->year)
       ->groupBy(DB::raw('month'))

        ->get();

    $mois  = ["janvier", "fevrier", "mars", "avril", "mai", "juin", "juillet", "aout", "septembre", "octobre", "novembre", "decembre"];

    foreach ($mois as $mois99) {
        $arriveeparmoisrefuge[]= array(
            'mois' => $mois99,
            'total' => '0'
        );

    }
        foreach ($monthly_uploaded_product as $key) {
                $arriveeparmoisrefuge[$key->month - 1]['total'] = $key->total;//update each month with the total value
        }
    $sorted = collect($arriveeparmoisrefuge)->sortBy(function ($count, $month) {
        $currentMonth = (int) \Carbon\Carbon::now()->month;

        return ($month + (13 - $currentMonth - 1)) % 12;
    });

    return  $sorted;