Hello everyone, today I will describe our Laravel eloquent performance part - 4. 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 🌞.

So, here in part 4, we talk about creating dynamic relationships using subqueries. Let's checkout. In our previous part, we got the last login in date. Now we can also want to show the login IP address. For that, we also make the same scope as the last login IP address. let's jump to the code.

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']);
}


// that is the new scope 👇
public function scopeWithLastLoginIpAddress($query)
{
    $query->addSelect(['last_login_ip_address' => \App\Models\Login::query()
        ->select('ip_address')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->take(1)
    ]);
}

that is the scope so we just add this in controller,

public function index()
{
	$users = User::query()
    ->withLastLoginAt()
    ->withLastLoginIpAddress() // that is the new
    ->orderBy('name')
    ->paginate();
    
   return view('users', compact('users'));
}

So, we declared the scope with last login IP address. In our blade file we just put if on the side let's see that

<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>
            {{-- we modify this --}}
            <td>{{ $user->last_login_at->diffForHumans() }} <small class="text-sm">({{ $user->last_login_ip_address }})</small></td>
        </tr>
    @endforeach
    </tbody>
</table>
{{ $users->links() }}

as we can see in our debug bar everything is okay and we fetch all data of it. Why do not we create a dynamic relationship with subquery?

In, User.php create a lastLogin() function which one belongs to login table with last login id. but in our user table we don't have that. so we build a subquery where we make a virtual column last_login_id and relation this with user table and login table. So, let's modify that User.php

public function lastLogin()
{
    return $this->belongsTo(Login::class);
}

public function scopeWithLastLogin($query)
{
    $query->addSelect(['last_login_id' => Login::query()
        ->select('id')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->take(1)
    ])->with('lastLogin');
}

In the controller, we modify the code and replace it with the below one. we just add a withLastLogin()

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

Until that everything is work fine. But what will happened if we relation this with hasOne relationship. So let's jump to our User model and modify the lastLogin(),

public function lastLogin()
{
    return $this->hasOne(Login::class)->latest();
}

after this, we need to modify the controller. in our controller, if we don't add the →with('lastLogin') in debug bar we see we have 17 queries. so face the (n 1) issues. but after we added →with('lastLogin') we see the query comes to below 3 but it is called 7500 login model. Let's have a look at that code.

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

$users = User::query()
    ->with('lastLogin') // it loads 7500 login model
    ->orderBy('name')
    ->paginate();

To solve that problem we just take only 1 data from lastLogin() so take a look,

public function lastLogin()
{
    return $this->hasOne(Login::class)->latest()->take(1);
}

but if we hit the press in the browser we got an error so to solve that we need to modify that blade view. let's have a look at the updated 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)

        @if($user->lastLogin)
            <tr>
                <td>{{ $user->name }}</td>
                <td>{{ $user->email }}</td>
                <td>{{ $user->lastLogin->created_at->diffForHumans() }} <small class="text-sm">({{ $user->lastLogin->ip_address }})</small></td>
            </tr>
        @endif

    @endforeach
    </tbody>
</table>
{{ $users->links() }}