Laravel-填写数据库中缺少的日期和计数 [英] Laravel - fill up missing dates and counts from database

问题描述

我需要根据从$request获得的日期发送每个日期的视图数.从控制器中的查询中，我获得了如下图表的数据:

I need to send number of views for each date based on dates that I get from $request . From my query in controller I get data for the charts that looks like this:

[{"time":"2016-05-01","count":2},{"time":"2016-05-02","count":3},{"time":"2016-05-03","count":7},{"time":"2016-05-07","count":3}]

对于该数据，我需要添加缺少的日期和0的观看次数.我正在尝试遵循此示例但对于我的示例，我似乎无法实现此解决方案.这是我的方法:

For that data I need to add missing dates and count of views of 0. I am trying to follow this example but I just can't seem to implement this solution for my example. This is my method:

public function timelines(Request $request){
    $from = $request['from'];
    $to = $request['to'];


    $data = DB::table($request['option'])
                ->select(DB::raw('DATE(created_at) as time'), DB::raw('count(*) as count'))
                ->whereDate('created_at', '>=', date($from).' 00:00:00')
                ->whereDate('created_at', '<=', date($to).' 00:00:00')
                ->groupBy('time')
                ->get();


    return json_encode($data);
  }

更新的代码:

public function timelines(Request $request){
    $from = $request['from'];
    $to = $request['to'];
    $date = $from;
    $data = [];

    if ($request['option'] == 'time'){
      $data = View::groupBy('time')
               ->orderBy('time', 'ASC')
               ->whereDate('created_at', '>=', date($from).' 00:00:00')
               ->whereDate('created_at', '<=', date($to).' 00:00:00')
               ->get([
                 DB::raw('Hour(created_at) as time'),
                 DB::raw('COUNT(*) as "count"')
                 ]);
    }
    else {
      while($date <= $to) {
        $formattedDate = date('Y-m-d', strtotime($date));

        $results = DB::table($request['option'])
                   ->select(DB::raw('DATE(created_at) as time'), DB::raw('count(*) as count'))
                   ->whereDate('created_at', '=', $formattedDate .' 00:00:00')
                   ->groupBy('time')
                   ->get();

        if(count($results)) {
            $data[] = [
                'time' => $formattedDate,
                'count' => $results[0]->count
            ];
        } else {
            $data[] = [
                'time' => $formattedDate,
                'count' => 0
            ];
        }

        $date = strtotime('+1 day', strtotime($formattedDate));
      }
    }

    return json_encode($data);
  }

通过这种方式，我只会得到我发送的第一个日期的计数，例如:

With this I only get count for the first date that I send, for example:

[{"time":"2016-03-16","count":0}]

推荐答案

这个解决方案对我来说很有效，我首先创建了一个数组，其中包含时间范围内的所有日期，日期设置为键，计数值设置为0，然后将查询后的值替换为DB中与查询中的计数值相同的数组中的值:

This solution worked out for me, I first created an array with all the dates in the time range, with dates set as keys and count values set as 0, and then replaced the values after the query in DB, in the same array with count values from the query:

$period = new DatePeriod( new DateTime($from), new DateInterval('P1D'), new DateTime($to));
      $dbData = [];

      foreach($period as $date){
          $range[$date->format("Y-m-d")] = 0;
      }

      $data = DB::table($request['option'])
                ->select(DB::raw('DATE(created_at) as time'), DB::raw('count(*) as count'))
                ->whereDate('created_at', '>=', date($from).' 00:00:00')
                ->whereDate('created_at', '<=', date($to).' 00:00:00')
                ->groupBy('time')
                ->get();

      foreach($data as $val){
        $dbData[$val->time] = $val->count;
      }

      $data = array_replace($range, $dbData);
    }

    return json_encode($data);

这篇关于Laravel-填写数据库中缺少的日期和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！