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.

Solution 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"')
                            ));

Solution 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
    });

Solution 3

Here is how I do it. A short example, but made my query much more manageable

$visitorTraffic = PageView::where('created_at', '>=', \Carbon\Carbon::now->subMonth())
                        ->groupBy(DB::raw('Date(created_at)'))
                        ->orderBy('created_at', 'DESC')->get();

Solution 4

You can filter the results based on formatted date using mysql (See here for Mysql/Mariadb help) and use something like this in laravel-5.4:

Model::selectRaw("COUNT(*) views, DATE_FORMAT(created_at, '%Y %m %e') date")
    ->groupBy('date')
    ->get();

Solution 5

Like most database problems, they should be solved by using the database.

Storing the data you want to group by and using indexes you can achieve an efficient and clear method to solve this problem.

Create the migration

    $table->tinyInteger('activity_year')->unsigned()->index();
    $table->smallInteger('activity_day_of_year')->unsigned()->index();

Update the Model

<?php

namespace App\Models;

  use DB;
  use Carbon\Carbon;
  use Illuminate\Database\Eloquent\Model;

  class PageView extends Model
  {
  public function scopePerDay($query){

     $query->groupBy('activity_year');
     $query->groupBy('activity_day_of_year');

     return $query;

  }

  public function setUpdatedAt($value)
  {   
    $date = Carbon::now();

    $this->activity_year = (int)$date->format('y');
    $this->activity_day_of_year = $date->dayOfYear;

    return parent::setUpdatedAt($value);
 }

Usage

   $viewsPerDay = PageView::perDay()->get();

View more solutions