Get a data from user table for line chart (chart.js)

Question

Below is the image is what i am trying to achieve:-

I am on Laravel and I want to get the last 30 days from the current date data from the user table based on how many new users enter a website and how many users delete the account from the website. I have managed the deleted user with soft delete. I have to show that data in the chat.js. I have done a below code but not getting the proper data for my chat.js

$startDate = now()->subDays(30)->startOfDay();
        $endDate = now()->endOfDay();

        $days = [];
        $currentDate = clone $startDate;

        $allUsers = User::count();
        while ($currentDate->lte($endDate)) {
            $days[$currentDate->format('M d')] = $allUsers;
            $currentDate->addDay();
        }

        $records = User::withTrashed()
            ->whereBetween('created_at', [$startDate, $endDate])
            ->select('created_at', 'deleted_at')
            ->get();

        foreach ($records as $record) {
            $date = Carbon::parse($record->created_at)->format('M d');

            if($allUsers != 0){
                $days[$date] += $record->deleted_at ? -1 : 1;
            }
        }
        
        $labels = array_keys($days);
        $data = array_values($days);


        return [
            'datasets' => [
                [
                    'label' => 'New user',
                    'data' => $data,
                ],
            ],
            'labels' => $labels,
        ];

I have managed to get the data but generated data is not correct. I want to show the last 30 days in chat.js For example if today is 14-jul-23 to i want to get data between 14-jun-23 to 14-jul-23. and also if before 14-jun-23 there is 5 active user in the user table and some new user enters between the last 30 days then the count must start with 6.

This is on my user table screenshort

And this is the output output screenshot

Answer 1

I would suggest creating a table or view calculating the number of users by the end of the day. That way you don't have to run the same query and calculations every time the chart is loaded.

1. Make a command php artisan make:command GenerateUserCounts, the command should generate the users count for an input of $date, by default is today()
2. Schedule command to run by end of the day

$schedule->command('users:log-count')->dailyAt('00:00');

3. Run command manually for the days before

Answer 2

foreach ($records as $record) {
  $date = Carbon::parse($record->created_at)->format('M d');
  $prevDate = Carbon::parse($record->created_at)->subDay()->format('M d');
  $days[$date] = $days[$date] + ($days[$prevDate] ?? 0);

  if($allUsers != 0){
    $days[$date] += $record->deleted_at ? -1 : 1;
  }
}

Answer 3

From your code, it seems you were trying to achieve something on the lines of:

1. Pre-populate an array of days with the count on users on the beginning of the period (there is a bug here);
2. Iterate through days adding the count of created users and removing the count of deleted users.

The bug I see is that you started your count at the end of the period instead of the beginning. You should not pre-populate all days with $allUsers but only the users at the beginning, $usersAtBeginning = User::where('created_at', '<' $startDate).

Nonetheless, as I've mentioned, your code can be more straightforward (and faster) by making better use of Laravel feature. In this case, Collection's methods. Your function could look like this:

$startDate = now()->subDays(30)->startOfDay();
$endDate = now()->endOfDay();

/** @var Collection<int, User> */
$users = User::query()
    ->withTrashed()
    ->whereBetween('created_at', [$startDate, $endDate])
    ->get(['created_at', 'deleted_at']);

$totalAtTime = User::query()
    ->where('created_at', '<', $startDate)
    ->whereNull('deleted_at')
    ->count();

$current = $startDate;
while ($current <= $endDate) {
    $next = (clone $current)->endOfDay();

    $created = $users->whereBetween('created_at', [
        $current,
         $next,
    ])
        ->count();

    $deleted = $users->whereBetween('deleted_at', [
        $current,
        $next,
    ])
        ->count();

    $totalAtTime += $created - $deleted;

    $data[$current->format('M d')] = $totalAtTime;
    $current->addDay();
}