Advanced Eloquent Techniques and Optimizations in Laravel

• Introduction
• Advanced Query Optimization
  • 1. Eager Loading Strategies
  • 2. Advanced Where Clauses
• Advanced Relationship Techniques
  • 1. Polymorphic Relationships
  • 2. Custom Relationship Methods
• Performance Optimization Techniques
  • 1. Database Query Optimization
  • 2. Caching Strategies
• Advanced Model Techniques
  • 1. Custom Collections
  • 2. Global Scopes and Local Scopes
• Real-World Optimization Examples
  • 1. Dashboard Query Optimization
  • 2. Search Optimization
• Performance Monitoring and Debugging
  • 1. Query Logging and Analysis
  • 2. Database Optimization Commands
• Best Practices Summary
• Conclusion

Introduction

After a decade of working with Laravel’s Eloquent ORM, I’ve discovered that the difference between good and exceptional applications often lies in how efficiently you handle database operations. This comprehensive guide shares advanced techniques that can dramatically improve your application’s performance and maintainability.

Advanced Query Optimization

1. Eager Loading Strategies

The N+1 Problem Solution:

// Bad: N+1 queries
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name; // Each iteration hits the database
}

// Good: Eager loading
$posts = Post::with('user')->get();
foreach ($posts as $post) {
    echo $post->user->name; // No additional queries
}

// Advanced: Conditional eager loading
$posts = Post::with(['user' => function ($query) {
    $query->select('id', 'name', 'email');
}])->get();

Nested Eager Loading:

// Load posts with users and their profiles
$posts = Post::with([
    'user.profile',
    'comments.user',
    'tags'
])->get();

// Conditional nested loading
$posts = Post::with([
    'user' => function ($query) {
        $query->where('active', true);
    },
    'comments' => function ($query) {
        $query->where('approved', true)->latest();
    }
])->get();

2. Advanced Where Clauses

Complex Conditional Queries:

// Using when() for conditional queries
$users = User::query()
    ->when($request->filled('search'), function ($query) use ($request) {
        $query->where('name', 'like', '%' . $request->search . '%');
    })
    ->when($request->filled('status'), function ($query) use ($request) {
        $query->where('status', $request->status);
    })
    ->get();

// Using where callbacks for complex logic
$posts = Post::where(function ($query) {
    $query->where('published', true)
          ->orWhere(function ($subQuery) {
              $subQuery->where('draft', true)
                       ->where('user_id', auth()->id());
          });
})->get();

Subqueries and Exists:

// Using subqueries for performance
$users = User::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('posts')
          ->whereColumn('posts.user_id', 'users.id')
          ->where('posts.published', true);
})->get();

// Advanced subquery with aggregates
$users = User::addSelect([
    'latest_post_title' => Post::select('title')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1),
    'posts_count' => Post::selectRaw('count(*)')
        ->whereColumn('user_id', 'users.id')
])->get();

Advanced Relationship Techniques

1. Polymorphic Relationships

One-to-Many Polymorphic:

// Model setup
class Comment extends Model
{
    public function commentable()
    {
        return $this->morphTo();
    }
}

class Post extends Model
{
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

class Video extends Model
{
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

// Usage with constraints
$comments = Comment::with(['commentable' => function ($morphTo) {
    $morphTo->morphWith([
        Post::class => ['user'],
        Video::class => ['channel'],
    ]);
}])->get();

Many-to-Many Polymorphic:

class Tag extends Model
{
    public function posts()
    {
        return $this->morphedByMany(Post::class, 'taggable');
    }

    public function videos()
    {
        return $this->morphedByMany(Video::class, 'taggable');
    }
}

// Advanced polymorphic queries
$tags = Tag::whereHasMorph('taggable', [Post::class, Video::class], function ($query, $type) {
    if ($type === Post::class) {
        $query->where('published', true);
    } elseif ($type === Video::class) {
        $query->where('status', 'active');
    }
})->get();

2. Custom Relationship Methods

Through Relationships:

class Country extends Model
{
    public function posts()
    {
        return $this->hasManyThrough(Post::class, User::class);
    }
}

// Advanced through with custom keys
class Supplier extends Model
{
    public function userHistory()
    {
        return $this->hasManyThrough(
            History::class,
            User::class,
            'supplier_id', // Foreign key on users table
            'user_id',     // Foreign key on history table
            'id',          // Local key on suppliers table
            'id'           // Local key on users table
        );
    }
}

Custom Relationship Classes:

class BelongsToManyOrdered extends BelongsToMany
{
    public function get($columns = ['*'])
    {
        return parent::get($columns)->sortBy('pivot.sort_order');
    }
}

class Post extends Model
{
    public function tags()
    {
        return new BelongsToManyOrdered(
            Tag::query(),
            $this,
            'post_tag',
            'post_id',
            'tag_id'
        );
    }
}

Performance Optimization Techniques

1. Database Query Optimization

Chunk Processing for Large Datasets:

// Process large datasets efficiently
User::where('active', true)
    ->chunk(1000, function ($users) {
        foreach ($users as $user) {
            // Process each user
            $this->processUser($user);
        }
    });

// Lazy collections for memory efficiency
User::lazy()->each(function ($user) {
    // Process users one by one with minimal memory usage
    $this->processUser($user);
});

Database Transactions:

// Manual transaction control
DB::transaction(function () {
    $user = User::create($userData);
    $profile = $user->profile()->create($profileData);
    $user->assignRole('user');
}, 3); // Retry 3 times on deadlock

// Using database transactions with savepoints
DB::transaction(function () {
    $user = User::create($userData);
    
    DB::transaction(function () use ($user) {
        // Nested transaction (savepoint)
        $user->profile()->create($profileData);
    });
});

2. Caching Strategies

Query Result Caching:

// Cache expensive queries
class PostService
{
    public function getPopularPosts($days = 7)
    {
        return Cache::remember("popular_posts_{$days}", 3600, function () use ($days) {
            return Post::with(['user', 'comments'])
                ->where('created_at', '>=', now()->subDays($days))
                ->withCount(['views', 'likes'])
                ->orderBy('views_count', 'desc')
                ->limit(10)
                ->get();
        });
    }
}

// Model-based caching
class User extends Model
{
    protected static function booted()
    {
        static::updated(function ($user) {
            Cache::forget("user_posts_{$user->id}");
        });
    }

    public function getCachedPosts()
    {
        return Cache::remember("user_posts_{$this->id}", 1800, function () {
            return $this->posts()->published()->with('tags')->get();
        });
    }
}

Cache Tags for Complex Invalidation:

// Using cache tags
class PostService
{
    public function getCategoryPosts($categoryId)
    {
        return Cache::tags(['posts', "category_{$categoryId}"])
            ->remember("category_posts_{$categoryId}", 3600, function () use ($categoryId) {
                return Post::where('category_id', $categoryId)
                    ->published()
                    ->with(['user', 'tags'])
                    ->get();
            });
    }

    public function invalidateCategoryCache($categoryId)
    {
        Cache::tags("category_{$categoryId}")->flush();
    }
}

Advanced Model Techniques

1. Custom Collections

class PostCollection extends Collection
{
    public function published()
    {
        return $this->filter(function ($post) {
            return $post->published;
        });
    }

    public function byAuthor($authorId)
    {
        return $this->filter(function ($post) use ($authorId) {
            return $post->user_id === $authorId;
        });
    }
}

class Post extends Model
{
    public function newCollection(array $models = [])
    {
        return new PostCollection($models);
    }
}

// Usage
$posts = Post::all()->published()->byAuthor(1);

2. Global Scopes and Local Scopes

Global Scopes:

class PublishedScope implements Scope
{
    public function apply(Builder $builder, Model $model)
    {
        $builder->where('published', true);
    }
}

class Post extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(new PublishedScope);
    }
}

// Disable global scope when needed
$allPosts = Post::withoutGlobalScope(PublishedScope::class)->get();

Advanced Local Scopes:

class Post extends Model
{
    public function scopeForUser($query, $userId)
    {
        return $query->where('user_id', $userId);
    }

    public function scopePopular($query, $threshold = 100)
    {
        return $query->where('views', '>=', $threshold);
    }

    public function scopeWithinDateRange($query, $startDate, $endDate)
    {
        return $query->whereBetween('created_at', [$startDate, $endDate]);
    }
}

// Chaining scopes
$posts = Post::forUser(1)
    ->popular(500)
    ->withinDateRange(now()->subMonth(), now())
    ->get();

Real-World Optimization Examples

1. Dashboard Query Optimization

class DashboardService
{
    public function getUserDashboard($userId)
    {
        return Cache::remember("dashboard_{$userId}", 600, function () use ($userId) {
            return [
                'user' => User::with(['profile', 'settings'])->find($userId),
                'stats' => $this->getUserStats($userId),
                'recent_posts' => $this->getRecentPosts($userId),
                'notifications' => $this->getUnreadNotifications($userId),
            ];
        });
    }

    private function getUserStats($userId)
    {
        return User::where('id', $userId)
            ->withCount([
                'posts',
                'posts as published_posts_count' => function ($query) {
                    $query->where('published', true);
                },
                'comments',
                'followers'
            ])
            ->first();
    }
}

2. Search Optimization

class SearchService
{
    public function searchPosts($query, $filters = [])
    {
        return Post::query()
            ->when($query, function ($builder) use ($query) {
                $builder->where(function ($subQuery) use ($query) {
                    $subQuery->where('title', 'like', "%{$query}%")
                             ->orWhere('content', 'like', "%{$query}%")
                             ->orWhereHas('tags', function ($tagQuery) use ($query) {
                                 $tagQuery->where('name', 'like', "%{$query}%");
                             });
                });
            })
            ->when($filters['category_id'] ?? null, function ($builder, $categoryId) {
                $builder->where('category_id', $categoryId);
            })
            ->when($filters['date_range'] ?? null, function ($builder, $dateRange) {
                $builder->whereBetween('created_at', $dateRange);
            })
            ->with(['user:id,name', 'category:id,name', 'tags:id,name'])
            ->published()
            ->latest()
            ->paginate(15);
    }
}

Performance Monitoring and Debugging

1. Query Logging and Analysis

// Enable query logging in development
if (app()->environment('local')) {
    DB::listen(function ($query) {
        Log::info('Query executed:', [
            'sql' => $query->sql,
            'bindings' => $query->bindings,
            'time' => $query->time
        ]);
    });
}

// Custom query analyzer
class QueryAnalyzer
{
    public static function analyzeSlowQueries()
    {
        $queries = DB::getQueryLog();
        
        return collect($queries)
            ->filter(function ($query) {
                return $query['time'] > 100; // Queries taking more than 100ms
            })
            ->sortByDesc('time');
    }
}

2. Database Optimization Commands

// Custom Artisan command for database optimization
class OptimizeDatabaseCommand extends Command
{
    protected $signature = 'db:optimize';

    public function handle()
    {
        $this->info('Analyzing database performance...');
        
        // Analyze table sizes
        $tables = DB::select('SHOW TABLE STATUS');
        
        foreach ($tables as $table) {
            if ($table->Data_length > 100000000) { // > 100MB
                $this->warn("Large table detected: {$table->Name}");
            }
        }
        
        // Suggest indexes
        $this->suggestIndexes();
    }
}

Best Practices Summary

1. Always use eager loading to prevent N+1 queries
2. Implement proper indexing on frequently queried columns
3. Use database transactions for data integrity
4. Cache expensive queries with appropriate invalidation strategies
5. Monitor query performance in production
6. Use chunking for large dataset processing
7. Implement proper scopes for reusable query logic
8. Optimize relationships with custom pivot attributes when needed

Conclusion

Mastering these advanced Eloquent techniques will significantly improve your Laravel application’s performance and maintainability. The key is to understand your data access patterns and choose the right optimization strategy for each use case.

Remember: premature optimization is the root of all evil, but knowing these techniques allows you to make informed decisions when performance becomes critical.