The N+1 Problem in Laravel: A Complete Arsenal of Solutions Beyond Eager Loading
In the previous article, we saw how Eager Loading solves the N+1 problem. But what if you have complex scenarios or need maximum performance? Let's explore ALL possible solutions.
🎯 Method 1: Eager Loading
The foundational solution that covers 80% of cases:
// Simple Eager Loading
$posts = Post::with('author')->get();
// Loading multiple relationships
$posts = Post::with(['author', 'tags', 'comments'])->get();
// Conditional loading
$posts = Post::with(['author' => function($query) {
$query->select('id', 'name')->where('active', true);
}])->get();
🚀 Method 2: Joins
When you only need data from related tables:
// Get posts with author names in a single query
$posts = Post::join('authors', 'posts.author_id', '=', 'authors.id')
->select('posts.*', 'authors.name as author_name')
->get();
// Use directly in the template:
@foreach($posts as $post)
<p>Author: {{ $post->author_name }}</p>
@endforeach
⚡ Method 3: Has Many Through
For deeply nested relationships:
// Suppose there's a chain: Country -> User -> Post
class Country extends Model
{
public function posts()
{
return $this->hasManyThrough(Post::class, User::class);
}
}
// Now get all posts for a country with one query
$country = Country::with('posts')->find(1);
📊 Method 4: Lazy Eager Loading
When you don't know in advance which relationships will be needed:
$posts = Post::all();
// Load authors for the already retrieved collection
$posts->load('author');
// Or conditionally:
if ($someCondition) {
$posts->load('author', 'comments');
}
🎪 Method 5: Aggregates
When you only need counts or sums:
// INSTEAD OF THIS (N+1 queries):
$posts = Post::all();
foreach($posts as $post) {
echo $post->comments->count(); // Separate query for each post
}
// DO THIS (2 queries):
$posts = Post::withCount('comments')->get();
foreach($posts as $post) {
echo $post->comments_count; // Value is already preloaded
}
🔥 Method 6: Raw Queries
For maximum performance in complex cases:
$posts = DB::table('posts')
->leftJoin('authors', 'posts.author_id', '=', 'authors.id')
->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
->select('posts.*', 'authors.name as author_name',
DB::raw('COUNT(comments.id) as comments_count'))
->groupBy('posts.id')
->get();
💾 Method 7: Database Views
For very complex queries that are used frequently:
CREATE VIEW post_details AS
SELECT
p.*,
a.name as author_name,
COUNT(c.id) as comments_count
FROM posts p
LEFT JOIN authors a ON p.author_id = a.id
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, a.name;
// Use in Laravel as a regular table
$posts = DB::table('post_details')->get();
🏗 Method 8: Denormalization
Sometimes it's easier to add redundant data:
// Add an author_name field to the posts table
$post = new Post();
$post->title = 'Example';
$post->author_name = $author->name; // Save a copy
$post->save();
// Now get the author name without JOIN:
$posts = Post::all(); // No N+1!
📝 Method 9: Scopes with Eager Loading
Create reusable scopes:
class Post extends Model
{
public function scopeWithRelations($query)
{
return $query->with(['author', 'comments' => function($q) {
$q->latest()->limit(5);
}]);
}
}
// Use everywhere:
$posts = Post::withRelations()->get();
🎯 When to Use What?
- Eager Loading - for most cases
- Joins - when you only need data from related tables
- Has Many Through - for deep relationships
- Aggregates - for counts and sums
- Raw Queries - for maximum performance
- Database Views - for very complex queries
- Denormalization - for frequently requested data
🔍 How to Find Problem Areas?
// In AppServiceProvider
public function boot()
{
if (app()->environment('local')) {
DB::listen(function ($query) {
if (str($query->sql)->contains('where `id` in')) {
Log::warning('Possible N+1 detected: ' . $query->sql);
}
});
}
}
Conclusion: Eager Loading is an excellent solution, but it's not the only one. Choose the approach based on the specific task and performance requirements.