Laravel Eloquent, group by month/year

Question

i'm trying to group my data by month and year.

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
           ->groupby('year','month')
           ->get();

The output is :

{
"data": 19215,
"year": 2016,
"month": 10
},

if i group only by month, i don't know from which year belong this month, my expected output is :

{
"clicks": 19215,
"month": 11-2016,
},
{
"clicks": 11215,
"month": 12-2016,

},

i want to do it in sql, not in php.

Answer 1

If you want to group your results for example by year, you can use closure where you will parse your datetime to desired format (year or month).

Here is example of the code:

$res= ModelName::where('someColumn','test')
      ->get()
      ->groupBy(function($val) {
      return Carbon::parse($val->created_at)->format('Y');
});

Answer 2

You can try as:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();