samedi 27 avril 2019

How to count and group by month or years with month at zero

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)



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire