Hello everyone, today I will describe our Laravel eloquent performance part - 3. It's an advanced-level topic so I recommend you know the basics of Laravel. How to create a Model, Controller, how it works, and call this in view blade. You can find so many tutorials about this so check that and keep learning. Let's begin our series 🌞.

Here today we talk about one too many records in relationships. Imagine we have a User Model also a Login model. The login model contains the user's IP address so we have a blade view where we need to show another column where we can see the user's last login IP address.

In the beginning, we check out the migration file of the users table & logins table.

// users table
Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            //$table->foreignId('company_id')->constrained()->nullable();
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
        
        
// logins table
Schema::create('logins', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained('users');
            $table->string('ip_address', 50);
            $table->timestamps();
        });

You, see that the logins table have contain the user's id. Now let's look at the factory file to create the fake data.

// UserFactory.php
    public function definition()
    {
        return [
            'name' => $this->faker->name(),
            'email' => $this->faker->unique()->safeEmail(),
            'email_verified_at' => now(),
            'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password
            'remember_token' => Str::random(10),
        ];
    }
    
    
// LoginFactory.php
    public function definition()
    {
        return [
            'ip_address' => $this->faker->ipv4(),
            'created_at' => $this->faker->dateTimeThisDecade(),
        ];
    }

Now, to execute those fake data we must put this in the DatabaseSeeder file,

public function run()
{
        
    User::factory()->count(60)->create()->each(fn ($user) => $user->logins()
    ->createMany(Login::factory()->count(500)->make()->toArray())
    );

}

If you check the database you can see we have 60 users and each user has contained 500 IP address. After we migrate we want to show the users with the last login. So we already made the Model: User, Login. You can create any controller or where you want to show this in your controller. Make a route in web.php or whatever you want. so let's jump to the controller and blade view. Now let's checkout the controller file d👇

public function index()
{
   // we modify here
   $users = User::query()
            ->orderBy('name')
            ->paginate();
            
   return view('users', compact('users'));
}

Now, let's check the blade view 👇

<table class="table table-striped">
    <thead>
    <tr>
        <th scope="col">Name</th>
        <th scope="col">Email</th>
        <th scope="col">Last Login</th>
    </tr>
    </thead>
    <tbody>
    @foreach($users as $user)
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
            <td>{{ 'Here we need to show last login' }}</td> <!-- we only modify this column --!>
        </tr>
    @endforeach
    </tbody>
</table>
{{ $users->links() }}

Okay, here we show the basic view of our blade and controller. Now we modify our code and blade view let's check that. Now we make a relation in our User model. In User.php we write that relation,

public function logins()
{
    return $this->hasMany(Login::class);
}

So we can quickly call that login data in blade view so modify that blade,

<td>{{ $user->logins()->latest()->first()->created_at->diffForHumans() }}</td>

So here is the look of our table. We see the last login data is here.

but if you see the debug bar you see we call 17 queries which are most of which are the same and we called 30 models. 15 models for users and 15 models for login. We face the (n 1) issues. So take a look,

So how we can reduce this? now let's modify our controller. So look at the below,

$users = User::query()
    ->with('logins')
    ->orderBy('name')
    ->paginate();

also, we modify our blade view,

<td>{{ $user->logins->sortBYDesc('created_at')->first()->created_at->diffForHumans() }}</td>

now, let's hit the browser, then see our query comes to only 3 queries one for pagination another for the user, and another for the user's login. but if you look at the model we call all 7515 models for login. We told you we have 500 login data for each user. so we called all login data for a user.

Let's modify the controller, now we build a sub-query where we create a column of last_login_at here we pass the created_at data in the user table,

$users = User::query()
    ->addSelect(['last_login_at' => Login::query()
        ->select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->take(1)
    ])
    ->orderBy('name')
    ->paginate();

So, we need to modify the blade view,

<td>{{ $user->last_login_at }}</td>

so, here in debug bar, we can see we only call 2 queries and 15 models. So it comes to much much better. To show the created_at data as human user-friendly we need to put them in ‘withCasts’ in datetime. To modify our controller,

$users = User::query()
    ->addSelect(['last_login_at' => Login::query()
        ->select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->take(1)
    ])
    ->withCasts(['last_login_at' => 'datetime'])
    ->orderBy('name')
    ->paginate();

Our blae view will be,

<td>{{ $user->last_login_at->diffForHumans() }}</td>

Now, we modify our code in a pretty good way. We put our subquery in scope with. so we put that all in our model. So got to our User.php file and put that in scope with. let's have a look,

public function scopeWithLastLoginAt($query)
{
    $query->addSelect(['last_login_at' => \App\Models\Login::query()
        ->select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->take(1)
    ])
        ->withCasts(['last_login_at' => 'datetime']);
}

Now, changed our controller,

$users = User::query()
    ->withLastLoginAt()
    ->orderBy('name')
    ->paginate();

Okay, now it looks much much better. So subquery is a powerful tool for queries we can easily fetch data from there. Now let's have a look at all the files and here I comment on the previous query. so it should be better to find everything in one view.

Model 👇

public function logins()
{
    return $this->hasMany(\App\Models\Login::class);
}

public function scopeWithLastLoginAt($query)
{
    $query->addSelect(['last_login_at' => \App\Models\Login::query()
        ->select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->take(1)
    ])
        ->withCasts(['last_login_at' => 'datetime']);
}

Controller 👇

public function index()
{

//        $users = User::query()
//            ->orderBy('name')
//            ->paginate();


//        $users = User::query()
//            ->with('logins')
//            ->orderBy('name')
//            ->paginate();


//        $users = User::query()
//            ->addSelect(['last_login_at' => Login::query()
//                ->select('created_at')
//                ->whereColumn('user_id', 'users.id')
//                ->latest()
//                ->take(1)
//            ])
//            ->orderBy('name')
//            ->paginate();


//        $users = User::query()
//            ->addSelect(['last_login_at' => Login::query()
//                ->select('created_at')
//                ->whereColumn('user_id', 'users.id')
//                ->latest()
//                ->take(1)
//            ])
//            ->withCasts(['last_login_at' => 'datetime'])
//            ->orderBy('name')
//            ->paginate();

    $users = User::query()
        ->withLastLoginAt()
        ->orderBy('name')
        ->paginate();

    return view('users', compact('users'));
}

Blade 👇

<table class="table table-striped">
    <thead>
    <tr>
        <th scope="col">Name</th>
        <th scope="col">Email</th>
        <th scope="col">Last Login</th>
    </tr>
    </thead>
    <tbody>
    @foreach($users as $user)
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
            {{--<td>{{ $user->logins()->latest()->first()->created_at->diffForHumans() }}</td>--}}
            {{--<td>{{ $user->logins->sortBYDesc('created_at')->first()->created_at->diffForHumans() }}</td>--}}
            {{--<td>{{ $user->last_login_at }}</td>--}}
            <td>{{ $user->last_login_at->diffForHumans() }}</td>
        </tr>
    @endforeach
    </tbody>
</table>
{{ $users->links() }}