Master 7 battle-tested Eloquent patterns to eliminate N+1 issues, optimize memory usage, and write maintainable Laravel database queries.

Laravel Eloquent: 7 Pattern Avanzati che Trasformeranno le Tue Query (e la Tua Sanità Mentale)

You’ve been writing Eloquent queries for years. They work. But every time you revisit that 200-line controller method with nested where clauses and duplicated filtering logic, you feel that familiar pit in your stomach. Your queries run slow in production, memory spikes during CSV exports crash your workers, and debugging N+1 issues has become a recurring nightmare.

This isn’t a beginner’s guide. We’re diving into battle-tested patterns that separate maintainable Laravel applications from unmaintainable ones. These seven advanced techniques will fundamentally change how you approach database interactions—making your code cleaner, your queries faster, and your debugging sessions shorter.

Prerequisites

Before implementing these patterns, ensure you have:

Laravel 10+ installed (patterns work with Laravel 9, but examples use modern syntax)
PHP 8.1+ for proper type hints and enums
MySQL 8.0+ or PostgreSQL 13+ for optimal index support
Laravel Debugbar or Telescope installed for query profiling

1
2
3
4
5
# Install debugging tools
composer require barryvdh/laravel-debugbar --dev
composer require laravel/telescope --dev
php artisan telescope:install
php artisan migrate

💡 Enable slow query logging in your database before starting. You’ll need it for the optimization sections.

Familiarity with basic Eloquent relationships, query builder methods, and Laravel’s service container is assumed.

Architecture and Key Concepts

The patterns we’ll implement follow a layered approach that separates concerns while maintaining Eloquent’s expressiveness where it shines.

flowchart TD
    subgraph Presentation["Presentation Layer"]
        Controller[Controller]
        FormRequest[Form Request]
    end
    
    subgraph Domain["Domain Layer"]
        QueryFilter[Query Filters]
        Scopes[Eloquent Scopes]
        Specs[Specifications]
    end
    
    subgraph Data["Data Access Layer"]
        Model[Eloquent Model]
        Repository[Repository]
        QueryBuilder[Query Builder]
    end
    
    subgraph Database["Database"]
        MySQL[(MySQL/PostgreSQL)]
        Indexes[Optimized Indexes]
    end
    
    Controller --> FormRequest
    FormRequest --> QueryFilter
    QueryFilter --> Scopes
    Scopes --> Model
    QueryFilter --> Repository
    Repository --> QueryBuilder
    Model --> MySQL
    QueryBuilder --> MySQL
    MySQL --> Indexes
    
    style Domain fill:#e1f5fe
    style Data fill:#fff3e0

The key insight: Eloquent excels at domain modeling but struggles with complex reporting queries. We’ll use each tool where it’s strongest.

Core concepts we’ll leverage:

Query Scopes — Encapsulate reusable query constraints
Pipeline Pattern — Chain filters dynamically based on request input
Repository Abstraction — Escape Eloquent when raw SQL performs better
Lazy Collections — Process millions of records with constant memory
Index-Driven Development — Let EXPLAIN guide your schema decisions

Step-by-Step Implementation

Dynamic and Composable Query Scopes

The typical approach to filtering looks like this disaster:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
// ❌ The "it works but kills maintainability" approach
public function index(Request $request)
{
    $query = Order::query();
    
    if ($request->has('status')) {
        $query->where('status', $request->status);
    }
    if ($request->has('min_amount')) {
        $query->where('total_amount', '>=', $request->min_amount);
    }
    if ($request->has('customer_id')) {
        $query->where('customer_id', $request->customer_id);
    }
    if ($request->has('date_from')) {
        $query->where('created_at', '>=', $request->date_from);
    }
    // ... 15 more conditionals
    
    return $query->paginate();
}

Let’s build a composable filter system using the Pipeline pattern with dynamic scopes.

First, create a base filter class:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<?php

namespace App\QueryFilters;

use Closure;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;

abstract class QueryFilter
{
    protected Request $request;
    
    public function __construct(Request $request)
    {
        $this->request = $request;
    }
    
    /**
     * Handle the filter pipeline
     */
    public function handle(Builder $query, Closure $next): Builder
    {
        if (!$this->shouldApply()) {
            return $next($query);
        }
        
        return $next($this->apply($query));
    }
    
    /**
     * Determine if this filter should be applied
     */
    protected function shouldApply(): bool
    {
        return $this->request->filled($this->getFilterKey());
    }
    
    /**
     * Get the request key for this filter
     */
    abstract protected function getFilterKey(): string;
    
    /**
     * Apply the filter to the query
     */
    abstract protected function apply(Builder $query): Builder;
}

Now create specific filters that handle single responsibilities:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
<?php

namespace App\QueryFilters\Orders;

use App\QueryFilters\QueryFilter;
use Illuminate\Database\Eloquent\Builder;

class StatusFilter extends QueryFilter
{
    protected function getFilterKey(): string
    {
        return 'status';
    }
    
    protected function apply(Builder $query): Builder
    {
        $statuses = (array) $this->request->input('status');
        
        return $query->whereIn('status', $statuses);
    }
}

class AmountRangeFilter extends QueryFilter
{
    protected function shouldApply(): bool
    {
        return $this->request->filled('min_amount') 
            || $this->request->filled('max_amount');
    }
    
    protected function getFilterKey(): string
    {
        return 'amount'; // Not directly used due to custom shouldApply
    }
    
    protected function apply(Builder $query): Builder
    {
        return $query
            ->when(
                $this->request->filled('min_amount'),
                fn ($q) => $q->where('total_amount', '>=', $this->request->min_amount)
            )
            ->when(
                $this->request->filled('max_amount'),
                fn ($q) => $q->where('total_amount', '<=', $this->request->max_amount)
            );
    }
}

class DateRangeFilter extends QueryFilter
{
    protected function shouldApply(): bool
    {
        return $this->request->filled('date_from') 
            || $this->request->filled('date_to');
    }
    
    protected function getFilterKey(): string
    {
        return 'date';
    }
    
    protected function apply(Builder $query): Builder
    {
        return $query
            ->when(
                $this->request->filled('date_from'),
                fn ($q) => $q->where('created_at', '>=', $this->request->date('date_from')->startOfDay())
            )
            ->when(
                $this->request->filled('date_to'),
                fn ($q) => $q->where('created_at', '<=', $this->request->date('date_to')->endOfDay())
            );
    }
}

Create a trait for your models to enable pipeline filtering:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php

namespace App\Traits;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pipeline\Pipeline;
use Illuminate\Http\Request;

trait Filterable
{
    /**
     * Apply filters through the pipeline
     * 
     * @param Builder $query
     * @param array<class-string> $filters
     * @param Request|null $request
     */
    public function scopeFilter(
        Builder $query, 
        array $filters, 
        ?Request $request = null
    ): Builder {
        $request ??= request();
        
        // Instantiate filters with request
        $filterInstances = array_map(
            fn ($filterClass) => new $filterClass($request),
            $filters
        );
        
        return app(Pipeline::class)
            ->send($query)
            ->through($filterInstances)
            ->thenReturn();
    }
}

Your controller becomes elegant:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php

namespace App\Http\Controllers;

use App\Models\Order;
use App\QueryFilters\Orders\{
    StatusFilter,
    AmountRangeFilter,
    DateRangeFilter,
    CustomerFilter,
    ProductFilter
};
use Illuminate\Http\Request;

class OrderController extends Controller
{
    private const FILTERS = [
        StatusFilter::class,
        AmountRangeFilter::class,
        DateRangeFilter::class,
        CustomerFilter::class,
        ProductFilter::class,
    ];
    
    public function index(Request $request)
    {
        $orders = Order::query()
            ->filter(self::FILTERS, $request)
            ->with(['customer', 'items.product'])
            ->latest()
            ->paginate();
            
        return view('orders.index', compact('orders'));
    }
}

📝 Each filter is now testable in isolation, reusable across controllers, and follows the single responsibility principle.

Conditional Eager Loading and Solving N+1

The N+1 problem isn’t just about adding with(). It’s about loading the right relationships at the right time—and knowing when you’ve over-fetched.

First, let’s understand the profiling workflow:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
<?php

// Enable query logging for development
// Add to AppServiceProvider::boot()
if (app()->environment('local')) {
    \DB::listen(function ($query) {
        if ($query->time > 100) { // Log queries over 100ms
            \Log::warning('Slow query detected', [
                'sql' => $query->sql,
                'bindings' => $query->bindings,
                'time' => $query->time,
            ]);
        }
    });
}

Here’s a model with strategic relationship definitions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\{BelongsTo, HasMany, HasManyThrough};

class Order extends Model
{
    // Eager load by default only lightweight relationships
    protected $with = ['status']; // Status is a small lookup table
    
    public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
    
    public function items(): HasMany
    {
        return $this->hasMany(OrderItem::class);
    }
    
    // Optimized relationship with constraints
    public function recentItems(): HasMany
    {
        return $this->items()
            ->where('created_at', '>=', now()->subDays(30))
            ->orderByDesc('created_at');
    }
    
    // Calculated relationship - use sparingly
    public function itemsWithTotals(): HasMany
    {
        return $this->items()
            ->select([
                'order_items.*',
                \DB::raw('quantity * unit_price as line_total')
            ]);
    }
    
    // Nested eager loading with constraints
    public function itemsWithProductInventory(): HasMany
    {
        return $this->items()->with([
            'product' => fn ($q) => $q->select('id', 'name', 'sku'),
            'product.inventory' => fn ($q) => $q->where('warehouse_id', config('app.primary_warehouse'))
        ]);
    }
}

Implement conditional loading based on context:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<?php

namespace App\Http\Controllers;

use App\Models\Order;
use Illuminate\Http\Request;

class OrderController extends Controller
{
    public function index(Request $request)
    {
        $query = Order::query();
        
        // Load relationships based on view requirements
        $query->when(
            $request->boolean('include_customer'),
            fn ($q) => $q->with(['customer' => fn ($cq) => $cq->select('id', 'name', 'email')])
        );
        
        $query->when(
            $request->boolean('include_items'),
            fn ($q) => $q->with(['items' => function ($iq) {
                $iq->select('id', 'order_id', 'product_id', 'quantity', 'unit_price')
                   ->with(['product:id,name,sku']);
            }])
        );
        
        // For list views, use withCount instead of loading full relationships
        $query->when(
            !$request->boolean('include_items'),
            fn ($q) => $q->withCount('items')->withSum('items', 'quantity')
        );
        
        return $query->paginate();
    }
    
    public function show(Order $order)
    {
        // Load everything needed for detail view in one query
        $order->load([
            'customer.addresses',
            'items.product.category',
            'items.product.images' => fn ($q) => $q->limit(1),
            'payments',
            'shipments.tracking'
        ]);
        
        return view('orders.show', compact('order'));
    }
}

⚠️ Never use $with property on models for heavy relationships. It forces loading even when you don’t need the data.

Use whenLoaded() in your API Resources to prevent serialization errors:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;

class OrderResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'order_number' => $this->order_number,
            'total_amount' => $this->total_amount,
            'status' => $this->status,
            'created_at' => $this->created_at->toISOString(),
            
            // Only include if relationship was eager loaded
            'customer' => CustomerResource::make($this->whenLoaded('customer')),
            'items' => OrderItemResource::collection($this->whenLoaded('items')),
            
            // Include counts if they were loaded
            'items_count' => $this->when(
                isset($this->items_count),
                $this->items_count
            ),
            
            // Conditional aggregates
            'total_quantity' => $this->when(
                isset($this->items_sum_quantity),
                $this->items_sum_quantity
            ),
        ];
    }
}

Repository Pattern vs Query Builder: Hybrid Approach

Pure repository pattern often becomes an Eloquent wrapper that adds complexity without benefits. Pure Query Builder abandons domain modeling. The hybrid approach gives you both.

flowchart LR
    subgraph Simple["Simple Operations"]
        Model[Eloquent Model]
    end
    
    subgraph Complex["Complex Queries"]
        Repo[Repository]
        QB[Query Builder]
        Raw[Raw SQL]
    end
    
    Controller --> Model
    Controller --> Repo
    Repo --> QB
    Repo --> Raw
    
    Model -->|"CRUD, Relations"| DB[(Database)]
    QB -->|"Reports, Aggregates"| DB
    Raw -->|"Optimized Analytics"| DB

Define when to use each:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
<?php

namespace App\Repositories;

use App\Models\Order;
use App\DTOs\OrderReportDTO;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class OrderRepository
{
    /**
     * Simple queries - use Eloquent
     * Rule: If it involves relationships and domain logic, use Eloquent
     */
    public function findWithDetails(int $id): ?Order
    {
        return Order::with(['customer', 'items.product'])->find($id);
    }
    
    /**
     * Complex aggregations - use Query Builder
     * Rule: If you need complex JOINs or aggregates, escape Eloquent
     */
    public function getSalesReportByCategory(
        \DateTimeInterface $startDate,
        \DateTimeInterface $endDate
    ): Collection {
        return DB::table('orders')
            ->join('order_items', 'orders.id', '=', 'order_items.order_id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->join('categories', 'products.category_id', '=', 'categories.id')
            ->select([
                'categories.id as category_id',
                'categories.name as category_name',
                DB::raw('COUNT(DISTINCT orders.id) as order_count'),
                DB::raw('SUM(order_items.quantity) as total_quantity'),
                DB::raw('SUM(order_items.quantity * order_items.unit_price) as total_revenue'),
                DB::raw('AVG(order_items.unit_price) as avg_unit_price'),
            ])
            ->whereBetween('orders.created_at', [$startDate, $endDate])
            ->where('orders.status', 'completed')
            ->groupBy('categories.id', 'categories.name')
            ->orderByDesc('total_revenue')
            ->get()
            ->map(fn ($row) => OrderReportDTO::fromRow($row));
    }
    
    /**
     * Performance-critical queries - use raw SQL
     * Rule: When EXPLAIN shows the ORM generates suboptimal queries
     */
    public function getTopCustomersByLifetimeValue(int $limit = 100): Collection
    {
        $sql = <<<SQL
            WITH customer_orders AS (
                SELECT 
                    customer_id,
                    COUNT(*) as order_count,
                    SUM(total_amount) as lifetime_value,
                    MIN(created_at) as first_order_at,
                    MAX(created_at) as last_order_at
                FROM orders
                WHERE status = 'completed'
                GROUP BY customer_id
            )
            SELECT 
                c.id,
                c.name,
                c.email,
                co.order_count,
                co.lifetime_value,
                co.first_order_at,
                co.last_order_at,
                DATEDIFF(NOW(), co.last_order_at) as days_since_last_order
            FROM customers c
            INNER JOIN customer_orders co ON c.id = co.customer_id
            ORDER BY co.lifetime_value DESC
            LIMIT ?
        SQL;
        
        return collect(DB::select($sql, [$limit]));
    }
    
    /**
     * Hybrid: Start with Eloquent, optimize specific parts
     */
    public function getOrdersWithCalculatedMetrics(array $filters): \Illuminate\Pagination\LengthAwarePaginator
    {
        return Order::query()
            ->filter($filters)
            ->select([
                'orders.*',
                // Subquery for item count (avoids N+1)
                DB::raw('(SELECT COUNT(*) FROM order_items WHERE order_items.order_id = orders.id) as items_count'),
                // Subquery for calculated total (if not denormalized)
                DB::raw('(SELECT SUM(quantity * unit_price) FROM order_items WHERE order_items.order_id = orders.id) as calculated_total'),
            ])
            ->with(['customer:id,name,email']) // Still use Eloquent for simple relations
            ->paginate();
    }
}

Create a DTO for type-safe report data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php

namespace App\DTOs;

readonly class OrderReportDTO
{
    public function __construct(
        public int $categoryId,
        public string $categoryName,
        public int $orderCount,
        public int $totalQuantity,
        public float $totalRevenue,
        public float $avgUnitPrice,
    ) {}
    
    public static function fromRow(object $row): self
    {
        return new self(
            categoryId: (int) $row->category_id,
            categoryName: $row->category_name,
            orderCount: (int) $row->order_count,
            totalQuantity: (int) $row->total_quantity,
            totalRevenue: (float) $row->total_revenue,
            avgUnitPrice: (float) $row->avg_unit_price,
        );
    }
}

💡 Use repositories for complex read operations. Keep Eloquent models for write operations where you need events, observers, and validation.

Production Configuration

Database Connection Optimization

Production environments demand careful query tuning. Configure your database connections for optimal Eloquent performance:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<?php
// config/database.php

return [
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            
            // Production optimizations
            'options' => extension_loaded('pdo_mysql') ? [
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                PDO::ATTR_STRINGIFY_FETCHES => false,
            ] : [],
        ],
        
        // Read replica for heavy queries
        'mysql_read' => [
            'read' => [
                'host' => [
                    env('DB_READ_HOST_1', '127.0.0.1'),
                    env('DB_READ_HOST_2', '127.0.0.1'),
                ],
            ],
            'write' => [
                'host' => env('DB_WRITE_HOST', '127.0.0.1'),
            ],
            'sticky' => true, // Use write connection after writes
            'driver' => 'mysql',
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ],
    ],
];

Query Caching Strategy

Implement a robust caching layer for expensive queries:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
<?php

namespace App\Services;

use App\Models\Product;
use Illuminate\Support\Facades\Cache;
use Illuminate\Database\Eloquent\Collection;

class CachedProductService
{
    private const CACHE_TTL = 3600; // 1 hour
    
    public function getFeaturedProducts(): Collection
    {
        return Cache::tags(['products', 'featured'])
            ->remember(
                'products:featured:' . now()->format('Y-m-d'),
                self::CACHE_TTL,
                fn () => Product::query()
                    ->with(['category:id,name', 'images' => fn ($q) => $q->limit(1)])
                    ->featured()
                    ->available()
                    ->orderByDesc('popularity_score')
                    ->limit(20)
                    ->get()
            );
    }
    
    public function getCategoryProducts(int $categoryId, array $filters = []): Collection
    {
        $cacheKey = $this->buildCacheKey('category', $categoryId, $filters);
        
        return Cache::tags(['products', "category:{$categoryId}"])
            ->remember($cacheKey, self::CACHE_TTL, function () use ($categoryId, $filters) {
                return Product::query()
                    ->forCategory($categoryId)
                    ->when($filters['price_min'] ?? null, fn ($q, $min) => 
                        $q->where('price', '>=', $min)
                    )
                    ->when($filters['price_max'] ?? null, fn ($q, $max) => 
                        $q->where('price', '<=', $max)
                    )
                    ->when($filters['sort'] ?? null, fn ($q, $sort) => 
                        $this->applySorting($q, $sort)
                    )
                    ->paginate($filters['per_page'] ?? 24);
            });
    }
    
    private function buildCacheKey(string $prefix, int $id, array $filters): string
    {
        $filterHash = md5(serialize($filters));
        return "{$prefix}:{$id}:filters:{$filterHash}";
    }
    
    private function applySorting($query, string $sort)
    {
        return match($sort) {
            'price_asc' => $query->orderBy('price'),
            'price_desc' => $query->orderByDesc('price'),
            'newest' => $query->orderByDesc('created_at'),
            'popular' => $query->orderByDesc('sales_count'),
            default => $query->orderByDesc('popularity_score'),
        };
    }
    
    // Invalidate cache when products change
    public function invalidateProductCache(Product $product): void
    {
        Cache::tags(['products'])->flush();
        
        if ($product->category_id) {
            Cache::tags(["category:{$product->category_id}"])->flush();
        }
    }
}

Model Event Handling for Cache Invalidation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php

namespace App\Observers;

use App\Models\Product;
use App\Services\CachedProductService;

class ProductObserver
{
    public function __construct(
        private CachedProductService $cacheService
    ) {}
    
    public function saved(Product $product): void
    {
        $this->cacheService->invalidateProductCache($product);
    }
    
    public function deleted(Product $product): void
    {
        $this->cacheService->invalidateProductCache($product);
    }
}

💡 Use cache tags strategically. They let you invalidate related cache entries without affecting unrelated data.

Query Monitoring Configuration

Set up comprehensive query logging for production debugging:

1
2
3
4
5
6
7
8
9
# config/logging.php channels section (as array)
# Add this to your logging configuration

channels:
  query:
    driver: daily
    path: storage/logs/queries.log
    level: debug
    days: 7

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?php
// app/Providers/AppServiceProvider.php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Database\Events\QueryExecuted;

class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        if (config('app.debug') || config('app.query_logging')) {
            $this->enableQueryLogging();
        }
    }
    
    private function enableQueryLogging(): void
    {
        DB::listen(function (QueryExecuted $query) {
            $threshold = config('database.slow_query_threshold', 100); // ms
            
            if ($query->time >= $threshold) {
                Log::channel('query')->warning('Slow query detected', [
                    'sql' => $query->sql,
                    'bindings' => $query->bindings,
                    'time_ms' => $query->time,
                    'connection' => $query->connectionName,
                    'trace' => collect(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10))
                        ->filter(fn ($trace) => 
                            isset($trace['file']) && 
                            !str_contains($trace['file'], 'vendor')
                        )
                        ->take(5)
                        ->values()
                        ->toArray(),
                ]);
            }
        });
    }
}

Common Mistakes and Troubleshooting

The N+1 Detection System

Build an automated N+1 detection system that catches issues before they reach production:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
<?php

namespace App\Support;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class NPlus1Detector
{
    private array $queryPatterns = [];
    private int $threshold = 5; // Alert after 5 similar queries
    
    public function enable(): void
    {
        if (!app()->environment('local', 'testing')) {
            return;
        }
        
        DB::listen(function ($query) {
            // Normalize the query by removing specific values
            $pattern = preg_replace('/\d+/', '?', $query->sql);
            $pattern = preg_replace('/\'[^\']*\'/', '?', $pattern);
            
            $this->queryPatterns[$pattern] = ($this->queryPatterns[$pattern] ?? 0) + 1;
            
            if ($this->queryPatterns[$pattern] === $this->threshold) {
                $this->reportPotentialNPlus1($query->sql, $pattern);
            }
        });
    }
    
    private function reportPotentialNPlus1(string $sql, string $pattern): void
    {
        $trace = collect(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 20))
            ->filter(fn ($t) => isset($t['file']) && !str_contains($t['file'], 'vendor'))
            ->first();
        
        Log::warning('Potential N+1 query detected', [
            'pattern' => $pattern,
            'example_sql' => $sql,
            'count' => $this->queryPatterns[$pattern],
            'location' => $trace ? "{$trace['file']}:{$trace['line']}" : 'unknown',
        ]);
        
        if (app()->environment('local')) {
            dump("⚠️ N+1 detected: {$pattern} (executed {$this->queryPatterns[$pattern]} times)");
        }
    }
    
    public function reset(): void
    {
        $this->queryPatterns = [];
    }
    
    public function getReport(): array
    {
        return collect($this->queryPatterns)
            ->filter(fn ($count) => $count >= $this->threshold)
            ->sortDesc()
            ->toArray();
    }
}

⚠️ Never enable detailed query logging in production without log rotation. Query logs can grow to gigabytes within hours on busy systems.

Common Pitfalls and Solutions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
<?php

namespace App\Examples;

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

class CommonMistakes
{
    // ❌ MISTAKE: Loading all records to count them
    public function badCount(): int
    {
        return User::all()->count(); // Loads ALL users into memory
    }
    
    // ✅ FIX: Use query count
    public function goodCount(): int
    {
        return User::count(); // SELECT COUNT(*) - single value
    }
    
    // ❌ MISTAKE: Checking existence by loading the model
    public function badExists(string $email): bool
    {
        return User::where('email', $email)->first() !== null;
    }
    
    // ✅ FIX: Use exists()
    public function goodExists(string $email): bool
    {
        return User::where('email', $email)->exists();
    }
    
    // ❌ MISTAKE: Updating inside a loop
    public function badMassUpdate(array $userIds, string $status): void
    {
        foreach ($userIds as $id) {
            User::find($id)->update(['status' => $status]);
        }
    }
    
    // ✅ FIX: Single query update
    public function goodMassUpdate(array $userIds, string $status): int
    {
        return User::whereIn('id', $userIds)->update(['status' => $status]);
    }
    
    // ❌ MISTAKE: Ordering by related data without join
    public function badOrderByRelation()
    {
        // This won't work as expected
        return Order::with('customer')
            ->orderBy('customer.name')
            ->get();
    }
    
    // ✅ FIX: Use join for ordering by related columns
    public function goodOrderByRelation()
    {
        return Order::query()
            ->select('orders.*')
            ->join('customers', 'orders.customer_id', '=', 'customers.id')
            ->orderBy('customers.name')
            ->with('customer') // Still eager load for access
            ->get();
    }
    
    // ❌ MISTAKE: Using orWhere without grouping
    public function badOrWhere(string $search)
    {
        // This produces: WHERE active = 1 AND name LIKE ? OR email LIKE ?
        // Which matches ALL inactive users with matching email!
        return User::where('active', true)
            ->where('name', 'like', "%{$search}%")
            ->orWhere('email', 'like', "%{$search}%")
            ->get();
    }
    
    // ✅ FIX: Group OR conditions
    public function goodOrWhere(string $search)
    {
        return User::where('active', true)
            ->where(function ($query) use ($search) {
                $query->where('name', 'like', "%{$search}%")
                      ->orWhere('email', 'like', "%{$search}%");
            })
            ->get();
    }
}

Debugging Complex Queries

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<?php

namespace App\Support;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Log;

trait DebuggableQueries
{
    public function scopeDebug(Builder $query): Builder
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();
        
        // Replace placeholders with actual values for readability
        $fullSql = $sql;
        foreach ($bindings as $binding) {
            $value = is_numeric($binding) ? $binding : "'{$binding}'";
            $fullSql = preg_replace('/\?/', $value, $fullSql, 1);
        }
        
        dump([
            'sql' => $sql,
            'bindings' => $bindings,
            'full_sql' => $fullSql,
        ]);
        
        return $query;
    }
    
    public function scopeExplain(Builder $query): array
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();
        
        return DB::select("EXPLAIN {$sql}", $bindings);
    }
    
    public function scopeExplainAnalyze(Builder $query): array
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();
        
        // MySQL 8.0+ only
        return DB::select("EXPLAIN ANALYZE {$sql}", $bindings);
    }
}

// Usage:
// Product::where('active', true)->debug()->get();
// Product::where('active', true)->explain();

📝 Add the DebuggableQueries trait to your base model or specific models that need query debugging capabilities.

Performance and Scalability

Query Architecture Overview

flowchart TD
    subgraph Request["Incoming Request"]
        A[Controller] --> B{Cache Hit?}
    end
    
    subgraph Cache["Cache Layer"]
        B -->|Yes| C[Return Cached]
        B -->|No| D[Query Builder]
    end
    
    subgraph Query["Query Execution"]
        D --> E{Read/Write?}
        E -->|Read| F[Read Replica]
        E -->|Write| G[Primary DB]
    end
    
    subgraph Optimization["Query Optimization"]
        F --> H[Index Lookup]
        G --> H
        H --> I{Result Size?}
        I -->|Large| J[Chunked Processing]
        I -->|Small| K[Direct Return]
    end
    
    subgraph Response["Response"]
        J --> L[Stream Response]
        K --> M[Cache Result]
        M --> C
        L --> N[Complete]
        C --> N
    end

Indexing Strategy

Create migrations that define proper indexes for your query patterns:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            // Composite index for common filter combinations
            $table->index(['status', 'created_at'], 'orders_status_created_idx');
            
            // Index for customer lookups with date range
            $table->index(['customer_id', 'created_at'], 'orders_customer_date_idx');
            
            // Partial index simulation: only index recent orders
            // (Use raw SQL for actual partial indexes in PostgreSQL)
            $table->index(['status', 'total_amount'], 'orders_status_amount_idx');
        });
        
        Schema::table('products', function (Blueprint $table) {
            // Full-text search index
            $table->fullText(['name', 'description'], 'products_search_idx');
            
            // Index for category browsing with sorting
            $table->index(['category_id', 'popularity_score'], 'products_category_popular_idx');
            
            // Covering index for listing pages
            $table->index(
                ['category_id', 'active', 'price', 'name'],
                'products_listing_idx'
            );
        });
    }
    
    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex('orders_status_created_idx');
            $table->dropIndex('orders_customer_date_idx');
            $table->dropIndex('orders_status_amount_idx');
        });
        
        Schema::table('products', function (Blueprint $table) {
            $table->dropIndex('products_search_idx');
            $table->dropIndex('products_category_popular_idx');
            $table->dropIndex('products_listing_idx');
        });
    }
};

Batch Processing for Large Datasets

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
<?php

namespace App\Jobs;

use App\Models\Order;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;

class ProcessLargeOrderReport implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    
    public function __construct(
        private string $startDate,
        private string $endDate,
        private string $outputPath
    ) {}
    
    public function handle(): void
    {
        $handle = fopen($this->outputPath, 'w');
        
        // Write CSV header
        fputcsv($handle, [
            'Order ID', 'Customer', 'Total', 'Status', 'Created At'
        ]);
        
        // Process in chunks to manage memory
        Order::query()
            ->whereBetween('created_at', [$this->startDate, $this->endDate])
            ->with('customer:id,name,email')
            ->select(['id', 'customer_id', 'total_amount', 'status', 'created_at'])
            ->chunkById(1000, function ($orders) use ($handle) {
                foreach ($orders as $order) {
                    fputcsv($handle, [
                        $order->id,
                        $order->customer->name ?? 'N/A',
                        $order->total_amount,
                        $order->status,
                        $order->created_at->toDateTimeString(),
                    ]);
                }
                
                // Clear memory after each chunk
                gc_collect_cycles();
            });
        
        fclose($handle);
    }
    
    // Alternative: Use lazy collections for streaming
    public function handleWithLazy(): void
    {
        $orders = Order::query()
            ->whereBetween('created_at', [$this->startDate, $this->endDate])
            ->with('customer:id,name,email')
            ->lazy(1000);
        
        $handle = fopen($this->outputPath, 'w');
        fputcsv($handle, ['Order ID', 'Customer', 'Total', 'Status', 'Created At']);
        
        foreach ($orders as $order) {
            fputcsv($handle, [
                $order->id,
                $order->customer->name ?? 'N/A',
                $order->total_amount,
                $order->status,
                $order->created_at->toDateTimeString(),
            ]);
        }
        
        fclose($handle);
    }
}

Connection Pooling with Octane

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?php
// config/octane.php

return [
    'server' => env('OCTANE_SERVER', 'swoole'),
    
    'listeners' => [
        // Reset query log between requests
        \Laravel\Octane\Events\RequestReceived::class => [
            fn () => DB::flushQueryLog(),
        ],
        
        \Laravel\Octane\Events\RequestTerminated::class => [
            // Ensure connections are properly returned to pool
            fn () => DB::purge(),
        ],
    ],
    
    'warm' => [
        // Pre-warm these services
        \App\Services\CachedProductService::class,
        \App\Repositories\OrderRepository::class,
    ],
    
    'tables' => [
        // Swoole tables for cross-worker caching
        'product_cache' => [
            'columns' => [
                ['name' => 'data', 'type' => 'string', 'size' => 10000],
            ],
            'rows' => 1000,
        ],
    ],
];

💡 When using Laravel Octane, be extra careful with static variables and singleton patterns. Query builders should never be stored across requests.

Conclusion and Next Steps

You now have seven advanced Eloquent patterns that will fundamentally change how you write Laravel database code:

Query Scopes — Encapsulate business logic in reusable, chainable methods
Eager Loading Optimization — Eliminate N+1 queries with strategic relationship loading
Subquery Selects — Add computed columns without post-processing
Dynamic Relationships — Build relationships that adapt to runtime conditions
Cursor Pagination — Handle massive datasets without offset performance degradation
Raw Expressions — Access full SQL power when Eloquent abstractions fall short
Repository Pattern — Separate complex read operations from model responsibilities

These patterns share a common philosophy: use the right tool for each situation. Eloquent excels at domain modeling, event handling, and simple CRUD operations. Raw queries shine for complex reporting. Repositories bridge the gap.

Your immediate action items:

Audit your existing code for N+1 queries using the detector pattern shown above
Refactor one complex controller method to use query scopes
Implement cursor pagination on at least one paginated listing
Set up query logging in your development environment

The patterns compound over time. A well-structured query scope today becomes the foundation for a complex report tomorrow. Start small, measure the impact, and iterate.

Additional Resources

Laravel Eloquent Documentation — Official documentation covering all Eloquent features with examples
Use The Index, Luke — Comprehensive guide to database indexing that applies directly to optimizing Eloquent queries
Laravel News: Eloquent Performance Patterns — Collection of real-world performance optimization techniques
Spatie Laravel Query Builder — Package that builds on these patterns for API filtering and sorting
MySQL EXPLAIN Demystified — Understanding query execution plans to validate your optimizations

Common Mistakes and Troubleshooting

Mistake #1: N+1 Queries Hiding in Accessors

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// ❌ WRONG: Hidden N+1 in accessor
class Order extends Model
{
    // This accessor triggers a query EVERY time it's accessed
    public function getTotalWithTaxAttribute(): float
    {
        // Queries the taxRate relationship if not loaded
        return $this->subtotal * (1 + $this->taxRate->percentage / 100);
    }
}

// Using it in a loop = disaster
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->total_with_tax; // N+1 queries!
}

// ✅ CORRECT: Defensive accessor with eager load check
class Order extends Model
{
    public function getTotalWithTaxAttribute(): float
    {
        // Check if relationship is loaded, throw if not
        if (!$this->relationLoaded('taxRate')) {
            throw new LogicException(
                'taxRate relationship must be eager loaded before accessing total_with_tax'
            );
        }
        
        return $this->subtotal * (1 + $this->taxRate->percentage / 100);
    }
}

// Or use withDefault to avoid the query
public function taxRate(): BelongsTo
{
    return $this->belongsTo(TaxRate::class)->withDefault([
        'percentage' => 0
    ]);
}

Mistake #2: Overusing Global Scopes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
// ❌ WRONG: Global scope that breaks everything
class Product extends Model
{
    protected static function booted(): void
    {
        // This affects ALL queries, including admin panels
        static::addGlobalScope('active', function (Builder $builder) {
            $builder->where('is_active', true);
        });
    }
}

// Now you can't see inactive products anywhere without remembering to remove scope
Product::withoutGlobalScope('active')->get(); // Easy to forget

// ✅ CORRECT: Use local scopes with explicit intent
class Product extends Model
{
    public function scopeActive(Builder $query): Builder
    {
        return $query->where('is_active', true);
    }
    
    public function scopeForStorefront(Builder $query): Builder
    {
        return $query->active()
                     ->where('stock', '>', 0)
                     ->whereNotNull('published_at');
    }
}

// Explicit and clear
Product::forStorefront()->get();  // Customer-facing
Product::all();                    // Admin sees everything

⚠️ Warning: Global scopes are powerful but dangerous. Use them only for true multi-tenancy or soft deletes. For business logic filtering, prefer explicit local scopes.

Mistake #3: Chunking Without Proper Memory Management

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// ❌ WRONG: Accumulating data defeats chunking purpose
$allUsers = collect();
User::chunk(1000, function ($users) use (&$allUsers) {
    $allUsers = $allUsers->merge($users); // Memory keeps growing!
});

// ❌ WRONG: Using chunk when you need to modify records
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        $user->update(['processed' => true]); // Breaks chunk pagination!
    }
});

// ✅ CORRECT: Use chunkById for updates
User::where('processed', false)
    ->chunkById(1000, function ($users) {
        foreach ($users as $user) {
            $user->update(['processed' => true]);
        }
    });

// ✅ CORRECT: Use lazy() for memory-efficient iteration
User::where('active', true)
    ->lazy(1000)
    ->each(function ($user) {
        // Process one at a time, memory stays constant
        dispatch(new ProcessUserJob($user));
    });

Mistake #4: Ignoring Query Execution Plans

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
// Debugging slow queries properly
class QueryDebugger
{
    public static function explain(Builder $query): array
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();
        
        // Get the raw SQL with bindings
        $rawSql = vsprintf(
            str_replace('?', "'%s'", $sql),
            $bindings
        );
        
        // Run EXPLAIN ANALYZE
        $explanation = DB::select("EXPLAIN ANALYZE {$rawSql}");
        
        return [
            'sql' => $rawSql,
            'explain' => $explanation,
            'warnings' => self::analyzeExplanation($explanation)
        ];
    }
    
    private static function analyzeExplanation(array $explanation): array
    {
        $warnings = [];
        
        foreach ($explanation as $row) {
            // Check for full table scans
            if (str_contains($row->EXPLAIN ?? '', 'Seq Scan')) {
                $warnings[] = 'Sequential scan detected - consider adding an index';
            }
            
            // Check for filesort
            if (isset($row->Extra) && str_contains($row->Extra, 'Using filesort')) {
                $warnings[] = 'Filesort detected - ORDER BY not using index';
            }
            
            // Check for temporary tables
            if (isset($row->Extra) && str_contains($row->Extra, 'Using temporary')) {
                $warnings[] = 'Temporary table created - query may be slow';
            }
        }
        
        return $warnings;
    }
}

// Usage in development
$debug = QueryDebugger::explain(
    Order::with('items')
        ->whereBetween('created_at', [now()->subMonth(), now()])
        ->orderBy('total', 'desc')
);

dump($debug);

Troubleshooting Decision Tree

flowchart TD
    A[Query is Slow] --> B{Check Query Count}
    B -->|High Count| C[N+1 Problem]
    B -->|Normal Count| D{Check Single Query Time}
    
    C --> E[Add eager loading<br/>with/load]
    
    D -->|>100ms| F{Check EXPLAIN}
    D -->|<100ms| G[Check Memory Usage]
    
    F -->|Seq Scan| H[Add Missing Index]
    F -->|Filesort| I[Optimize ORDER BY]
    F -->|Good Plan| J[Check Data Volume]
    
    G -->|High| K[Use Chunking/Lazy]
    G -->|Normal| L[Check Serialization]
    
    J --> M[Implement Pagination<br/>or Caching]
    
    H --> N[✅ Solved]
    I --> N
    E --> N
    K --> N
    M --> N

💡 Tip: Install Laravel Debugbar in development. It shows query count, execution time, and duplicate queries at a glance. Most N+1 issues become obvious immediately.

Mistake #5: Not Using Database Transactions Properly

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
// ❌ WRONG: Partial failures leave inconsistent data
public function createOrderWithItems(array $data): Order
{
    $order = Order::create($data['order']);
    
    foreach ($data['items'] as $item) {
        // If this fails halfway, you have orphaned order
        $order->items()->create($item);
    }
    
    return $order;
}

// ✅ CORRECT: Atomic operations with proper error handling
public function createOrderWithItems(array $data): Order
{
    return DB::transaction(function () use ($data) {
        $order = Order::create($data['order']);
        
        // Bulk insert is faster and atomic
        $items = array_map(fn($item) => [
            ...$item,
            'order_id' => $order->id,
            'created_at' => now(),
            'updated_at' => now(),
        ], $data['items']);
        
        OrderItem::insert($items);
        
        // Reload with items for return
        return $order->load('items');
    }, attempts: 3); // Retry on deadlock
}

Conclusion and Next Steps

These seven Eloquent patterns represent the difference between code that works and code that scales. Let’s recap what you’ve learned:

Query Scopes — Encapsulate business logic into reusable, chainable methods
Subquery Selects — Eliminate N+1 with calculated columns at query level
Dynamic Relationships — Leverage whereHas and withCount for complex filtering
Chunking & Lazy Collections — Process millions of records without exhausting memory
Raw Expressions — When the ORM isn’t enough, use safe raw SQL
Custom Casts — Transform data at the model boundary for cleaner code
Conditional Eager Loading — Load relationships based on context

Implementation Priority

Start with the patterns that give you the biggest wins:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# Priority matrix for implementation
high_impact_low_effort:
  - Query scopes for repeated WHERE clauses
  - withCount for avoiding N+1 on counts
  - Chunking for any batch processing

high_impact_high_effort:
  - Subquery selects for dashboard queries
  - Custom casts for domain objects
  - Dynamic relationship loading

low_impact_high_effort:
  - Raw expressions (use sparingly)
  - Complex conditional eager loading

Your Next Steps

Audit your current queries — Install Debugbar and identify your top 5 slowest endpoints
Pick one pattern — Don’t try to refactor everything. Start with query scopes
Measure before and after — Use EXPLAIN ANALYZE to verify improvements
Document patterns for your team — Create a coding standard that enforces these practices

📝 Note: Remember that premature optimization is the root of all evil. Profile first, then optimize. A “slow” query that runs once per hour doesn’t need the same attention as one that runs 10,000 times per minute.

The patterns in this article aren’t theoretical — they’re battle-tested solutions from production applications handling millions of records. Master them, and you’ll write Eloquent code that’s not just functional, but genuinely elegant.

Additional Resources

Laravel Eloquent Official Documentation — Complete reference for all Eloquent features and methods
Laravel Query Builder Documentation — Deep dive into the underlying query builder that powers Eloquent
Laravel Database Performance Tips — Official guide on model pruning and maintenance
MySQL Performance Blog — Advanced database optimization techniques from Percona
Laravel Daily YouTube Channel — Practical Laravel tutorials with real-world performance examples

7 Advanced Laravel Eloquent Patterns for Cleaner Queries

Laravel Eloquent: 7 Pattern Avanzati che Trasformeranno le Tue Query (e la Tua Sanità Mentale)

Prerequisites

Architecture and Key Concepts

Step-by-Step Implementation

Dynamic and Composable Query Scopes

Conditional Eager Loading and Solving N+1

Repository Pattern vs Query Builder: Hybrid Approach

Production Configuration

Database Connection Optimization

Query Caching Strategy

Model Event Handling for Cache Invalidation

Query Monitoring Configuration

Common Mistakes and Troubleshooting

The N+1 Detection System

Common Pitfalls and Solutions

Debugging Complex Queries

Performance and Scalability

Query Architecture Overview

Indexing Strategy

Batch Processing for Large Datasets

Connection Pooling with Octane

Conclusion and Next Steps

Additional Resources

Common Mistakes and Troubleshooting

Mistake #1: N+1 Queries Hiding in Accessors

Mistake #2: Overusing Global Scopes

Mistake #3: Chunking Without Proper Memory Management

Mistake #4: Ignoring Query Execution Plans

Troubleshooting Decision Tree

Mistake #5: Not Using Database Transactions Properly

Conclusion and Next Steps

Implementation Priority

Your Next Steps

Additional Resources