Laravel Group by Custom date Field

Question

I have have more than 16,000 data with corresponding date. I put these date in 'date' field in my table. I want to show these data group by months, I tried but I failed, my date format: date: "2015-08-31 02:12 "

My query:

$data = \App\Crawler::select([
        \DB::raw('count(id) as `count`'), 
        \DB::raw('Month(date) as month')
    ])->groupBy('month')->get(); 

I want:

{
  { 
    month: july
    count: 100
  },
  { 
    month: march
    count: 100
  }
}

Answer 1

You should be able to get the month name using MySQL's MONTHNAME() function.

This should work even if you don't have a full timestamp stored.

$data = \App\Crawler::select([
    \DB::raw('count(id) as `count`'), 
    \DB::raw('monthname(date) as month')
])->groupBy('month')->get();

[
    {
        count: 3,
        month: "July"
    },
    {
        count: 1,
        month: "March"
    }
]