When building an application that develops a heavy dependency on database interaction, it's easy to start with some basic queries that eventually require refactoring due to performance reasons. In this post, I'll walk through an example query which leverages a SQL left join to improve database response time.
Let's begin by examining some sample code which uses both the User and Role table to return the user's role, which in this case is a string "admin."
$user = User::where('id', $user_id)->first();
$userRole = Role::where('role_id', $user->role)->pluck('role_name')->first();
// returns 'admin'This block of code requires two separate calls to the database that cannot be run concurrently; the first must return before the second can begin. Let's improve this by combining the two requests into a single query.
$userRole = User::where('user.id', $user_id)
->leftJoin('role', 'user.role', '=', 'role.role_id')
->select(
'user.id',
'role.role_name'
)
->first();Now let's walk through what exactly is happening here.
First, we target the user based on the user id, just like in the first query of the original code block.
Next, we join our result with the role table, grabbing the role which is equal to this user's role. Note that in the first query we grabbed the entire user object, which provides us with access to the role attribute.
Finally, we decide what to retrieve. The select statement basically says "grab only the role name based on the role from the left join AND the user from the initial query.
By using eloquent's first() method, we can also ensure that this query is as efficient as possible; once it finds a single role which satisfies the requirements laid out in the query, it will return the role_name instantly.
Hopefully, by walking through such a seemingly complicated step by step, you now have a better understanding of how left joins work and have a workable example for all your eloquent joining needs! Any questions or comments let me know in the comments below.