N 1 Query problem In Laravel, after obtaining the main model list, multiple queries are triggered when accessing the associated model during the traversal process. The solutions include: 1. Use with() to load the associated model in advance, such as Post::with('user')->get(); 2. Use with('user.role'); 3. Add query conditions for with() through closures; 4. Use whereHas() or has() to filter associated records; 5. Use doesntHave() to obtain unrelated data; 6. Avoid calling database query methods in loops.

In Laravel development, N 1 query problems are a common but overlooked performance bottleneck. It usually occurs when we use Eloquent ORM to obtain the associated model data, causing the database to be frequently queried and slowed down the entire request speed. To solve this problem, the key is to load ahead of time (Eager Loading) and use query constraints correctly.

What is an N 1 query?

Simply put, when you take a list of a main model (such as Post) and access its association model (such as User) while iterating through them, each association triggers a new query. Suppose you get 20 Posts, each Post needs to check the corresponding User once, and will eventually execute 1 query to get Posts, plus 20 query to get Users - this is the so-called N 1 query problem.

For example:

 $posts = Post::all();

foreach ($posts as $post) {
    echo $post->user->name;
}

The above code will trigger 21 database queries: 1 get all posts, and then a user query for each post.

How to load relationships in advance with()

The most direct way to solve N 1 is to use the with() method to preload the association model. In this way, Eloquent will find out all relevant data at the beginning to avoid repeated queries.

 $posts = Post::with('user')->get();

foreach ($posts as $post) {
    echo $post->user->name;
}

In this example, there are only 2 queries: one get posts and one get all related users. The efficiency has been significantly improved.

If you have nested relationships, such as Post belongs to User and User belongs to Role , you can write this:

 Post::with('user.role')->get();

You can also add conditional restrictions to with() , for example:

 Post::with(['user' => function($query) {
    $query->where('status', 'active');
}])->get();

Use whereHas() and doesnthave() to control the query scope

Sometimes you just want to get records of those associated data. For example, if you want to find all posts with users, you can use has() or more flexible whereHas() :

 // Get the posts associated with the user $posts = Post::has('user')->get();

// Add conditions, such as the user status is active
$posts = Post::whereHas('user', function($query) {
    $query->where('status', 'active');
})->get();

On the contrary, if you want to find posts that are not associated with users, you can use doesntHave() :

 $posts = Post::doesntHave('user')->get();

These methods can help you accurately control the query range, while also reducing unnecessary data processing.

Avoid calling database queries in loops

In addition to using with() , there is another thing to note: don't call any method in the loop that will cause database queries. For example, the following writing method will also cause N 1:

 foreach ($posts as $post) {
    // Assume getComments() is a custom method, and the Comment::where(...) is executed internally
    $comments = $post->getComments();
}

If there is no optimization inside getComments() , a query will be initiated every loop. The correct way is to define the relationship in the model and load it in advance via with() :

 class Post extends Model {
    public function comments() {
        return $this->hasMany(Comment::class);
    }
}

// Post::with('comments')->get() when querying;

This avoids additional queries in the loop.

Basically that's it. The core of solving the N 1 query problem lies in understanding the relationship loading mechanism of Eloquent and making good use of methods such as with() , has() , whereHas() , etc. Although it doesn't seem complicated, if ignored, it can easily become an invisible killer of system performance.

The above is the detailed content of Addressing the N 1 Query Problem in Laravel Eloquent. For more information, please follow other related articles on the PHP Chinese website!