Question 207916 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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

enter image description here

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

Tags:

3

Answers


  1. 0

    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');
    1. Run command manually for the days before
    Login or Signup to reply.
  2. 0 
    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;
  }
}
    Login or Signup to reply.
  3. 0

    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)
    ->count();

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

    $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();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search