使用 Laravel 查询数据并按天分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29753027/
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
Querying Data and Grouping by Day with Laravel
提问by TechKat
I run a website that stores images and users get a hotlink.
我运行一个存储图像的网站，用户得到一个热链接。
I want to be able to query records made in the last 7 days in the table containing the uploaded image data, extract the created_atcolumn only, and compile the data into an array, similar to making an archive list for a blog.
我希望能够在包含上传的图像数据的表中查询最近 7 天的记录，created_at仅提取列，并将数据编译成数组，类似于为博客制作存档列表。
I would like for the results to be presented like:
我希望结果显示如下：
[
'Sunday' => 5,
'Monday' => 45,
'Tuesday' => 452,
...
]
Where each number represents the number of records created on each day. As long as I can output an array like that, I can handle the Javascript side easily.
其中每个数字代表每天创建的记录数。只要我能输出这样的数组，我就可以轻松处理Javascript端。
Anybody have any suggestions?
有人有什么建议吗？
EDIT
编辑
This is the code I've tried so far:
这是我到目前为止尝试过的代码：
<?php
class Admin
{
public function getCreatedAtAttribute($value)
{
$this->attributes['created_at'] = Carbon::createFromFormat('Y-m-d H:i:s', $value);
}
public static function uploadsGraph()
{
$date = \Carbon\Carbon::now();
$uploads = Upload::select('created_at')->where('created_at', '>=', \Carbon\Carbon::now()->subWeek())->get();
foreach($uploads as $date)
{
echo $date->created_at . '<br>';
}
}
}
EDIT 2
编辑 2
Here's another version I tried, but that didn't work out well.
这是我尝试过的另一个版本，但效果不佳。
class Admin
{
public static function uploadsGraph()
{
$date = \Carbon\Carbon::now();
$uploadsByDay = DB::table('uploads')
->select(DB::raw('
YEAR(created_at) year,
MONTH(created_at) month,
MONTHNAME(created_at) month_name
'))
->groupBy('year')
->groupBy('month')
->orderBy('year', 'desc')
->orderBy('month', 'desc')
->get();
dd($uploadsByDay);
}
}
回答by Jeff Lambert
I'm assuming that the number next to each day of the week represents the number of records made on that day, with the entire dataset you want to query ranging only over the last 7 days.
我假设一周中每一天旁边的数字代表当天所做的记录数，您要查询的整个数据集仅在过去 7 天范围内。
The idea here is to select the count of items that were created on the same day (ignoring completely the timestamp portion of the created_atcolumn), so we can use DB::rawinside of a select()call to aggregate all of the entries that were created on a specific day and then restrict that dataset to only those created in the last week. Something like this ought to work:
这里的想法是选择在同一天创建的项目的计数（完全忽略created_at列的时间戳部分），因此我们可以DB::raw在select()调用内部使用来聚合在特定日期创建的所有条目和然后将该数据集限制为仅在上周创建的数据集。像这样的事情应该有效：
$data = Upload::select([
// This aggregates the data and makes available a 'count' attribute
DB::raw('count(id) as `count`'),
// This throws away the timestamp portion of the date
DB::raw('DATE(created_at) as day')
// Group these records according to that day
])->groupBy('day')
// And restrict these results to only those created in the last week
->where('created_at', '>=', Carbon\Carbon::now()->subWeeks(1))
->get()
;
$output = [];
foreach($data as $entry) {
$output[$entry->day] = $entry->count;
}
print_r($output);
Also note that I assumed this to be a 'rolling' week, where if todayhappens to be a Thursday, then the first date in the dataset will be the previous Thursday. It will not start on the most recent Sunday, if that is what you need. If it is, you can change the -where()condition to something like this:
另请注意，我假设这是一个“滚动”周，如果今天恰好是Thursday，那么数据集中的第一个日期将是上一个Thursday。如果您需要，它不会在最近的星期日开始。如果是，您可以将-where()条件更改为如下所示：
...
->where('created_at', '>=', Carbon\Carbon::parse('last sunday'))
...
回答by Pardeep Goyal
DB::table("clicks")
->select("id" ,DB::raw("(COUNT(*)) as total_click"))
->orderBy('created_at')
->groupBy(DB::raw("MONTH(created_at)"))
->get();
