Ad

Get Data By Quantity And Date Using Laravel

- 1 answer

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?

Ad

Answer

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;
Ad
source: stackoverflow.com
Ad