title: Laravel - fill up missing dates and counts from database
body: <p>I need to send number of views for each date based on dates that I get from <code>$request</code> . From my query in controller I get data for the charts that looks like this:</p>
  
  <pre><code>[{"time":"2016-05-01","count":2},{"time":"2016-05-02","count":3},{"time":"2016-05-03","count":7},{"time":"2016-05-07","count":3}]
  </code></pre>
  
  <p>For that data I need to add missing dates and count of views of <code>0</code>. I am trying to follow <a href="https://stackoverflow.com/questions/31507251/count-from-each-distinct-date-fill-in-missing-dates-with-zero">this example</a>  but I just can't seem to implement this solution for my example. This is my method:</p>
  
  <pre><code>public function timelines(Request $request){
      $from = $request['from'];
      $to = $request['to'];
  
  
      $data = DB::table($request['option'])
                  -&gt;select(DB::raw('DATE(created_at) as time'), DB::raw('count(*) as count'))
                  -&gt;whereDate('created_at', '&gt;=', date($from).' 00:00:00')
                  -&gt;whereDate('created_at', '&lt;=', date($to).' 00:00:00')
                  -&gt;groupBy('time')
                  -&gt;get();
  
  
      return json_encode($data);
    }
  </code></pre>
  
  <p>Updated code:</p>
  
  <pre><code>public function timelines(Request $request){
      $from = $request['from'];
      $to = $request['to'];
      $date = $from;
      $data = [];
  
      if ($request['option'] == 'time'){
        $data = View::groupBy('time')
                 -&gt;orderBy('time', 'ASC')
                 -&gt;whereDate('created_at', '&gt;=', date($from).' 00:00:00')
                 -&gt;whereDate('created_at', '&lt;=', date($to).' 00:00:00')
                 -&gt;get([
                   DB::raw('Hour(created_at) as time'),
                   DB::raw('COUNT(*) as "count"')
                   ]);
      }
      else {
        while($date &lt;= $to) {
          $formattedDate = date('Y-m-d', strtotime($date));
  
          $results = DB::table($request['option'])
                     -&gt;select(DB::raw('DATE(created_at) as time'), DB::raw('count(*) as count'))
                     -&gt;whereDate('created_at', '=', $formattedDate .' 00:00:00')
                     -&gt;groupBy('time')
                     -&gt;get();
  
          if(count($results)) {
              $data[] = [
                  'time' =&gt; $formattedDate,
                  'count' =&gt; $results[0]-&gt;count
              ];
          } else {
              $data[] = [
                  'time' =&gt; $formattedDate,
                  'count' =&gt; 0
              ];
          }
  
          $date = strtotime('+1 day', strtotime($formattedDate));
        }
      }
  
      return json_encode($data);
    }
  </code></pre>
  
  <p>With this I only get count for the first date that I send, for example:</p>
  
  <pre><code>[{"time":"2016-03-16","count":0}]
  </code></pre>