php Laravel Eloquent，按月/年分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40529355/
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, group by month/year
提问by TheShun
i'm trying to group my data by month and year.
我正在尝试按月份和年份对我的数据进行分组。
$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();
The output is :
输出是：
{
"data": 19215,
"year": 2016,
"month": 10
},
if i group only by month, i don't know from which year belong this month, my expected output is :
如果我只按月分组，我不知道这个月属于哪一年，我的预期输出是：
{
"clicks": 19215,
"month": 11-2016,
},
{
"clicks": 11215,
"month": 12-2016,
},
},
i want to do it in sql, not in php.
我想用 sql 来做，而不是用 php。
回答by Amit Gupta
You can try as:
您可以尝试如下：
->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"), DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();
回答by Brane
If you want to group your results for example by year, you can use closure where you will parse your datetime to desired format (year or month).
如果您想按年份对结果进行分组，则可以使用闭包将日期时间解析为所需的格式（年或月）。
Here is example of the code:
以下是代码示例：
$res= ModelName::where('someColumn','test')
->get()
->groupBy(function($val) {
return Carbon::parse($val->created_at)->format('Y');
});
Note: This is solution based on eloquent, and yes, it not the best approach, but as the question is, this is using eloquent. If you want faster way, you can do it with raw query.
注意：这是基于 eloquent 的解决方案，是的，它不是最好的方法，但问题是，这是使用 eloquent。如果您想要更快的方式，您可以使用原始查询来完成。
回答by otim fredrick
$result = ModelName::selectRaw('year(created_at) year, monthname(created_at) month, count(*) data')
->groupBy('year', 'month')
->orderBy('year', 'desc')
->get();
回答by umar Aslam
$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year'),DB::raw('MONTH(created_at) month'))
->groupBy(DB::raw('YEAR(created_at)'), DB::raw('MONTH(created_at)'))
->get();
回答by ffegu
=>this might help someone in 2019
=>这可能对 2019 年的某人有所帮助
$date = "2019-06-16";
return date('m', strtotime($date)); //this return you 09;
so you can do foreach by this method , so you don't need to do the all sql things..
所以你可以通过这种方法来做 foreach，所以你不需要做所有的 sql 事情..
