Laravel - fill up missing dates and counts from database

Question

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}]

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

Updated code:

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}]

Answer 1

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

Answer 2

@Marco answered it perfectly but there is a little improvement I would like to make. There is no need to call array_replace instead of that you can do that in a loop. Like this.

$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){
    $range[$val->time] = $val->count; //Filling value in the array
}

//$data = array_replace($range, $dbData); <-- No need to do this.

return json_encode($range);