Laravel Eloquent ORM — 2,847 Queries in 12s from N+1

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable;

    /**
     * The table associated with the model.
     * Only needed if it deviates from the convention (users).
     */
    // protected $table = 'users';

    /**
     * The primary key type.
     * Override if using UUIDs or non-integer keys.
     */
    // protected $keyType = 'string';

    /**
     * Indicates if the IDs are auto-incrementing.
     * Set to false when using UUIDs.
     */
    // public $incrementing = false;

    /**
     * Mass-assignable attributes.
     * ONLY include fields users should be able to set.
     * NEVER include: is_admin, role, balance, password_hash.
     */
    protected $fillable = [
        'name',
        'email',
        'password',
    ];

    /**
     * Hidden attributes — excluded from JSON serialization.
     * Prevents password hashes from leaking in API responses.
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * Attribute casting — database values cast to PHP types.
     * Ensures consistent types regardless of database driver.
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
        'is_admin'          => 'boolean',
        'preferences'       => 'array',
        'last_login_at'     => 'datetime',
    ];

    // ── Relationships ────────────────────────────────────────────────────────

    public function orders(): HasMany
    {
        return $this->hasMany(Order::class);
    }

    public function profile(): HasOne
    {
        return $this->hasOne(Profile::class);
    }

    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class)->withTimestamps();
    }

    // ── Local Scopes ─────────────────────────────────────────────────────────

    /**
     * Scope: only active users.
     * Usage: User::active()->get()
     */
    public function scopeActive($query)
    {
        return $query->where('is_active', true);
    }

    /**
     * Scope: users who logged in within the last N days.
     * Usage: User::recentlyActive(30)->get()
     */
    public function scopeRecentlyActive($query, int $days = 30)
    {
        return $query->where('last_login_at', '>=', now()->subDays($days));
    }

    // ── Accessors ────────────────────────────────────────────────────────────

    /**
     * Computed attribute: full name.
     * Accessible as $user->full_name (no parentheses).
     */
    public function getFullNameAttribute(): string
    {
        return "{$this->first_name} {$this->last_name}";
    }

    // ── Model Events ─────────────────────────────────────────────────────────

    protected static function boot()
    {
        parent::boot();

        static::created(function ($user) {
            // Create a profile for every new user
            $user->profile()->create([
                'bio' => '',
                'avatar_url' => null,
            ]);
        });

        static::deleting(function ($user) {
            // Clean up related data before the user is deleted
            $user->orders()->update(['user_id' => null]);
        });
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\MorphMany;

class Order extends Model
{
    protected $fillable = [
        'user_id',
        'status',
        'total_in_cents',
        'notes',
    ];

    protected $casts = [
        'total_in_cents' => 'integer',
        'shipped_at'     => 'datetime',
    ];

    // ── belongsTo: the foreign key (user_id) is on THIS table ────────────────
    // Each order belongs to one user.
    // The inverse of User::hasMany(Order::class)
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    // ── hasMany: the foreign key (order_id) is on the RELATED table ──────────
    // Each order has many items.
    public function items(): HasMany
    {
        return $this->hasMany(OrderItem::class);
    }

    // ── hasMany with custom foreign key ──────────────────────────────────────
    // If the FK is not order_id, specify it explicitly.
    public function shipments(): HasMany
    {
        return $this->hasMany(Shipment::class, 'parent_order_id');
    }

    // ── belongsToMany: requires a pivot table (order_coupons) ────────────────
    // An order can have many coupons, and a coupon can apply to many orders.
    public function coupons(): BelongsToMany
    {
        return $this->belongsToMany(Coupon::class)
            ->withPivot('discount_in_cents', 'applied_at')
            ->withTimestamps();
    }

    // ── Polymorphic: comments can belong to orders, products, etc. ───────────
    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable');
    }

    // ── Querying through relationships ───────────────────────────────────────
    // These queries compile to SQL JOINs or subqueries — no PHP iteration.

    // Get all orders for a specific user:
    // $user->orders()->where('status', 'shipped')->get();

    // Get orders that have at least one item with a specific product:
    // Order::whereHas('items', function ($query) use ($productId) {
    //     $query->where('product_id', $productId);
    // })->get();

    // Get orders with no items (orphans):
    // Order::doesntHave('items')->get();

    // Count related records without loading them:
    // $user->orders()->count(); // Single COUNT query, no model hydration
}

<?php

namespace Io\Thecodeforge\Eloquent;

use App\Models\User;
use App\Models\Order;
use Illuminate\Support\Facades\DB;

class EagerLoadingExamples
{
    /**
     * N+1 PROBLEM: 1 query for users + N queries for orders
     * For 100 users: 101 queries
     */
    public function nPlusOneProblem(): void
    {
        DB::enableQueryLog();

        $users = User::all(); // Query 1: SELECT * FROM users

        foreach ($users as $user) {
            // Query 2..101: SELECT * FROM orders WHERE user_id = ?
            // One query PER user — this is the N+1 problem
            echo $user->orders->count();
        }

        $queries = DB::getQueryLog();
        // count($queries) = 101 (for 100 users)
    }

    /**
     * EAGER LOADING SOLUTION: 2 queries total
     * User::with('orders') loads all users, then all orders with WHERE IN.
     */
    public function eagerLoadingSolution(): void
    {
        DB::enableQueryLog();

        // Query 1: SELECT * FROM users
        // Query 2: SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...)
        $users = User::with('orders')->get();

        foreach ($users as $user) {
            // No additional query — orders are already loaded
            echo $user->orders->count();
        }

        $queries = DB::getQueryLog();
        // count($queries) = 2 (regardless of user count)
    }

    /**
     * NESTED EAGER LOADING: 4 queries for 3 levels of relationships
     */
    public function nestedEagerLoading(): void
    {
        DB::enableQueryLog();

        // Query 1: SELECT * FROM users
        // Query 2: SELECT * FROM orders WHERE user_id IN (...)
        // Query 3: SELECT * FROM order_items WHERE order_id IN (...)
        // Query 4: SELECT * FROM products WHERE id IN (...)
        $users = User::with('orders.items.product')->get();

        $queries = DB::getQueryLog();
        // count($queries) = 4 (regardless of user/order/item count)
    }

    /**
     * CONDITIONAL EAGER LOADING: filter related records during load
     */
    public function conditionalEagerLoading(): void
    {
        // Only load shipped orders — pending orders are excluded
        $users = User::with(['orders' => function ($query) {
            $query->where('status', 'shipped')
                  ->orderBy('shipped_at', 'desc')
                  ->limit(5); // Only the 5 most recent shipped orders
        }])->get();
    }

    /**
     * LAZY EAGER LOADING: load relationships after the initial query
     * Useful when you realize you need a relationship mid-request.
     */
    public function lazyEagerLoading(): void
    {
        $users = User::all(); // Already loaded without orders

        // Later in the code, realize you need orders
        $users->load('orders'); // Runs the missing query and attaches to existing models

        // Can also load with conditions
        $users->load(['orders' => function ($query) {
            $query->where('status', 'shipped');
        }]);
    }

    /**
     * COUNTING WITHOUT LOADING: COUNT query without hydrating models
     */
    public function countingWithoutLoading(): void
    {
        // Option 1: on a single model
        $user = User::find(1);
        $orderCount = $user->orders()->count(); // SELECT COUNT(*) FROM orders WHERE user_id = 1

        // Option 2: on a collection with withCount
        $users = User::withCount('orders')->get();
        foreach ($users as $user) {
            echo "{$user->name}: {$user->orders_count} orders";
        }
        // Adds orders_count attribute without loading Order models
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Order extends Model
{
    protected $fillable = ['user_id', 'status', 'total_in_cents', 'shipped_at'];

    protected $casts = [
        'total_in_cents' => 'integer',
        'shipped_at'     => 'datetime',
    ];

    // ── Relationships ────────────────────────────────────────────────────────

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function items(): HasMany
    {
        return $this->hasMany(OrderItem::class);
    }

    // ── Local Scopes ─────────────────────────────────────────────────────────

    /**
     * Scope: only orders with a specific status.
     * Usage: Order::withStatus('shipped')->get()
     */
    public function scopeWithStatus(Builder $query, string $status): Builder
    {
        return $query->where('status', $status);
    }

    /**
     * Scope: orders created within a date range.
     * Usage: Order::createdBetween('2024-01-01', '2024-12-31')->get()
     */
    public function scopeCreatedBetween(Builder $query, string $from, string $to): Builder
    {
        return $query->whereBetween('created_at', [$from, $to]);
    }

    /**
     * Scope: high-value orders above a threshold.
     * Usage: Order::highValue(10000)->get() // orders > $100
     */
    public function scopeHighValue(Builder $query, int $minCents = 5000): Builder
    {
        return $query->where('total_in_cents', '>=', $minCents);
    }

    /**
     * Scope: orders that have not been shipped yet.
     * Usage: Order::unshipped()->get()
     */
    public function scopeUnshipped(Builder $query): Builder
    {
        return $query->whereNull('shipped_at')
                     ->whereNot('status', 'cancelled');
    }

    /**
     * Scope: orders with at least N items.
     * Uses a subquery to avoid loading all items.
     * Usage: Order::withMinItems(5)->get()
     */
    public function scopeWithMinItems(Builder $query, int $min): Builder
    {
        return $query->has('items', '>=', $min);
    }

    /**
     * Scope: recent orders sorted by creation date.
     * Usage: Order::recent(10)->get() // 10 most recent orders
     */
    public function scopeRecent(Builder $query, int $limit = 20): Builder
    {
        return $query->orderBy('created_at', 'desc')
                     ->limit($limit);
    }

    // ── Composing scopes ─────────────────────────────────────────────────────
    // Order::withStatus('shipped')->highValue(10000)->recent(5)->get()
    // SQL: SELECT * FROM orders WHERE status = 'shipped' AND total_in_cents >= 10000
    //      ORDER BY created_at DESC LIMIT 5

<?php

namespace Io\Thecodeforge\Eloquent;

use App\Models\User;
use App\Models\Order;
use Illuminate\Support\Facades\DB;

class PerformancePatterns
{
    /**
     * CHUNKING: process large datasets in batches
     * Each batch loads 500 rows, processes them, then loads the next 500.
     */
    public function processAllUsers(): void
    {
        User::chunk(500, function ($users) {
            foreach ($users as $user) {
                // Process each user (send email, update flag, etc.)
                $user->update(['processed_at' => now()]);
            }
        });
        // Memory usage: ~500 users at a time, not all users at once
    }

    /**
     * CHUNK BY ID: stable chunking even if the table changes during processing
     * Uses WHERE id > lastId instead of OFFSET/LIMIT.
     */
    public function exportOrders(): void
    {
        Order::with('user', 'items.product')
            ->chunkById(500, function ($orders) {
                foreach ($orders as $order) {
                    // Export to CSV, S3, etc.
                    $this->exportToCsv($order);
                }
            });
    }

    /**
     * LAZY COLLECTION: iterate over large datasets without loading all into memory
     * Returns a LazyCollection that loads rows on demand.
     */
    public function findHighValueCustomers(): void
    {
        $highValueUsers = User::lazy(500)
            ->filter(function ($user) {
                return $user->orders()->sum('total_in_cents') > 100000;
            })
            ->map(function ($user) {
                return [
                    'name'  => $user->name,
                    'email' => $user->email,
                    'total' => $user->orders()->sum('total_in_cents'),
                ];
            })
            ->values();

        // LazyCollection processes one batch at a time — not all users at once
    }

    /**
     * DB::TABLE: raw query builder for bulk operations without model events
     * 10x faster than Eloquent for bulk updates.
     */
    public function bulkDeactivateInactiveUsers(): int
    {
        // Eloquent version (slow — fires events, casts, per-row UPDATE):
        // User::where('last_login_at', '<', now()->subYear())->update(['is_active' => false]);
        // Actually, update() on Eloquent is also a single query.
        // The issue is when you need model events — then use chunk.

        // Query builder version (fast — single UPDATE query, no events):
        return DB::table('users')
            ->where('last_login_at', '<', now()->subYear())
            ->update(['is_active' => false]);
        // Single UPDATE query — no model instantiation, no events, no casting
    }

    /**
     * RAW SQL: for complex queries that Eloquent cannot express
     * Window functions, CTEs, complex aggregations.
     */
    public function getTopCustomersByMonth(): array
    {
        return DB::select("
            SELECT
                u.name,
                DATE_FORMAT(o.created_at, '%Y-%m') AS month,
                SUM(o.total_in_cents) AS total_spent,
                RANK() OVER (
                    PARTITION BY DATE_FORMAT(o.created_at, '%Y-%m')
                    ORDER BY SUM(o.total_in_cents) DESC
                ) AS monthly_rank
            FROM users u
            JOIN orders o ON o.user_id = u.id
            WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
            GROUP BY u.id, DATE_FORMAT(o.created_at, '%Y-%m')
            HAVING monthly_rank <= 10
            ORDER BY month DESC, monthly_rank ASC
        ");
    }

    /**
     * SELECT: limit columns to reduce memory and query time
     */
    public function efficientUserList(): void
    {
        // Bad: loads ALL columns including TEXT, JSON, BLOB fields
        $users = User::all();

        // Good: loads only needed columns
        $users = User::select('id', 'name', 'email')
            ->where('is_active', true)
            ->orderBy('name')
            ->get();
        // Smaller result set, less memory, faster query
    }

    private function exportToCsv(Order $order): void
    {
        // Export logic
    }
}