Laravel Eloquent get results grouped by days

Question

I currently have a table of page_views that records one row for each time a visitor accesses a page, recording the user's ip/id and the id of the page itself. I should add that the created_at column is of type: timestamp, so it includes the hours/minutes/seconds. When I try groupBy queries, it does not group same days together because of the seconds difference.

created_at         page_id       user_id ==========         =======       ======= 10-11-2013            3            1 10-12 2013            5            5 10-13 2013            5            2 10-13 2013            3            4   ...                ...          ... 

I'd like to get results based on views/day, so I can get something like:

  date          views   ====          ===== 10-11-2013       15 10-12 2013       45   ...            ... 

I'm thinking I'll need to dig into DB::raw() queries to achieve this, but any insight would help greatly, thanks

Edit: Added clarification of created_at format.

Answer 1

I believe I have found a solution to this, the key is the DATE() function in mysql, which converts a DateTime into just Date:

DB::table('page_views')       ->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as views'))       ->groupBy('date')       ->get(); 

However, this is not really an Laravel Eloquent solution, since this is a raw query.The following is what I came up with in Eloquent-ish syntax. The first where clause uses carbon dates to compare.

$visitorTraffic = PageView::where('created_at', '>=', \Carbon\Carbon::now->subMonth())                             ->groupBy('date')                             ->orderBy('date', 'DESC')                             ->get(array(                                 DB::raw('Date(created_at) as date'),                                 DB::raw('COUNT(*) as "views"')                             )); 

Answer 2

You can use Carbon (integrated in Laravel)

// Carbon use Carbon\Carbon;    $visitorTraffic = PageView::select('id', 'title', 'created_at')     ->get()     ->groupBy(function($date) {         return Carbon::parse($date->created_at)->format('Y'); // grouping by years         //return Carbon::parse($date->created_at)->format('m'); // grouping by months     });