Optimizing Eloquent Queries with Constrained Eager Loading

Hello! Welcome to the next lesson in our journey to master Eloquent.

In our last session, we confronted the N+1 query problem and learned how to solve it using basic eager loading with with(). This was a huge step toward writing performant database queries. You now know how to prevent your application from sending hundreds of redundant queries just to fetch related data.

Today, we'll build directly on that foundation. What if your data needs are more complex? What if you need to load not just an author's posts, but also the comments on each of those posts? Or what if you only want to load the approved comments, not all of them?

This lesson will give you that fine-grained control. We'll be focusing on how to use constrained and nested eager loading for optimizing complex relationship queries. By the end of this session, you'll be able to precisely fetch deeply nested and filtered relationship data with maximum efficiency.

1. Nested Eager Loading: Going Deeper

In the previous lesson, we eager-loaded direct relationships, like fetching the user for a collection of posts. Nested eager loading allows you to load relationships of relationships.

Imagine these models:

• User has many Posts.
• Post has many Comments.
• Comment belongs to a User (the commenter).

If you retrieve a user and want to display all their posts and all comments on each post, you need to load two levels deep: posts, and then posts.comments.

Without nested eager loading, you'd solve the first N+1 problem (User -> Post), but then create a new one when you loop through the posts to display their comments.

The Dot Notation Syntax

The classic way to handle this is with "dot" notation. You simply specify the nested path to the relationship you want to load.

// Load a user, their posts, and all comments for each post
$user = User::with('posts.comments')->find(1);

// This is efficient. It runs 3 queries total:
// 1. SELECT * FROM users WHERE id = 1
// 2. SELECT * FROM posts WHERE user_id = 1
// 3. SELECT * FROM comments WHERE post_id IN (post_id_1, post_id_2, ...)

You can even go deeper and load the user who wrote each comment:

$user = User::with('posts.comments.user')->find(1);

This fetches the original user, all their posts, all comments on those posts, and the user model for each of those comments, all in just 4 optimized queries.

The Array Syntax

Laravel also offers a cleaner, more flexible array syntax, which becomes especially powerful when we add constraints later.

As the video shows, this query:
User::with('posts.comments.user')->find(1);

Is equivalent to this using the array syntax:
User::with(['posts' => ['comments' => 'user']])->find(1);

Both are valid, but getting comfortable with the array syntax is beneficial for the next step.

2. Constrained Eager Loading: Being Specific

Eager loading all related models is great, but often you need more control. You might only want comments that are approved, or the five most recent posts. This is where constrained eager loading comes in.

You can customize the relationship query by passing a closure to the with method. This allows you to add where, orderBy, limit, and other query builder constraints to the relationship query.

As the article explains, the syntax is straightforward. Let's say you want to get posts, but only eager load their approved comments that are sorted by the newest first.

$posts = Post::with(['comments' => function ($query) {
    $query->where('approved', true)
           ->orderBy('created_at', 'desc');
}])->get();

How it works: Laravel will still execute just two queries:

1. SELECT * FROM posts
2. SELECT * FROM comments WHERE post_id IN (...) AND approved = 1 ORDER BY created_at DESC

Notice how your constraints were added to the second query. This is extremely powerful.

A crucial point to remember is that this filters the related models, not the parent models. The query above will return all posts, but posts without any approved comments will simply have an empty comments collection. This is different from whereHas, which would filter the Post results themselves.

Constraining with Column Selection

A common and important constraint is selecting only the columns you need. Why retrieve 20 columns from a related table if you only need the id and name?

// Retrieve posts with their user, but only get the user's ID and name
$posts = Post::with(['user' => function ($query) {
    // IMPORTANT: Always select the foreign key (`id` in this case)
    $query->select('id', 'name'); 
}])->get();

3. Combining Nested and Constrained Loading

Now, let's combine these two concepts. What if we want to get posts, their approved comments, and the name of the user who wrote each of those approved comments?

We use the dot notation within the array keys.

$posts = Post::with([
    'comments' => function ($query) {
        $query->where('approved', true)->latest(); // 'latest()' is a shortcut for orderBy('created_at', 'desc')
    },
    'comments.user' => function ($query) {
        $query->select('id', 'name'); // Only get the commenter's ID and name
    }
])->get();

This is the pinnacle of relationship optimization in Eloquent. With one expressive command, you are fetching a complex, nested, and filtered data set with the minimum number of queries possible.

The following video shows a practical example of adding a where clause to a nested relationship.

4. Lazy Eager Loading with Constraints

Sometimes, you already have a model instance and decide you need to load a relationship based on some condition. You can apply the same constraint techniques to the load() method. This is often called "lazy eager loading".

// First, you get a post
$post = Post::find(1);

// ... some logic happens ...

// Now, you decide you need the approved comments
if ($user->wantsToSeeComments()) {
    $post->load(['comments' => function ($query) {
        $query->where('approved', true);
    }]);
}

This provides flexibility while still preventing the N+1 problem.

Conclusion

Congratulations! You have now moved beyond basic eager loading and have the tools to handle truly complex data requirements efficiently. Mastering these patterns is a hallmark of a senior developer and is critical for building applications that can scale.

Let's recap the key techniques:

• Nested Eager Loading: Use dot notation (e.g., 'posts.comments.user') to load relationships of relationships, preventing multiple N+1 problems in one go.
• Constrained Eager Loading: Use an array and a closure (e.g., ['comments' => fn($q) => $q->where(...)]) to filter, sort, or limit the related models that are loaded. This is essential for both performance and business logic.
• Combining Techniques: You can mix nested and constrained loading to fetch deeply nested and highly specific data sets with surgical precision and optimal performance.
• Lazy Eager Loading: Use the load() method with the same closure-based constraints when you need to load relationships onto a model that has already been retrieved.

In our previous lessons, you've learned to filter parent models with whereHas and get aggregates with withCount. Today, you've learned to filter the children models with constrained with(). You now have a complete and powerful toolkit for querying relationships in Laravel.

Up Next:
We will shift our focus slightly within the Eloquent ORM. So far, we've focused on retrieving data. In the next lesson, we will explore soft deletes, a powerful feature for managing data lifecycle by marking records as "deleted" without actually removing them from the database. This is a crucial pattern for applications that need audit trails or the ability to restore data.