Get data by quantity and date using Laravel

Question

I have in my mysql database a user table where I store the date the user was created. I would like to get the number of registered users in the last 7 days. Example:

[6,4,8,6,5,6,7]

Where each number is representing the number of registered users on each date within the 7 day period. How could I do this using Laravel?

Group by date and get the count. – Sachin Kumar Apr 06 '20 at 14:55 — Sachin Kumar, Apr 06 '20 at 14:55

score 0 · Answer 1 · answered Apr 06 '20 at 14:58

with the following code you will get an array with the name and created at, and then do a count to get the number of users:

$previous_week = strtotime("-1 week +1 day");
$start_week = strtotime("last sunday midnight",$previous_week);
$end_week = strtotime("next saturday",$start_week);
$start_week = date("Y-m-d",$start_week);
$end_week = date("Y-m-d",$end_week);

$users = User::whereBetween('created_at', [$start_week, $end_week])->get(['name','created_at']);

echo count($users);

Insted of echo do whatever you want. hope this works

Hi @Oriol , I need the data to be returned in this array of 7 positions, just the amount of each day: [6,4,8,6,5,6,7] — Carlos Farias, Apr 06 '20 at 15:48

Sachin Kumar · Accepted Answer · 2020-04-06T16:30:52.380

You can use the following solution where you can get the normal array and also another array where the key is the date and the value is the number of users registered on that day.

$usersPerDay = User::select(DB::raw('count(id) as `number_of_users`'),DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d') new_date"))
                    ->whereRaw('DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)')
                    ->groupBy('new_date')->orderBy('new_date')->get();

print_r($usersPerDay->pluck('number_of_users')->toArray());

print_r($usersPerDay->pluck('number_of_users', 'new_date'));

As your requirement is to generate days from a date range(link) you will need to do the following. first, update the strict value in config/database.php file

'mysql' => [
    ...
    'strict' => false,
    ...
]

and then run the following query to get the desire result

$query = "select 
            t1.new_date,
            coalesce(SUM(t1.number_of_users+t2.number_of_users), 0) AS number_of_users
            from
            (
              select DATE_FORMAT(a.Date,'%Y-%m-%d') as new_date,
              '0' as  number_of_users
              from (
                select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
                from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
                cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
                cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
              ) a
              where a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
            )t1
            left join
            (
              SELECT DATE_FORMAT(created_at,'%Y-%m-%d') AS created_at, 
              COUNT(*) AS number_of_users
              FROM users
              WHERE DATE_SUB(created_at, INTERVAL 1 DAY) > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK) 
              GROUP BY DAY(created_at) DESC
            )t2
            on t2.created_at = t1.new_date
            group by DAY(t1.new_date)
            order by t1.new_date asc";
        $users = DB::select($query);
        $usersPerDay = collect($users)->pluck('number_of_users')->toArray();
        print_r($usersPerDay);
        die;

Hi, I need the data to be returned in this array of 7 positions, just the amount of each day: [6,4,8,6,5,6,7] — Carlos Farias, Apr 06 '20 at 15:47
What happens when there is no user register on particular day? — Sachin Kumar, Apr 06 '20 at 15:52
Example: [0,0,0,0,0,0,5] In this example, there were only 5 users registered on day "X" (that day is within the last 7 days) — Carlos Farias, Apr 06 '20 at 16:04

score 0 · Answer 3 · answered Apr 06 '20 at 16:56

Using Laravel CollectiongroupBy() function.

$users = Users::whereBetween(now(), now()->subWeek())
             ->groupBy(function ($user) {
                 return $user->created_at->toDateString();
             })
             ->map(function ($group) {
                 return $group->count();
             })
             ->values()
             ->toArray();

Get data by quantity and date using Laravel

3 Answers3