Using Joins in Laravel Eloquent Queries

Laravel applications which depend heavily on database interaction using Laravel Eloquent often create performance issues.

To improve the performance of your application you have to refactor your queries and keep an eye on the memory consuming queries by using the ORM like Laravel Eloquent.

In this post, I will walk you through a very simple example to refactor a query and improve the database response time.

Problem

Consider the sample code below, which use the Product and Category table to return the category name.

$product = Product::where('id', $productId)->first();

$productCategory = Category::where('id', $product->category_id)->pluck('name')->first();

Above code example is simple enough to understand but it uses two separate calls to the database. The first query must return before the second query can be triggered.

Solution

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

$productCategory = Product::where('id', $productId)
    ->leftJoin('category', 'product.category', '=', 'category.id')
    ->select('product.id','category.name')->first();

Now, what we have just done above?

• Firstly we target the product based on the productId, exactly same query as the first one.
• Next, we join the results with category table using the category to product’s category. First query grabbed the Product model which provides us with the access to the catgeory attribute.
• We select the product id and category name.
• Finally, we use first() method, which ensures that once it finds a single category which statisfy the requirement, it will resturn the category name instantly.

That’s it. You know have a better understanding of how left join works and you can combine the query to make a single request to the database. It looks fairly simple but using the same method you can optimize the complex queries dealing with multiple tables.

If you have any comments or suggestion, or you find any mistake please let me know by submitting a comment below.