Understanding PHP's N+1 Problem and How to Use Eager Loading

// Assuming a PDO connection already exists in the $pdo variable

// 1. Fetch the 100 most recent articles (This is the "1" part of N+1)
$stmt = $pdo->query("SELECT id, title, content, author_id FROM articles ORDER BY created_at DESC LIMIT 100");
$articles = $stmt->fetchAll(PDO::FETCH_ASSOC);

$result = [];

// 2. Loop through each article (This is the "N" part of N+1)
foreach ($articles as $article) {
    // Execute query for EVERY loop iteration!
    $authorStmt = $pdo->prepare("SELECT id, name, email FROM authors WHERE id = :author_id");
    $authorStmt->execute(['author_id' => $article['author_id']]);
    $author = $authorStmt->fetch(PDO::FETCH_ASSOC);
    
    $result[] = [
        'title' => $article['title'],
        'content' => $article['content'],
        'author_name' => $author['name']
    ];
}

// Render JSON or HTML...

// 1. Fetch 100 latest articles
$stmt = $pdo->query("SELECT id, title, content, author_id FROM articles ORDER BY created_at DESC LIMIT 100");
$articles = $stmt->fetchAll(PDO::FETCH_ASSOC);

// If there are no articles, immediately return an empty array to prevent errors
if (empty($articles)) {
    return [];
}

// 2. Collect all unique author_ids from the previous article
$authorIds = array_unique(array_column($articles, 'author_id'));

// 3. Create a placeholder for the WHERE IN query (?, ?, ?)
$placeholders = implode(',', array_fill(0, count($authorIds), '?'));

// 4. Execute ONE additional query to get all authors at once
$authorQuery = "SELECT id, name FROM authors WHERE id IN ($placeholders)";
$authorStmt = $pdo->prepare($authorQuery);
$authorStmt->execute(array_values($authorIds));
$authors = $authorStmt->fetchAll(PDO::FETCH_ASSOC);

// 5. Indexing author array based on ID for fast search (O(1) time complexity)
$authorMap = [];
foreach ($authors as $author) {
    $authorMap[$author['id']] = $author;
}

// 6. Combine data in PHP memory
$result = [];
foreach ($articles as $article) {
    $authorName = isset($authorMap[$article['author_id']]) ? $authorMap[$article['author_id']]['name'] : 'Unknown';
    
    $result[] = [
        'title' => $article['title'],
        'author_name' => $authorName
    ];
}

// Controller
$articles = Article::latest()->limit(100)->get(); // Execute 1 Query

// View (Blade Template)
@foreach ($articles as $article)
    
    

{{ $article->title }}

Written by: {{ $article->author->name }}

@endforeach

// Controller
// We instruct Eloquent to load the 'author' relationship AT ONCE.
$articles = Article::with('author')->latest()->limit(100)->get();

// View
@foreach ($articles as $article)
    
    

{{ $article->title }}

Written by: {{ $article->author->name }}

@endforeach

$articles = Article::with('author')->get();

foreach ($articles as $article) {
    // author has been loaded, it's safe.
    // BUT, author->profile has NOT been loaded yet! N+1 happens again here.
    echo $article->author->profile->avatar_url; 
}

$articles = Article::with('author.profile')->get();
// Menjalankan 3 Query: Articles, Authors (WHERE IN), Profiles (WHERE IN)

$articles = Article::with(['author', 'category'])->get(); // Mengambil 50.000 data

Article::with(['author', 'category'])->chunk(500, function ($articles) {
    // Proses 500 artikel pada satu waktu
    foreach ($articles as $article) {
        // Tulis ke CSV atau jalankan proses logik
    }
    // RAM akan dibebaskan oleh Garbage Collector PHP setelah scope function selesai
});

use Illuminate\Database\Eloquent\Model;

public function boot()
{
    // Hanya matikan lazy loading di environment lokal dan testing.
    // Jika ada yang mencoba mengakses relasi yang tidak di-load via with(),
    // Laravel akan melemparkan Exception yang membuat aplikasi error dengan jelas.
    Model::preventLazyLoading(! app()->isProduction());
}