从每个不同的日期计算，填写缺失的日期为零 [英] Count from each distinct date, fill in missing dates with zero

问题描述

我正在尝试创建一个雄辩的查询，获取每个不同日子的帖子总数，如果日期不正确，请填写零值。

I'm trying to create an Eloquent query that gets the total number of posts made each distinct day, and if the date is missing, fill it in with a value of zero.

例如，如果我的表格如下所示：

For example, if my table looks like this:

+----+---------------------+
| id |        date         |
+----+---------------------+
|  1 | 2015-01-01 00:00:00 |
|  2 | 2015-01-01 01:53:18 |
|  3 | 2015-01-01 02:41:26 |
|  4 | 2015-01-02 12:51:01 |
|  5 | 2015-01-05 08:24:12 |
+----+---------------------+

这将输出：

2015-01-01 : 3
2015-01-02 : 1
2015-01-05 : 1

，日子 03-04 缺少。如何包含这些日期，但是给他们的价值 0 ，以便我最终输出如下：

Notice, however, that the days 03-04 are missing. How can I include these dates, but give them the value 0 such that I end up with an output like:

2015-01-01 : 3
2015-01-02 : 1
2015-01-03 : 0
2015-01-04 : 0
2015-01-05 : 1

这是我当前的查询：

$posts = Post::select(array(
        DB::raw('DATE(`created_at`) as `date`'),
        DB::raw('COUNT(*)as `count`')
    ))
    ->where('created_at', '>', Carbon::today()->subWeek())
    ->groupBy('date')
    ->orderBy('date', 'DESC')
    ->lists('count', 'date');

谢谢！

推荐答案

在您的SQL结果中，您可以在行中生成一些假数据，但是您不能生成假行，而是加入到假（临时）表中。
在您的情况下，您可以轻松地在sql-result周围应用一些逻辑。

In your SQL results you can generate some "fake-data" in your rows, but u can not generate "fake-rows", exept joining to some "fake(temporary)-table". In your case ll be much easier to apply some logic around sql-result.

使用以下代码替换代码：

Replace your code with this:

$order              = 'DESC';
$endDate            = Carbon::today();
$startDate          = Carbon::today()->subWeek();

$dateInc            = ($order == 'DESC') ? -1 : 1;
$dateCycleHolder    = clone ($dateInc > 0 ? $startDate : $endDate);
$dateCycleEnd       = clone ($dateInc > 0 ? $endDate : $startDate);


$posts = Post::select(array(
    DB::raw('DATE(`created_at`) as `date`'),
    DB::raw('COUNT(*)as `count`')
))
    ->where('created_at', '>', $startDate)
    ->groupBy('date')
    ->orderBy('date', $order)
    ->lists('count', 'date');

$postsFinal = new \Illuminate\Database\Eloquent\Collection();

while ($dateCycleHolder->ne($dateCycleEnd)) {
    $dateCurr = $dateCycleHolder->format('Y-m-d');
    $postsFinal->put($dateCurr, $posts->get($dateCurr, 0));
    $dateCycleHolder->addDay($dateInc);
}
$dateCurr = $dateCycleHolder->format('Y-m-d');    
$postsFinal->put($dateCurr, $posts->get($dateCurr, 0));

$posts = $postsFinal;

它的微软位置灵活，你可以改变这些东西的价值：

its alittle bit flexible, you can change values of this things:

$order              = 'DESC';
$endDate            = Carbon::today();
$startDate          = Carbon::today()->subWeek();

这篇关于从每个不同的日期计算，填写缺失的日期为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！