blocks|key|1683802|text|警告:未测试的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1683803|$dailyData+=+DB::table('page_views')
++++->select('created_at',+DB::raw('count(*)+as+views'))
++++->groupBy('created_at')
++++->get();|code-block|syntax|javascript|1683804|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Warning: untested code.

<pre><code>$dailyData = DB::table('page_views')
 -&gt;select('created_at', DB::raw('count(*) as views'))
 -&gt;groupBy('created_at')
 -&gt;get();
</code></pre>

blocks|key|1683871|text|我相信我已经找到了解决这个问题的方法，关键是mysql中的DateTime+()函数，它将日期转换为日期：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1683872|DB::table('page_views')
++++++->select(DB::raw('DATE(created_at)+as+date'),+DB::raw('count(*)+as+views'))
++++++->groupBy('date')
++++++->get();|code-block|syntax|javascript|1683873|然而，这并不是真正的Laravel雄辩的解决方案，因为这是一个原始的query.The，下面是我用雄辩的语法想出来的。第一个where子句使用碳日期进行比较。|1683874|$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"')
++++++++++++++++++++++++++++));|1683875|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>DB::table('page_views')
 -&gt;select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as views'))
 -&gt;groupBy('date')
 -&gt;get();
</code></pre>

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.

<pre><code>$visitorTraffic = PageView::where('created_at', '&gt;=', \Carbon\Carbon::now-&gt;subMonth())
 -&gt;groupBy('date')
 -&gt;orderBy('date', 'DESC')
 -&gt;get(array(
 DB::raw('Date(created_at) as date'),
 DB::raw('COUNT(*) as "views"')
 ));
</code></pre>

blocks|key|1683903|text|您可以使用Carbon+(集成在Laravel中)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1683904|//+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
++++});|code-block|syntax|javascript|1683905|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use Carbon (integrated in Laravel) 

<pre><code>// Carbon
use Carbon\Carbon; 
$visitorTraffic = PageView::select('id', 'title', 'created_at')
 -&gt;get()
 -&gt;groupBy(function($date) {
 return Carbon::parse($date-&gt;created_at)-&gt;format('Y'); // grouping by years
 //return Carbon::parse($date-&gt;created_at)-&gt;format('m'); // grouping by months
 });
</code></pre>

blocks|key|1897518|text|这就是我是怎么做的。一个简短的示例，但使我的查询更易于管理|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1897519|$visitorTraffic+=+PageView::where('created_at',+'>=',+\Carbon\Carbon::now->subMonth())
++++++++++++++++++++++++->groupBy(DB::raw('Date(created_at)'))
++++++++++++++++++++++++->orderBy('created_at',+'DESC')->get();|code-block|syntax|javascript|1897520|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is how I do it. A short example, but made my query much more manageable

<pre><code>$visitorTraffic = PageView::where('created_at', '&gt;=', \Carbon\Carbon::now-&gt;subMonth())
 -&gt;groupBy(DB::raw('Date(created_at)'))
 -&gt;orderBy('created_at', 'DESC')-&gt;get();
</code></pre>

blocks|key|1897545|text|若要将数据按日期分组，而不是按日期时间分组，可以使用CAST函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1897546|$visitorTraffic+=+PageView::select('id',+'title',+'created_at')
->get()
->groupBy(DB::raw('CAST(created_at+AS+DATE)'));|code-block|syntax|javascript|1897547|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>$visitorTraffic = PageView::select('id', 'title', 'created_at')
-&gt;get()
-&gt;groupBy(DB::raw('CAST(created_at AS DATE)'));
</code></pre>

blocks|key|1684035|text|在mysql中，您可以添加带有时间戳的MONTH关键字作为laravel中的参数，您可以这样做|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1684036|Payement::groupBy(DB::raw('MONTH(created_at)'))->get();|code-block|syntax|javascript|1684037|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>Payement::groupBy(DB::raw('MONTH(created_at)'))-&gt;get();
</code></pre>

blocks|key|1897660|text|我也有同样的问题，我现在使用的是Laravel+5.3。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1897661|我使用DATE_FORMAT()|offset|length|style|CODE|1897662|->groupBy(DB::raw("DATE_FORMAT(created_at,+'%25Y-%25m-%25d')"))|code-block|syntax|javascript|1897663|希望这能对你有所帮助。|1897664|entityMap^0|0|3|D|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

I use <code>DATE_FORMAT()</code>


<pre><code>-&gt;groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"))
</code></pre>

Hopefully this will help you.

blocks|key|1684163|text|我构建了一个用于统计的laravel包：https://github.com/Ifnot/statistics|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1684164|它是基于口才，碳和指标，所以它真的很容易使用。它对于提取日期分组的指示器可能很有用。|1684165|$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;|code-block|syntax|javascript|1684166|1684167|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/Ifnot/statistics^0|K|Z|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Y|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|H|7|Z|8|@]|9|@]|C|$I|J]]|$1|L|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|M|$N|$5|O|P|Q|C|$R|S]]]]

I built a laravel package for making statistics : <a href="https://github.com/Ifnot/statistics" rel="nofollow noreferrer">https://github.com/Ifnot/statistics</a>

It is based on eloquent, carbon and indicators so it is really easy to use. It may be usefull for extracting date grouped indicators.

<pre><code>$statistics = Statistics::of(MyModel::query());

$statistics-&gt;date('validated_at');

$statistics-&gt;interval(Interval::$DAILY, Carbon::createFromFormat('Y-m-d', '2016-01-01'), Carbon::now())

$statistics-&gt;indicator('total', function($row) {
 return $row-&gt;counter;
});

$data = $statistics-&gt;make();

echo $data['2016-01-01']-&gt;total;
</code></pre>

```

blocks|key|1684615|text|我知道这是一个老生常谈的问题，答案多种多样。根据下面的文档和我在laravel上的经验，如何才是处理事情的好的“雄辩的方式”|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1684616|在您的模型中，像这样添加一个赋值函数/获取函数|1684617|public+function+getCreatedAtTimeAttribute()
{
+++return+$this->created_at->toDateString();
}|code-block|syntax|javascript|1684618|另一种方法是强制转换模型中的列，填充$cast数组|offset|length|style|CODE|1684619|$casts+=+[
+++'created_at'+=>+'string'
]|1684620|这里的问题是，您将不能在此模型上再次使用碳，因为Eloquent总是将列转换为字符串|1684621|希望它能有所帮助:)|1684622|entityMap^0|0|0|0|I|5|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@$K|10|L|11|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

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

In your model, add a mutator/Getter like this

<pre><code>public function getCreatedAtTimeAttribute()
{
 return $this-&gt;created_at-&gt;toDateString();
}
</code></pre>

Another way is to cast the columns
in your model, populate the <code>$cast</code> array

<pre><code>$casts = [
 'created_at' =&gt; 'string'
]
</code></pre>

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

Hope it helps :)

blocks|key|1898211|text|使用无碳的Laravel+4.2|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1898212|下面是我如何获取最近的10天，并使用同一天的created_at时间戳计算每一行。|1898213|$q+=+Spins::orderBy('created_at',+'desc')
->groupBy(DB::raw("DATE_FORMAT(created_at,+'%25Y-%25m-%25d')"))
->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.' ';

}|code-block|syntax|javascript|1898214|希望这能有所帮助|1898215|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Using Laravel 4.2 without Carbon

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

<pre><code>$q = Spins::orderBy('created_at', 'desc')
-&gt;groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"))
-&gt;take(10)
-&gt;get(array(
 DB::raw('Date(created_at) as date'),
 DB::raw('COUNT(*) as "views"')
 ));


foreach ($q as $day) {

 echo $day-&gt;date. " Views: " . $day-&gt;views.'&lt;br&gt;';

}
</code></pre>

Hope this helps

blocks|key|1684666|text|您也可以通过以下方式解决此问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1684667|$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();|code-block|syntax|javascript|1684668|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could also solve this problem in following way:

<pre><code>$totalView = View::select(DB::raw('Date(read_at) as date'), DB::raw('count(*) as Views'))
 -&gt;groupBy(DB::raw('Date(read_at)'))
 -&gt;orderBy(DB::raw('Date(read_at)'))
 -&gt;get();
</code></pre>

blocks|key|1684709|text|您可以使用mysql+(See+here+for+Mysql/Mariadb+help)根据格式化的日期过滤结果，并在laravel-5.4中使用类似以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1684710|Model::selectRaw("COUNT(*)+views,+DATE_FORMAT(created_at,+'%25Y+%25m+%25e')+date")
++++->groupBy('date')
++++->get();|code-block|syntax|javascript|1684711|entityMap|0|LINK|mutability|MUTABLE|url|https://mariadb.com/kb/en/library/date_format/^0|C|V|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can filter the results based on formatted date using mysql (<a href="https://mariadb.com/kb/en/library/date_format/" rel="noreferrer">See here for Mysql/Mariadb help</a>) and use something like this in laravel-5.4:

<pre><code>Model::selectRaw("COUNT(*) views, DATE_FORMAT(created_at, '%Y %m %e') date")
 -&gt;groupBy('date')
 -&gt;get();
</code></pre>

blocks|key|1684739|text|像大多数数据库问题一样，它们应该通过使用数据库来解决。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1684740|存储您想要分组的数据并使用索引，您可以实现一种高效而清晰的方法来解决此问题。|1684741|创建迁移|1684742|++++$table->tinyInteger('activity_year')->unsigned()->index();
++++$table->smallInteger('activity_day_of_year')->unsigned()->index();|code-block|syntax|javascript|1684743|更新模型|1684744|<?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);
+}|1684745|用法|1684746|+++$viewsPerDay+=+PageView::perDay()->get();|1684747|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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.

<h1>Create the migration</h1>

<pre><code> $table-&gt;tinyInteger('activity_year')-&gt;unsigned()-&gt;index();
 $table-&gt;smallInteger('activity_day_of_year')-&gt;unsigned()-&gt;index();
</code></pre>

<h1>Update the Model</h1>

<pre><code>&lt;?php

namespace App\Models;

 use DB;
 use Carbon\Carbon;
 use Illuminate\Database\Eloquent\Model;

 class PageView extends Model
 {
 public function scopePerDay($query){

 $query-&gt;groupBy('activity_year');
 $query-&gt;groupBy('activity_day_of_year');

 return $query;

 }

 public function setUpdatedAt($value)
 { 
 $date = Carbon::now();

 $this-&gt;activity_year = (int)$date-&gt;format('y');
 $this-&gt;activity_day_of_year = $date-&gt;dayOfYear;

 return parent::setUpdatedAt($value);
 }
</code></pre>

<h1>Usage</h1>

<pre><code> $viewsPerDay = PageView::perDay()-&gt;get();
</code></pre>

blocks|key|1684751|text|PageView::select('id','title',+DB::raw('DATE(created_at)+as+date'))
++++++++++->get()
++++++++++->groupBy('date');|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1684752|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>PageView::select('id','title', DB::raw('DATE(created_at) as date'))
 -&gt;get()
 -&gt;groupBy('date');
</code></pre>

blocks|key|1898381|text|您可以使用以下代码按日期对它们进行分组，因为您必须在选择查询和groupBy方法中进行分析：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1898382|$counts+=+DB::table('page_views')->select(DB::raw('DATE(created_at)+as+created_at'),+DB::raw('COUNT(*)+as+views'))->
++++++++++++++groupBy(DB::raw('DATE(created_at)'))->
++++++++++++++get();|code-block|syntax|javascript|1898383|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can use the following code to group them by the date, since you have to parse both in the selection query and in the groupBy method:
<pre><code>$counts = DB::table('page_views')-&gt;select(DB::raw('DATE(created_at) as created_at'), DB::raw('COUNT(*) as views'))-&gt;
 groupBy(DB::raw('DATE(created_at)'))-&gt;
 get();
</code></pre>

I currently have a table of <code>page_views</code> 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 <code>created_at</code> column is of type: timestamp, so it includes the hours/minutes/seconds. When I try <code>groupBy</code> queries, it does not group same days together because of the seconds difference.
<pre><code>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
 ... ... ...
</code></pre>
I'd like to get results based on views/day, so I can get something like:
<pre><code> date views
 ==== =====
10-11-2013 15
10-12 2013 45
 ... ...
</code></pre>
I'm thinking I'll need to dig into <code>DB::raw()</code> queries to achieve this, but any insight would help greatly, thanks
Edit: Added clarification of <code>created_at</code> format.

Laravel Eloquent get results grouped by days

数据库

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

问Laravel Eloquent获取按天分组的结果
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Laravel Eloquent获取按天分组的结果EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Laravel Eloquent获取按天分组的结果
EN