Group items by month and count them

Question

I need to group and count all items by mês. Utilise Laravel 5.2, tried to do so:

$mes = date('m');
$vendas = Encomenda::where('FlgStEncomenda', 'O')
         ->group("MONTH(created_at)={$mes}")
         ->count();

I need you to come back like this:

[0] 5
[1] 10
[2] 4
[3] 11
[4] 107
[5] 110
[6] 120
[7] 5
[8] 103
[9] 104
[10] 102
[11] 0

Where the índice means the mês, and in the case of the month that has not yet arrived return 0. It has to do this with Eloquente?

Answer 1

$mes = date('m');
$vendas = Encomenda::where('FlgStEncomenda', 'O')
         ->groupBy(\DB::raw('MONTH(created_at)'))
         ->select(\DB::raw('MONTH(created_at) as Mes, count(*) as Quantidade'))   
         ->pluck('Mes','Quantidade');


$grafico = [1 => 0, 
            2 => 0, 
            3 => 0, 
            4 => 0, 
            5 => 0, 
            6 => 0, 
            7 => 0, 
            8 => 0, 
            9 => 0, 
            10 => 0, 
            11 => 0, 
            12 => 0];

foreach($vendas as $key => $value)
{
     $grafico[(int)$key] = (int)$value;
}

If you want to search by year:

$ano = date('Y');
$vendas = Encomenda::where('FlgStEncomenda', 'O')
         ->whereRaw("YEAR(created_at)={$ano}")
         ->groupBy(\DB::raw('MONTH(created_at)'))
         ->select(\DB::raw('MONTH(created_at) as Mes, count(*) as Quantidade'))   
         ->pluck('Mes','Quantidade');

_{I had already done an answer with whereRaw.}

Answer 2

To return 0 you have to array to do the months combo. Otherwise you can create a table for months and do a Join.

$vendas = Encomenda::select(DB::raw('MONTH(created_at) AS mes, count(id) AS qtd'))
          ->groupBy(DB::raw("MONTH(created_at)"))
          ->orderBy('mes')
          ->where('FlgStEncomenda', 'O')
          ->lists('mes', 'qtd')
          ->toArray();

$arrMeses = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12];

foreach($arrMeses as $key => $mes){
    if($mes == $vendas[$key]){
        $arrFoo[$mes] = $vendas[$key];
    }
    else{
        $arrFoo[$mes] = 0;
    }
}

dd($arrFoo);