Reputation: 1753

Laravel Eloquent get results grouped by days

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.

Upvotes: 69

Answers (18)

Hancie Phago

Reputation: 1

  $orderPayments = OrderPayment::select(
        DB::raw('MAX(id) as id'),
        DB::raw('SUM(net_total) as total_sales'),
        DB::raw('SUM(paid) as total_paid'),
        DB::raw('SUM(net_amount) as total_due_amount'),
        DB::raw("DATE_FORMAT(created_at, '%b %d, %Y') as Day"),

    )
        ->groupBy('Day')->get();

Upvotes: 0

DevonDahon

Reputation: 8360

Get pages views (or whatever), group my year-month-day and count for each date:

PageView::get()
        ->groupBy(fn($pv) => $pv->created_at->format('Y-m-d'))
        ->map(fn($date) => count($date));

Upvotes: 0

MHD Yasser Haddad

Reputation: 31

you can use the following code to group them by the date, since you have to parse both in the selection query and in the groupBy method:

$counts = DB::table('page_views')->select(DB::raw('DATE(created_at) as created_at'), DB::raw('COUNT(*) as views'))->
              groupBy(DB::raw('DATE(created_at)'))->
              get();

Upvotes: 1

Ramin eghbalian

Reputation: 2677

this way work properly and I used it in many projects! for example I get data of views the last 30 days:

$viewsData = DB::table('page_views')
    ->where('page_id', $page->id)
    ->whereDate('created_at', '>=', now()->subDays(30))
    ->select(DB::raw('DATE(created_at) as data'), DB::raw('count(*) as views'))
    ->groupBy('date')
    ->get();

If you want to get the number of views based on different IPs, you can use the DISTINCT like below :

$viewsData = DB::table('page_views')
    ->where('page_id', $page->id)
    ->whereDate('created_at', '>=', now()->subDays(30))
    ->select(DB::raw('DATE(created_at) as data'), DB::raw('count(DISTINCT user_ip) as visitors'))
    ->groupBy('date')
    ->get();

You can easily customize it by manipulating the columns name

Upvotes: 3

ako

Reputation: 2136

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();

Upvotes: 9

Shanu Singh

Reputation: 101

PageView::select('id','title', DB::raw('DATE(created_at) as date'))
          ->get()
          ->groupBy('date');

Upvotes: 5

Rubel Miya

Reputation: 51

You could also solve this problem in following way:

$totalView =  View::select(DB::raw('Date(read_at) as date'), DB::raw('count(*) as Views'))
        ->groupBy(DB::raw('Date(read_at)'))
        ->orderBy(DB::raw('Date(read_at)'))
        ->get();

Upvotes: 5

henryallsuch

Reputation: 169

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();

Upvotes: 10

btaylor507

Reputation: 211

Using Laravel 4.2 without Carbon

Here's how I grab the recent ten days and count each row with same day created_at timestamp.

$q = Spins::orderBy('created_at', 'desc')
->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"))
->take(10)
->get(array(
      DB::raw('Date(created_at) as date'),
      DB::raw('COUNT(*) as "views"')
  ));


foreach ($q as $day) {

  echo $day->date. " Views: " . $day->views.'<br>';

}

Hope this helps

Upvotes: 1

Sithira

Reputation: 1016

I know this is an OLD Question and there are multiple answers. How ever according to the docs and my experience on laravel below is the good "Eloquent way" of handling things

In your model, add a mutator/Getter like this

public function getCreatedAtTimeAttribute()
{
   return $this->created_at->toDateString();
}

Another way is to cast the columns in your model, populate the $cast array

$casts = [
   'created_at' => 'string'
]

The catch here is that you won't be able to use the Carbon on this model again since Eloquent will always cast the column into string

Hope it helps :)

Upvotes: 1

Ifnot

Reputation: 5103

I built a laravel package for making statistics : https://github.com/Ifnot/statistics

It is based on eloquent, carbon and indicators so it is really easy to use. It may be usefull for extracting date grouped indicators.

$statistics = Statistics::of(MyModel::query());

$statistics->date('validated_at');

$statistics->interval(Interval::$DAILY, Carbon::createFromFormat('Y-m-d', '2016-01-01'), Carbon::now())

$statistics->indicator('total', function($row) {
    return $row->counter;
});

$data = $statistics->make();

echo $data['2016-01-01']->total;

```

Upvotes: 0

Haidren Amalia

Reputation: 69

I had same problem, I'm currently using Laravel 5.3.

I use DATE_FORMAT()

->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"))

Hopefully this will help you.

Upvotes: 6

Mohammed Raad

Reputation: 1

in mysql you can add MONTH keyword having the timestamp as a parameter in laravel you can do it like this

Payement::groupBy(DB::raw('MONTH(created_at)'))->get();

Upvotes: 0

gskhanal

Reputation: 591

To group data according to DATE instead of DATETIME, you can use CAST function.

$visitorTraffic = PageView::select('id', 'title', 'created_at')
->get()
->groupBy(DB::raw('CAST(created_at AS DATE)'));

Upvotes: 2

Quins

Reputation: 813

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();

Upvotes: 32

dede

Reputation: 2673

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

Upvotes: 79

jct

Reputation: 1753

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

Upvotes: 101

J.T. Grimes

Reputation: 4272

Warning: untested code.

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

Upvotes: 1

Laravel Eloquent get results grouped by days

Answers (18)

Create the migration

Update the Model

Usage

Related Questions