Count every day entry

Question

I want to count all register users each day from past 30 days. If there is no registration return 0

Currently i'm using this

    $startDate = Carbon\Carbon::now()->subDays(30);
    $signup = User::where('created_at', '>=', $startDate)
        ->groupBy('date')
        ->orderBy('date', 'DESC')
        ->get([
            DB::raw('Date(created_at) as date'),
            DB::raw('COUNT(*) as value')
        ]);
    return $signup;

Current output

[ 
  { 
    "date" : "2014-05-29",
    "value" : 1
  },
  { 
    "date" : "2014-05-15",
    "value" : 1
  }
]

If there is no registration then display value 0 for example

[ 
  { 
    "date" : "2014-05-29",
    "value" : 1
  },
  { 
    "date" : "2014-05-28",
    "value" : 0
  },
  { 
    "date" : "2014-05-27",
    "value" : 0
  },
  { 
    .....
    .....
  },
  { 
    "date" : "2014-05-15",
    "value" : 1
  }
  { 
    "date" : "2014-05-14",
    "value" : 0
  }
]

How can I get such result.

No, I don't have. I just have entries with date `2014-05-29` and `2014-05-15` ( For sure they are of timestamp datatype ). I want to loop through whole past 30 days — Basic Bridge, Jun 01 '14 at 11:54

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

Put the $result array in another array value by value, and if lastrowdate - rowdate > 1 day, use this function to fill up the empty dates: https://stackoverflow.com/a/4312491/3696548

edited May 23 '17 at 12:27

Community

1
1

answered Jun 01 '14 at 12:08

GouxLord

86
8

Count every day entry

1 Answers1