Laravel Eloquent按天分组结果

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.

我目前有一个page_views表，每次访问者访问一个页面时记录一行，记录用户的ip / id和页面本身的id。我应该补充一点，created_at列的类型为：timestamp，因此它包含小时/分钟/秒。当我尝试groupBy查询时，由于秒差异，它不会将相同的日期组合在一起。

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

我想我需要深入研究DB :: raw（）查询才能实现这一目标，但任何见解都会有很大帮助，谢谢

Edit: Added clarification of created_at format.

编辑：添加了created_at格式的说明。

12 个解决方案

#1

You can use Carbon (integrated in Laravel)

你可以使用Carbon（集成在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
    });

#2

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

我相信我找到了一个解决方案，关键是mysql中的DATE（）函数，它将DateTime转换为日期：

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.

但是，这不是一个真正的Laravel Eloquent解决方案，因为这是一个原始查询。以下是我在Eloquent-ish语法中提出的。第一个where子句使用碳日期进行比较。

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

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

#4

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

要根据DATE而不是DATETIME对数据进行分组，可以使用CAST功能。

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

#5

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

我有同样的问题，我现在正在使用Laravel 5.3。

I use DATE_FORMAT()

我用DATE_FORMAT（）

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

Hopefully this will help you.

希望这会对你有所帮助。

#6

Warning: untested code.

警告：未经测试的代码。

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

#7

Using Laravel 4.2 without Carbon

使用不含碳的Laravel 4.2

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

以下是我抓住最近十天的情况，并计算每天create_at时间戳的每一行。

$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

希望这可以帮助

#8

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

在mysql中，您可以添加MONTH关键字，将时间戳作为laravel中的参数，您可以这样做

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

#9

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

我构建了一个用于统计的laravel包：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;

```

#10

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

我知道这是一个老问题，有多个答案。根据文档和我在laravel上的经验，如何处理事物的良好“雄辩的方式”

In your model, add a mutator/Getter like this

在你的模型中，像这样添加一个mutator / Getter

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

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

另一种方法是在模型中强制转换列，填充$ cast数组

$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

这里的问题是你不能再在这个模型上使用Carbon，因为Eloquent总是将列转换为字符串

Hope it helps :)

希望能帮助到你：）

#11

I'm solve problem in this 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();

#12

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:

您可以使用mysql根据格式化日期过滤结果（请参阅此处获取Mysql / Mariadb帮助）并在laravel-5.4中使用类似的内容：

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

#1