laravel

Using Joins in Laravel Eloquent Queries For Better Performance

Most often I see people making many mistakes when interacting with a database, and many beginners don’t really pay attention to how many calls the make. And this is fine in the beginning. Especially if your application is small and you are still learning.

Additionally, I also think that in the early days of the Laravel application, you should be focused on building the features and coding. Once you start running into issues and you actually start noticing that your application is slow, only then you should look into what you can do to optimize your MySQL queries.

99% of the time you will use Eloquent methods to make database calls and sometimes the easy route you want to take, turns out to cause issues down the line.

ORM like Eloquent is simply fantastic and it will support you all the way to the end.

Now imagine that you have been building your application for some time now and you want to refactor some of your calls to make them slightly faster. Let’s go through an example of how you can join queries to get the same result, in less time.

Problem

Consider an example code below, which uses the User and Organization table to return the user name.

$user = User::find($user_id);
$organization = Organization::where('id', $user->organization_id)->pluck('name')->first();

The code example above is simple enough to understand but it uses two separate calls to the database. The second call is obviously dependable on the first one, hence we need to wait for the first one to finish before we can get to the next one.

Solution

Let’s improve this code example by combining both requests into a single query.

$user_with_organization = User::where('id', $user_id)
    ->leftJoin('organizations', 'users.organization_id', '=', 'organizations.id')
    ->select('users.id','organizations.name')->first();

Now, what we have just done above?
1. Firstly we target the user based on the $user_id, exactly the same query as the first one.
2. Next, we join the results with the organizations table using the users table fill the left join query
3. First query grabbed the User model which provides us with the access to the organization_id attribute.
4. We select the user id and the organization name.
5. Finally, we use the first() method, which ensures that once it finds a single organization that satisfies the requirement, it will return the organization name instantly.

How Left Join Works

When you have two queries and you want to left join the second one, then that means that your end result will be the result of your first query, where some of the results might receive some matching with the second query and have some extra data with them.

That’s it. Now you have a better understanding of how left join works. Use it to combine queries that are related to make a single request to the database. It looks fairly simple but using the same method can help you optimize the most complex queries dealing with multiple tables.

If you have any comments or suggestions, feel free to contact me.

About the author

Avatar

laravelrecipies