public static function todayUsersPerHour() {
$today = Carbon::today();
$users = array();
$hours = array();
$a = 1;
for($h = 0; $h < 24; $h++){
$today->addHour($a);
$sub = $today;
array_push($hours, $today.' - '.$sub->subHour());
$user = User::where([
['created_at', '<=', $today],
['created_at','>',$sub]
])->count();
array_push($users, $user);
}
return $hours;
}
I have this static method that doesn't work as I wanted to.
When I run this in laravel tinker I get:
[
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
]
When I add $a++ into the loop I get:
[
"2018-06-02 01:00:00 - 2018-06-02 00:00:00",
"2018-06-02 02:00:00 - 2018-06-02 01:00:00",
"2018-06-02 04:00:00 - 2018-06-02 03:00:00",
"2018-06-02 07:00:00 - 2018-06-02 06:00:00",
"2018-06-02 11:00:00 - 2018-06-02 10:00:00",
"2018-06-02 16:00:00 - 2018-06-02 15:00:00",
"2018-06-02 22:00:00 - 2018-06-02 21:00:00",
"2018-06-03 05:00:00 - 2018-06-03 04:00:00",
"2018-06-03 13:00:00 - 2018-06-03 12:00:00",
"2018-06-03 22:00:00 - 2018-06-03 21:00:00",
"2018-06-04 08:00:00 - 2018-06-04 07:00:00",
"2018-06-04 19:00:00 - 2018-06-04 18:00:00",
"2018-06-05 07:00:00 - 2018-06-05 06:00:00",
"2018-06-05 20:00:00 - 2018-06-05 19:00:00",
"2018-06-06 10:00:00 - 2018-06-06 09:00:00",
"2018-06-07 01:00:00 - 2018-06-07 00:00:00",
"2018-06-07 17:00:00 - 2018-06-07 16:00:00",
"2018-06-08 10:00:00 - 2018-06-08 09:00:00",
"2018-06-09 04:00:00 - 2018-06-09 03:00:00",
"2018-06-09 23:00:00 - 2018-06-09 22:00:00",
"2018-06-10 19:00:00 - 2018-06-10 18:00:00",
"2018-06-11 16:00:00 - 2018-06-11 15:00:00",
"2018-06-12 14:00:00 - 2018-06-12 13:00:00",
"2018-06-13 13:00:00 - 2018-06-13 12:00:00",
]
It's adding to last $today value new $a value but when $a value is always 1 it is like it never go on last $today value but first.
What am I doing wrong here? Thanks
This should get you the results - using SQL only:
// Only select users with `created_at` greater than today - start of the day
User::where('created_at', '>', now()->startOfDay())
// Select created_at hour and count for that hour
->selectRaw('date_format(created_at, \'%Y-%m-%d %H:00:00\') as hour, count(users.id) as count')
// Group by created_at hour
->groupBy(\DB::raw('date_format(created_at, \'%Y-%m-%d %H:00:00\')'))
->get()
Will return something along these lines:
[{hour: '2018-06-02 20:00:00', count: 5}, {hour: '2018-06-02 21:00:00', count: 3}, ...]
Edit
To get all 24 hours I'd do this:
// Fetch needed users with 1 query
$users = User::where('created_at', '>', now()->startOfDay())->get();
$result = [];
for ($hour = 0; $hour < 24; $hour++) {
$result[] = [
'hour' => $hour,
'count' => $users->filter(function ($user) use ($hour) {
// Find users where hour equals to current iteration
return $user->created_at->hour == $hour;
})->count(),
];
}
return $result;