Last 6 months number of users graph - Laravel

Question

I want to create a chart. Using the library below. https://charts.erik.cat/adding_datasets.html#database-eloquent-example-2

Query that dynamically reports the number of users for the last 6 months

(with month rows
Sample:

January = 1,
February = 2,
March = 3
...)

The output I want is:

$user_count_chart = [
"2" => 30,
"3" => 41,
"4" => 50,
"5" => 62,
"6" => 72,
"7" => 150,
];

**

key: month value
value: number of users

**

Schema::create('users', function (Blueprint $table) {
  $table->bigIncrements('id');
  $table->string('email')->unique();
  $table->string('password');
  $table->dateTime('email_verified_at')->nullable();
  $table->rememberToken();
  $table->timestamps();
});

score 1 · Accepted Answer · answered Apr 21 '20 at 17:18

return User::groupBy('date')
        ->orderBy('date', 'desc')
        ->take(6)
        ->get([
            DB::raw('MONTH(created_at) as date'),
            DB::raw('count(id) as total')
        ])
        ->pluck('total', 'date');

One important thing you may need to be careful is, missing months. Let's say if there isn't any user on march then your chart may not work as you expected. Please check this answer to overcome this "possible" problem.

score 0 · Answer 2 · answered Apr 21 '20 at 16:47

0

Can use groupBy() function of Laravel Collection.

$user_count_chat = User::all()
    ->groupBy(function ($user) {
        return $user->created_at->month;
    })
    ->map(function ($group) {
        return $group->count();
    })

EDIT

Be carefull, I think month number is not good enough, Because 2019-Jan and 2020-Jan both has the same month number, but they are not the same, My advice is use 'year-month'

answered Apr 21 '20 at 16:47

Tharaka Dilshan

4,371
3
14
28

This one may have performance problem since it's making collection calculations after fetching all users from database. – Ersoy Apr 21 '20 at 16:50
I don't think so, preform collection calculation is almost the same as database grouping. It is done my DBMS, this is done by you :) – Tharaka Dilshan Apr 21 '20 at 16:52
It is not same, fetching 1 million users and making calculation on code level is not same as fetching 9 rows with aggregated results. – Ersoy Apr 21 '20 at 16:55
Yeah, can't argue with that. – Tharaka Dilshan Apr 21 '20 at 16:58

Last 6 months number of users graph - Laravel

2 Answers2