Query Builder vs Eloquent - When to Use Each

<?php
// Eloquent: Creates model instances with all features
$users = User::where('active', true)->get();

foreach ($users as $user) {
    // ✅ Access relationships
    echo $user->posts->count();
    
    // ✅ Use accessors
    echo $user->full_name;  // Accessor defined in model
    
    // ✅ Attribute casting works
    echo $user->created_at->diffForHumans();  // Carbon instance
    
    // ✅ Model events fire
    $user->save();  // Fires "saving", "saved" events
}

// Each $user is a full Eloquent model instance:
// - Contains all attributes as properties
// - Tracks original values for dirty checking
// - Loads relationship data
// - Registers event listeners
// - Memory overhead: ~5KB per instance

// For 10,000 records: ~50MB of memory!

// When Eloquent shines:
// ✅ Single record operations
$user = User::find(1);
$user->update(['last_login' => now()]);

// ✅ Using relationships
$user->posts()->create(['title' => 'New Post']);

// ✅ Model events needed
// Event listeners can send welcome emails, clear caches, etc.
$user = User::create(['email' => 'test@test.com']);  // Fires 'created' event

<?php
// Query Builder: Returns plain arrays or objects
$users = DB::table('users')->where('active', true)->get();

foreach ($users as $user) {
    // ❌ No relationships
    // $user->posts  // Error! Not an Eloquent model
    
    // ❌ No accessors
    // $user->full_name  // Undefined property
    
    // ❌ No casting
    echo $user->created_at;  // String, not Carbon
    
    // ✅ But much faster and less memory
    echo $user->name;  // Plain stdClass object
}

// Each $user is just a stdClass:
// - Plain PHP object with database columns as properties
// - No magic, no overhead
// - Memory overhead: ~1KB per instance

// For 10,000 records: ~10MB of memory (5x improvement!)

// When Query Builder shines:
// ✅ Bulk updates
DB::table('users')
    ->where('last_login', '<', now()->subYear())
    ->update(['active' => false]);
// Fast! No model instances created

// ✅ Reporting queries
$report = DB::table('orders')
    ->select(DB::raw('DATE(created_at) as date'), DB::raw('SUM(total) as revenue'))
    ->groupBy('date')
    ->get();

// ✅ Complex joins
$results = DB::table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->join('comments', 'posts.id', '=', 'comments.post_id')
    ->select('users.name', DB::raw('COUNT(comments.id) as comment_count'))
    ->groupBy('users.id', 'users.name')
    ->get();

// ✅ Performance-critical sections
// Exporting 100,000 records to CSV
DB::table('orders')->chunk(1000, function ($orders) {
    foreach ($orders as $order) {
        // Process each chunk
    }
});  // Uses minimal memory

<?php
// Strategy: Use Query Builder to fetch IDs, then hydrate models

// ❌ BAD: Loading 10,000 Eloquent models
$products = Product::where('price', '<', 100)->get();  // 50MB memory

// ✅ BETTER: Chunking
Product::where('price', '<', 100)->chunk(200, function ($products) {
    foreach ($products as $product) {
        // Process in batches of 200
    }
});

// ✅ BEST: Query Builder for data, Eloquent only when needed
// Get IDs first (fast, minimal memory)
$productIds = DB::table('products')
    ->where('price', '<', 100)
    ->pluck('id');  // Just IDs, not full records

// Then process in chunks
foreach ($productIds->chunk(200) as $chunk) {
    $products = Product::findMany($chunk);  // Load 200 at a time
    foreach ($products as $product) {
        // Full Eloquent features available
        $product->update(['on_sale' => true]);
    }
}

// Real-world example: Bulk import
// ❌ BAD:
foreach ($csvRows as $row) {
    User::create($row);  // 1000 INSERT queries + model overhead
}

// ✅ GOOD:
DB::table('users')->insert($csvRows);  // 1 bulk INSERT query

// Or if you need timestamps:
$now = now();
$csvRows = array_map(function ($row) use ($now) {
    return array_merge($row, [
        'created_at' => $now,
        'updated_at' => $now,
    ]);
}, $csvRows);
DB::table('users')->insert($csvRows);

// Hybrid: Query Builder + Eloquent for selective fields
$users = User::select(['id', 'name', 'email'])  // Only needed columns
    ->where('active', true)
    ->get();
// Reduces memory by not loading all attributes