php - 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', '>=', CarbonCarbon::now->subMonth())
                            ->groupBy('date')
                            ->orderBy('date', 'DESC')
                            ->get(array(
                                DB::raw('Date(created_at) as date'),
                                DB::raw('COUNT(*) as "views"')
                            ));