Using Joins in Laravel Eloquent Queries

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.

6 comments on “Using Joins in Laravel Eloquent Queries

  1. There are some error here “Product:where(‘id’, $productId)”. You forget one “:”. It is Product::where(‘id’, $productId)

  2. $test = MainWarehouse::where(‘id’, $id)
    ->leftJoin(‘Product’, ‘MainWarehouse.productId’, ‘=’, ‘product.id’)
    ->select(‘product.id’,’MainWarehouse.name’)->first();
    return view(‘mainwarehouse.index’,compact(‘test’));

    I have the ‘id’,$id on both table but it calling undefined variable id

Leave a Reply

Your email address will not be published. Required fields are marked *

*

When sharing a code snippet please wrap you code with pre tag and add a class code-block to it like below.
<pre class="code-block">you code here</pre>

*
*

This site uses Akismet to reduce spam. Learn how your comment data is processed.