Optimizing Laravel Eloquent Queries: Understanding N+1 Issue and Circular Relationships

When I started as a backend developer, my primary goal was to get the task done, focusing on producing the desired output rather than optimizing the code. However, as I gained experience, I realized that getting the task done isn't enough, the real challenge is to ensure that the code performs efficiently, handling even large datasets in the least amount of time.

As a Laravel developer, Eloquent provides an elegant and easy way to interact with the database. But it's crucial to understand what's happening under the hood. Writing optimized code involves recognizing potential performance pitfalls like the N+1 query problem and handling circular relationships efficiently.

Let's dive into a practical example to understand these concepts better.

Problem: The N+1 Query Issue

Example Scenario

Let's say we have two tables: Products and Categories.

• Product Model: Each product belongs to a category.
• Category Model: Each category can have multiple products.

In the Product model, we have a helper method discountedPrice() that uses a discount percentage defined in the related Category to calculate the product's price.

Product Model

Now, suppose we want to display all products under all categories along with their discounted prices. We might write the following controller method:

Controller Method

In the view, we iterate through $category->products and call $product->discountedPrice() to display the discounted price:

Blade View

Identifying the N+1 Problem

At first glance, the above code seems fine. We load all categories in one query, but the real problem starts when we loop through $category->products. For each category, Laravel will execute a query to fetch the products. If there are N categories, this results in N+1 queries:

1 query for fetching all categories + N queries for fetching products for each category.

For example, if we have 6 categories, we'll end up with 1 + 6 = 7 queries. This pattern is known as the N+1 query problem.

Solution 1: Eager Loading

Laravel offers a simple way to fix this issue using eager loading, which retrieves related models beforehand. To resolve this, we can modify the controller method:

Optimized Controller Method

By eager loading the products relationship, we reduce the query count to just 2:

• 1 query to fetch all categories
• 1 query to fetch all related products

This is a significant improvement, but we can optimize it further.

The Next Problem: Circular Relationships

Even though we have eager-loaded the products, we encounter a new issue when calling $product->discountedPrice(). Inside this method, $this->category->discount_percentage triggers a query to fetch the category for each product, leading to another N+1 problem. Essentially, we still end up running extra queries, even though we've already fetched the categories.

This happens because accessing $product->category inside discountedPrice() creates a circular relationship:

• Category → Products
• Product → Category

Solution 2: Advanced Eager Loading

We can address this issue by eager loading the categories within products to avoid the extra queries:

Now, we still have 2 queries, but this is still slightly inefficient. Since we already have the categories loaded initially, we don't need to re-fetch them again when calling $product->category.

Solution 3: Using setRelation to Break the Circular Relationship

To avoid redundant queries entirely, we can manually assign the Category relationship to each Product using the setRelation method:

Final Optimized Controller Method

How setRelation Works

The setRelation method manually sets the category property on each product, effectively telling Eloquent, "I already have this relation loaded, so don't query the database again." As a result, when $product->discountedPrice() is called, the category relation is already available, and no additional query is made.

I hope this was informative and helps you optimize your Laravel applications. Let’s write clean and efficient code! ??