Group items by month and count them laravel

Question

Group items by month and count them laravel

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

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

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

I need it to return 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 índice means mês , and in the case of the month that has not yet arrived return 0. Can you do this with Eloquente ?

php laravel laravel-5.2

asked by anonymous 23.11.2016 / 18:56

2 answers

2

To return 0 you have to array to make the combination of months. Otherwise you can create a table for months and make 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);

23.11.2016 / 19:11

Error installing JWT / Laravel Should I use abstract class or interface?

score 3 · Accepted Answer

$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've already done a response with whereRaw .