php Laravel Eloquent 获取按天分组的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20603075/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel Eloquent get results grouped by days
提问by jct
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 格式的说明。
回答by dede
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
});
回答by jct
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 转换为 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.
然而，这并不是真正的 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"')
));
回答by Quins
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();
回答by ako
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();
回答by Haidren Amalia
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.
希望这会帮助你。
回答by henryallsuch
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();
回答by Shanu Singh
PageView::select('id','title', DB::raw('DATE(created_at) as date'))
->get()
->groupBy('date');
回答by gskhanal
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)'));
回答by J.T. Grimes
Warning: untested code.
警告：未经测试的代码。
$dailyData = DB::table('page_views')
->select('created_at', DB::raw('count(*) as views'))
->groupBy('created_at')
->get();
回答by btaylor507
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.
这是我获取最近十天并使用同一天 created_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
希望这可以帮助
