Introduction

Eloquent ORM is Laravel's elegant ActiveRecord implementation. While most developers know the basics, mastering advanced query techniques separates good developers from great ones. This guide covers 20 advanced techniques that will make your queries faster, cleaner, and more maintainable.

1. Eager Loading to Avoid N+1

The N+1 problem is the most common performance issue in Laravel applications:

// Bad: N+1 queries
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name; // Query per post!
}

// Good: Eager loaded
$posts = Post::with('user')->get();

2. Nested Eager Loading

$books = Book::with('author.contacts')->get();

// Load multiple relationships
$posts = Post::with(['author', 'comments' => function ($query) {
    $query->where('approved', true);
}])->get();

3. Constraining Eager Loads

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

4. Lazy Eager Loading

$books = Book::all();
if ($someCondition) {
    $books->load('author', 'publisher');
}

5. Eager Loading Specific Columns

$users = User::with('posts:id,user_id,title')->get();

6. Query Scopes

class Post extends Model
{
    public function scopePublished($query)
    {
        return $query->where('status', 'published');
    }

    public function scopeOfAuthor($query, $authorId)
    {
        return $query->where('author_id', $authorId);
    }
}

// Usage
$posts = Post::published()->ofAuthor(5)->get();

7. Dynamic Scopes

public function scopeWhereStatus($query, $status)
{
    return $query->where('status', $status);
}

$posts = Post::whereStatus('draft')->get();

8. Subqueries with Select

use Illuminate\Support\Facades\DB;

$users = User::query()
    ->addSelect(['last_post_at' => Post::select('created_at')
        ->whereColumn('posts.user_id', 'users.id')
        ->orderByDesc('created_at')
        ->limit(1)
    ])
    ->get();

9. Subquery Ordering

use Illuminate\Support\Facades\DB;

$users = User::query()
    ->orderByDesc(Post::select('created_at')
        ->whereColumn('posts.user_id', 'users.id')
        ->orderByDesc('created_at')
        ->limit(1))
    ->get();

10. Conditional Clauses

$query = User::query();

$query->when($request->filled('role'), function ($q) use ($request) {
    return $q->where('role', $request->role);
})->when($request->filled('active'), function ($q) use ($request) {
    return $q->where('active', $request->boolean('active'));
});

$users = $query->get();

11. WhereHas and WhereDoesntHave

// Posts with at least 3 approved comments
$posts = Post::whereHas('comments', function ($query) {
    $query->where('approved', true);
}, '>=', 3)->get();

// Users who never logged in
$users = User::whereDoesntHave('logins')->get();

12. Aggregates and Grouping

$orders = Order::query()
    ->selectRaw('DATE(created_at) as date, COUNT(*) as total, SUM(total_amount) as revenue')
    ->groupByRaw('DATE(created_at)')
    ->orderByDesc('date')
    ->get();

13. Raw Expressions

$users = User::select('id', 'name')
    ->selectRaw('CASE WHEN active = 1 THEN "Active" ELSE "Inactive" END as status')
    ->get();

14. Unions

$first = User::where('role', 'admin');
$second = User::where('role', 'moderator');

$users = $first->union($second)->get();

15. Chunking to Handle Large Datasets

User::where('active', true)->chunk(500, function ($users) {
    foreach ($users as $user) {
        // Process each user in chunks of 500
    }
});

// Chunk by ID for very large tables
User::where('active', true)->chunkById(500, function ($users) {
    // ...
});

16. Cursors for Streaming

foreach (User::cursor() as $user) {
    // Stream results one at a time, low memory footprint
}

17. Lazy Loading with Lazy

User::query()->lazy(200)->each(function ($user) {
    // Process lazily
});

18. Upserts

Flight::upsert([
    ['destination' => 'Tokyo', 'price' => 1500],
    ['destination' => 'Osaka', 'price' => 1200],
], ['destination'], ['price']);

19. Transactions

DB::transaction(function () {
    $order = Order::create([...]);
    $order->items()->createMany([...]);
    Payment::create([...]);
}, 5); // 5 retry attempts

// Manual control
DB::beginTransaction();
try {
    // ...
    DB::commit();
} catch (Throwable $e) {
    DB::rollBack();
    throw $e;
}

20. Observers and Model Events

class PostObserver
{
    public function created(Post $post): void
    {
        Log::info('New post created: ' . $post->title);
        Notification::send(Subscriber::all(), new NewPostNotification($post));
    }
}

// Register in AppServiceProvider
Post::observe(PostObserver::class);

Performance Checklist

• Always eager load relationships
• Select only needed columns
• Use indexes on frequently queried columns
• Chunk or cursor for large datasets
• Cache expensive queries
• Monitor with Laravel Telescope

Conclusion

These 20 techniques cover the essential advanced Eloquent patterns. Master them and your queries will be faster, your code cleaner, and your application ready for production scale.