Laravel Eloquent 获取按天分组的结果 [英] Laravel Eloquent get results grouped by days

问题描述

我目前有一个 page_views 表，每次访问者访问页面时记录一行，记录用户的 ip/id 和页面本身的 id.我应该补充一点，created_at 列的类型是:timestamp，所以它包括小时/分钟/秒.当我尝试 groupBy 查询时，由于秒数差异，它不会将同一天组合在一起.

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
  ...            ...

我想我需要深入研究 DB::raw() 查询来实现这一点，但任何见解都会有很大帮助，谢谢

I'm thinking I'll need to dig into DB::raw() queries to achieve this, but any insight would help greatly, thanks

添加了对 created_at 格式的说明.

Added clarification of created_at format.

推荐答案

我相信我已经找到了解决方案，关键是mysql中的DATE()函数，它将DateTime转换为Date:

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

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

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

这篇关于Laravel Eloquent 获取按天分组的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！