1
2
3
4
5

# Setup rapido per gli strumenti di profiling
composer require barryvdh/laravel-debugbar --dev
composer require laravel/telescope --dev
php artisan telescope:install
php artisan migrate

 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

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Carbon;

class Product extends Model
{
    // Scope base: prodotti attivi
    public function scopeActive(Builder $query): Builder
    {
        return $query->where('status', 'active');
    }

    // Scope base: prodotti pubblicati
    public function scopePublished(Builder $query): Builder
    {
        return $query->where('published_at', '<=', now());
    }

    // Scope dinamico: filtro per prezzo con range opzionale
    public function scopePriceRange(
        Builder $query, 
        ?float $min = null, 
        ?float $max = null
    ): Builder {
        // Applica filtri solo se i valori sono forniti
        return $query
            ->when($min !== null, fn($q) => $q->where('price', '>=', $min))
            ->when($max !== null, fn($q) => $q->where('price', '<=', $max));
    }

    // Scope composito: combina più scope in uno scenario comune
    public function scopeAvailableForSale(Builder $query): Builder
    {
        return $query
            ->active()
            ->published()
            ->where('stock_quantity', '>', 0);
    }

    // Scope con logica condizionale complessa
    public function scopeFilterBy(Builder $query, array $filters): Builder
    {
        return $query
            ->when(
                $filters['category'] ?? null,
                fn($q, $category) => $q->where('category_id', $category)
            )
            ->when(
                $filters['brand'] ?? null,
                fn($q, $brand) => $q->whereIn('brand_id', (array) $brand)
            )
            ->when(
                $filters['search'] ?? null,
                fn($q, $search) => $q->where(function($q) use ($search) {
                    // Ricerca su più colonne
                    $q->where('name', 'like', "%{$search}%")
                      ->orWhere('sku', 'like', "%{$search}%")
                      ->orWhere('description', 'like', "%{$search}%");
                })
            )
            ->when(
                isset($filters['in_stock']) && $filters['in_stock'],
                fn($q) => $q->where('stock_quantity', '>', 0)
            )
            ->when(
                $filters['min_price'] ?? $filters['max_price'] ?? null,
                fn($q) => $q->priceRange(
                    $filters['min_price'] ?? null,
                    $filters['max_price'] ?? null
                )
            );
    }
}

 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

<?php

namespace App\Http\Controllers;

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

class ProductController extends Controller
{
    public function index(Request $request)
    {
        // Prima: 30 righe di where() duplicate
        // Dopo: una riga leggibile e manutenibile
        $products = Product::query()
            ->availableForSale()
            ->filterBy($request->only([
                'category', 
                'brand', 
                'search', 
                'in_stock',
                'min_price',
                'max_price'
            ]))
            ->orderBy($request->input('sort', 'created_at'), $request->input('dir', 'desc'))
            ->paginate(20);

        return view('products.index', compact('products'));
    }
}

 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\Filters;

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

abstract class QueryFilter
{
    protected Request $request;
    protected Builder $builder;

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    public function apply(Builder $builder): Builder
    {
        $this->builder = $builder;

        // Itera su tutti i parametri della request
        foreach ($this->request->all() as $filter => $value) {
            // Converte 'min_price' in 'minPrice' per il metodo
            $method = \Str::camel($filter);
            
            // Se esiste il metodo e il valore non è vuoto, applicalo
            if (method_exists($this, $method) && $value !== null && $value !== '') {
                $this->$method($value);
            }
        }

        return $this->builder;
    }
}

// Implementazione specifica per Product
class ProductFilter extends QueryFilter
{
    public function category(int $categoryId): void
    {
        $this->builder->where('category_id', $categoryId);
    }

    public function minPrice(float $price): void
    {
        $this->builder->where('price', '>=', $price);
    }

    public function maxPrice(float $price): void
    {
        $this->builder->where('price', '<=', $price);
    }

    public function search(string $term): void
    {
        $this->builder->where(function ($q) use ($term) {
            $q->where('name', 'like', "%{$term}%")
              ->orWhere('description', 'like', "%{$term}%");
        });
    }
}

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

<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        // In development: lancia eccezione per lazy loading
        // Questo FORZA l'uso di eager loading
        Model::preventLazyLoading(!app()->isProduction());

        // In produzione: logga invece di bloccare
        Model::handleLazyLoadingViolationUsing(function ($model, $relation) {
            \Log::warning("Lazy loading detected: {$model}::{$relation}");
        });
    }
}

 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

namespace App\Http\Controllers;

use App\Models\Order;

class OrderController extends Controller
{
    public function show(int $id)
    {
        // SBAGLIATO: genera N+1 query
        // $order = Order::find($id);
        // foreach ($order->items as $item) {
        //     echo $item->product->name; // Query per ogni item!
        // }

        // CORRETTO: eager loading con relazioni nested
        $order = Order::with([
            'customer',                          // 1 query
            'items.product.category',            // 2 query (items + products con category)
            'items.product.images' => function ($query) {
                // Carica solo le immagini principali
                $query->where('is_primary', true);
            },
            'shippingAddress',                   // 1 query
            'payments' => function ($query) {
                // Ordina i pagamenti per data
                $query->latest('processed_at');
            }
        ])->findOrFail($id);

        return view('orders.show', compact('order'));
    }

    public function index()
    {
        // Eager loading con conteggio relazioni
        $orders = Order::query()
            ->with(['customer', 'shippingAddress'])
            ->withCount('items')           // Aggiunge items_count
            ->withSum('items', 'quantity') // Aggiunge items_sum_quantity
            ->withAvg('items', 'price')    // Aggiunge items_avg_price
            ->latest()
            ->paginate(25);

        return view('orders.index', compact('orders'));
    }
}

 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

<?php

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;

class OrderResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id' => $this->id,
            'total' => $this->total,
            'status' => $this->status,
            'created_at' => $this->created_at->toISOString(),
            
            // whenLoaded: include SOLO se la relazione è già caricata
            // Non genera query aggiuntive se non serve
            'customer' => CustomerResource::make($this->whenLoaded('customer')),
            
            // Condizione + relazione
            'items' => $this->when(
                $this->relationLoaded('items'),
                fn() => OrderItemResource::collection($this->items)
            ),
            
            // Conteggi condizionali
            'items_count' => $this->when(
                isset($this->items_count),
                $this->items_count
            ),
            
            // Dati sensibili solo per admin
            'internal_notes' => $this->when(
                $request->user()?->isAdmin(),
                $this->internal_notes
            ),
        ];
    }
}

 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

<?php
// config/debugbar.php

return [
    'enabled' => env('DEBUGBAR_ENABLED', false),
    
    'collectors' => [
        'queries' => true,    // FONDAMENTALE: traccia tutte le query
        'models' => true,     // Mostra modelli caricati
        'time' => true,
    ],
    
    'options' => [
        'queries' => [
            'timeline' => true,          // Visualizza query nella timeline
            'explain' => [
                'enabled' => true,        // Mostra EXPLAIN per ogni query
                'types' => ['SELECT'],    // Solo per SELECT
            ],
            'hints' => true,              // Suggerimenti per ottimizzazione
            'show_copy' => true,          // Bottone per copiare query
            'slow_threshold' => 100,      // Evidenzia query > 100ms
        ],
    ],
];

  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
103
104
105
106
107
108
109
110

<?php

namespace App\Repositories;

use App\Models\Order;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Facades\DB;

class OrderRepository
{
    // Metodi semplici: delega a Eloquent
    public function find(int $id): ?Order
    {
        return Order::find($id);
    }

    public function findWithDetails(int $id): ?Order
    {
        return Order::with([
            'customer', 
            'items.product', 
            'payments'
        ])->find($id);
    }

    // Query complessa: usa Query Builder per performance
    public function getSalesReport(
        string $startDate, 
        string $endDate,
        ?int $categoryId = null
    ): array {
        // Per report complessi, Query Builder batte Eloquent
        $query = DB::table('orders as o')
            ->join('order_items as oi', 'o.id', '=', 'oi.order_id')
            ->join('products as p', 'oi.product_id', '=', 'p.id')
            ->whereBetween('o.created_at', [$startDate, $endDate])
            ->where('o.status', 'completed')
            ->when($categoryId, fn($q) => $q->where('p.category_id', $categoryId));

        return $query
            ->select([
                DB::raw('DATE(o.created_at) as date'),
                DB::raw('COUNT(DISTINCT o.id) as total_orders'),
                DB::raw('SUM(oi.quantity) as total_items'),
                DB::raw('SUM(oi.quantity * oi.unit_price) as gross_revenue'),
                DB::raw('AVG(oi.quantity * oi.unit_price) as avg_order_value'),
            ])
            ->groupBy(DB::raw('DATE(o.created_at)'))
            ->orderBy('date')
            ->get()
            ->toArray();
    }

    // Hybrid: Eloquent per struttura, raw per subquery
    public function getTopCustomersByRevenue(int $limit = 10): Collection
    {
        // Subquery per calcolo totale ordini
        $revenueSubquery = DB::table('orders')
            ->select('customer_id', DB::raw('SUM(total) as total_spent'))
            ->where('status', 'completed')
            ->groupBy('customer_id');

        // Eloquent con subquery iniettata
        return Order::query()
            ->select(['customers.*', 'revenue.total_spent'])
            ->join('customers', 'orders.customer_id', '=', 'customers.id')
            ->joinSub($revenueSubquery, 'revenue', function ($join) {
                $join->on('customers.id', '=', 'revenue.customer_id');
            })
            ->distinct()
            ->orderByDesc('revenue.total_spent')
            ->limit($limit)
            ->get();
    }

    // Query con CTE (Common Table Expression) per analisi ricorsive
    public function getCategorySalesHierarchy(): array
    {
        // MySQL 8+ / PostgreSQL: CTE per gerarchia categorie
        $sql = "
            WITH RECURSIVE category_tree AS (
                -- Caso base: categorie root
                SELECT id, name, parent_id, 0 as depth
                FROM categories
                WHERE parent_id IS NULL
                
                UNION ALL
                
                -- Ricorsione: sottocategorie
                SELECT c.id, c.name, c.parent_id, ct.depth + 1
                FROM categories c
                INNER JOIN category_tree ct ON c.parent_id = ct.id
            )
            SELECT 
                ct.id,
                ct.name,
                ct.depth,
                COALESCE(SUM(oi.quantity * oi.unit_price), 0) as revenue
            FROM category_tree ct
            LEFT JOIN products p ON p.category_id = ct.id
            LEFT JOIN order_items oi ON oi.product_id = p.id
            LEFT JOIN orders o ON o.id = oi.order_id AND o.status = 'completed'
            GROUP BY ct.id, ct.name, ct.depth
            ORDER BY ct.depth, revenue DESC
        ";

        return DB::select($sql);
    }
}

 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\Services;

use App\Repositories\OrderRepository;
use Illuminate\Support\Facades\Cache;

class ReportService
{
    public function __construct(
        private OrderRepository $orderRepository
    ) {}

    public function getDashboardStats(string $startDate, string $endDate): array
    {
        // Cache report pesanti
        $cacheKey = "dashboard_stats_{$startDate}_{$endDate}";
        
        return Cache::remember($cacheKey, now()->addHours(1), function () use ($startDate, $endDate) {
            return [
                'sales' => $this->orderRepository->getSalesReport($startDate, $endDate),
                'top_customers' => $this->orderRepository->getTopCustomersByRevenue(5),
                'category_breakdown' => $this->orderRepository->getCategorySalesHierarchy(),
            ];
        });
    }
}

 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
// config/database.php - Configurazione ottimizzata per produzione

return [
    'default' => env('DB_CONNECTION', 'mysql'),

    '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', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            
            // Opzioni critiche per produzione
            'options' => extension_loaded('pdo_mysql') ? [
                PDO::ATTR_PERSISTENT => true,           // Connessioni persistenti
                PDO::ATTR_EMULATE_PREPARES => false,    // Prepared statements reali
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                PDO::ATTR_STRINGIFY_FETCHES => false,   // Mantieni i tipi nativi
            ] : [],
        ],

        // Replica per letture pesanti
        '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, // Evita read-after-write inconsistencies
            'driver' => 'mysql',
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
        ],
    ],
];

 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

<?php

namespace App\Providers;

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

class QueryMonitoringServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        // Attiva solo in produzione con flag specifico
        if (!config('app.query_logging_enabled')) {
            return;
        }

        DB::listen(function (QueryExecuted $query) {
            $this->logSlowQuery($query);
            $this->detectNPlusOne($query);
        });
    }

    private function logSlowQuery(QueryExecuted $query): void
    {
        $threshold = config('app.slow_query_threshold', 1000); // ms
        
        if ($query->time > $threshold) {
            Log::channel('slow_queries')->warning('Query lenta rilevata', [
                'sql' => $query->sql,
                'bindings' => $query->bindings,
                'time_ms' => $query->time,
                'connection' => $query->connectionName,
                'trace' => collect(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10))
                    ->filter(fn($frame) => isset($frame['file']) && 
                        !str_contains($frame['file'], 'vendor'))
                    ->values()
                    ->toArray(),
            ]);
        }
    }

    private array $queryPatterns = [];

    private function detectNPlusOne(QueryExecuted $query): void
    {
        // Pattern semplificato della query (senza valori specifici)
        $pattern = preg_replace('/\d+/', '?', $query->sql);
        $requestId = request()->header('X-Request-ID', uniqid());
        
        $key = $requestId . ':' . $pattern;
        
        if (!isset($this->queryPatterns[$key])) {
            $this->queryPatterns[$key] = 0;
        }
        
        $this->queryPatterns[$key]++;
        
        // Se la stessa query viene eseguita più di 5 volte, è probabilmente N+1
        if ($this->queryPatterns[$key] === 6) {
            Log::channel('n_plus_one')->error('Possibile N+1 rilevato', [
                'pattern' => $pattern,
                'count' => $this->queryPatterns[$key],
                'url' => request()->fullUrl(),
            ]);
        }
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# config/cache.yaml (se usi Symfony-style config)
# oppure nel tuo .env per Laravel standard

# Redis configurazione ottimale per query caching
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379
REDIS_DB=0
REDIS_CACHE_DB=1

# Cache TTL defaults
CACHE_TTL_SHORT=300
CACHE_TTL_MEDIUM=3600
CACHE_TTL_LONG=86400

# Query specific
SLOW_QUERY_THRESHOLD=500
QUERY_LOGGING_ENABLED=true

 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

<?php
// config/cache.php - Configurazione multi-store

return [
    'default' => env('CACHE_DRIVER', 'redis'),

    'stores' => [
        'redis' => [
            'driver' => 'redis',
            'connection' => 'cache',
            'lock_connection' => 'default',
        ],

        // Store dedicato per query results
        'query_cache' => [
            'driver' => 'redis',
            'connection' => 'query_cache',
            'prefix' => 'qc',
        ],

        // Store per aggregazioni costose
        'aggregations' => [
            'driver' => 'redis',
            'connection' => 'cache',
            'prefix' => 'agg',
        ],
    ],

    'prefix' => env('CACHE_PREFIX', 'laravel'),
];

  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
103
104
105
106
107
108
109
110

<?php

namespace App\Support;

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

class QueryDebugger
{
    /**
     * Analizza una query e restituisce informazioni diagnostiche
     */
    public static function analyze(Builder $query): array
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();
        
        // Costruisci la query completa per debug
        $fullSql = self::interpolateQuery($sql, $bindings);
        
        // Esegui EXPLAIN
        $explain = DB::select('EXPLAIN ANALYZE ' . $fullSql);
        
        // Analizza i risultati
        $issues = self::detectIssues($explain);
        
        return [
            'sql' => $fullSql,
            'explain' => $explain,
            'issues' => $issues,
            'suggestions' => self::getSuggestions($issues),
        ];
    }

    private static function interpolateQuery(string $sql, array $bindings): string
    {
        foreach ($bindings as $binding) {
            $value = is_numeric($binding) ? $binding : "'" . addslashes($binding) . "'";
            $sql = preg_replace('/\?/', $value, $sql, 1);
        }
        return $sql;
    }

    private static function detectIssues(array $explain): array
    {
        $issues = [];
        
        foreach ($explain as $row) {
            $row = (array) $row;
            
            // Controlla per full table scan
            if (isset($row['type']) && $row['type'] === 'ALL') {
                $issues[] = [
                    'type' => 'full_scan',
                    'table' => $row['table'] ?? 'unknown',
                    'severity' => 'high',
                ];
            }
            
            // Controlla per filesort
            if (isset($row['Extra']) && str_contains($row['Extra'], 'filesort')) {
                $issues[] = [
                    'type' => 'filesort',
                    'table' => $row['table'] ?? 'unknown',
                    'severity' => 'medium',
                ];
            }
            
            // Controlla per temporary table
            if (isset($row['Extra']) && str_contains($row['Extra'], 'temporary')) {
                $issues[] = [
                    'type' => 'temporary_table',
                    'table' => $row['table'] ?? 'unknown',
                    'severity' => 'medium',
                ];
            }
        }
        
        return $issues;
    }

    private static function getSuggestions(array $issues): array
    {
        $suggestions = [];
        
        foreach ($issues as $issue) {
            switch ($issue['type']) {
                case 'full_scan':
                    $suggestions[] = sprintf(
                        "Aggiungi un indice sulla tabella '%s' per le colonne usate in WHERE/JOIN",
                        $issue['table']
                    );
                    break;
                    
                case 'filesort':
                    $suggestions[] = sprintf(
                        "Considera un indice che includa le colonne ORDER BY per la tabella '%s'",
                        $issue['table']
                    );
                    break;
                    
                case 'temporary_table':
                    $suggestions[] = "Riduci la complessità del GROUP BY o aumenta tmp_table_size";
                    break;
            }
        }
        
        return $suggestions;
    }
}

 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

// ❌ ERRORE 1: Mutare la query originale
$baseQuery = User::where('active', true);
$admins = $baseQuery->where('role', 'admin')->get();
$users = $baseQuery->where('role', 'user')->get(); // SBAGLIATO: include anche role = 'admin'

// ✅ SOLUZIONE: Clona sempre la query base
$baseQuery = User::where('active', true);
$admins = (clone $baseQuery)->where('role', 'admin')->get();
$users = (clone $baseQuery)->where('role', 'user')->get();


// ❌ ERRORE 2: whereHas senza constraint specifici
$posts = Post::whereHas('comments')->get(); // Lento su tabelle grandi

// ✅ SOLUZIONE: Usa exists con subquery esplicita o join
$posts = Post::whereExists(function ($query) {
    $query->select(DB::raw(1))
        ->from('comments')
        ->whereColumn('comments.post_id', 'posts.id')
        ->limit(1); // Importante: ferma alla prima match
})->get();


// ❌ ERRORE 3: Eager loading con troppi dati
$users = User::with('orders')->get(); // Carica TUTTI gli ordini di TUTTI gli utenti

// ✅ SOLUZIONE: Limita l'eager loading
$users = User::with(['orders' => function ($query) {
    $query->select('id', 'user_id', 'total', 'created_at')
        ->where('created_at', '>=', now()->subYear())
        ->latest()
        ->limit(10);
}])->get();


// ❌ ERRORE 4: Salvare in loop senza transazione
foreach ($items as $item) {
    Product::create($item); // N query separate, nessun rollback se fallisce
}

// ✅ SOLUZIONE: Batch insert o transazione
DB::transaction(function () use ($items) {
    // Per insert puri: usa insert bulk
    Product::insert(
        collect($items)->map(fn($item) => [
            ...$item,
            'created_at' => now(),
            'updated_at' => now(),
        ])->toArray()
    );
});

// Oppure se servono model events:
DB::transaction(function () use ($items) {
    foreach (array_chunk($items, 500) as $chunk) {
        foreach ($chunk as $item) {
            Product::create($item);
        }
    }
});

 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\Support;

use Closure;
use Illuminate\Support\Facades\DB;

class QueryBenchmark
{
    /**
     * Esegue una query multipla volte e restituisce statistiche
     */
    public static function run(Closure $queryBuilder, int $iterations = 100): array
    {
        $times = [];
        $memoryUsage = [];
        
        // Warmup: esegui una volta per popolare cache
        $queryBuilder();
        
        // Reset query log
        DB::flushQueryLog();
        DB::enableQueryLog();
        
        for ($i = 0; $i < $iterations; $i++) {
            $startMemory = memory_get_usage(true);
            $startTime = microtime(true);
            
            $queryBuilder();
            
            $times[] = (microtime(true) - $startTime) * 1000; // ms
            $memoryUsage[] = memory_get_usage(true) - $startMemory;
        }
        
        $queryLog = DB::getQueryLog();
        DB::disableQueryLog();
        
        return [
            'iterations' => $iterations,
            'time' => [
                'min' => min($times),
                'max' => max($times),
                'avg' => array_sum($times) / count($times),
                'p95' => self::percentile($times, 95),
                'p99' => self::percentile($times, 99),
            ],
            'memory' => [
                'avg_bytes' => array_sum($memoryUsage) / count($memoryUsage),
                'peak_bytes' => max($memoryUsage),
            ],
            'queries_per_iteration' => count($queryLog) / $iterations,
        ];
    }

    private static function percentile(array $values, int $percentile): float
    {
        sort($values);
        $index = ceil(($percentile / 100) * count($values)) - 1;
        return $values[$index];
    }
}

// Esempio d'uso
$resultsEager = QueryBenchmark::run(function () {
    return Order::with(['customer', 'items.product'])
        ->where('created_at', '>=', now()->subMonth())
        ->limit(100)
        ->get();
});

$resultsLazy = QueryBenchmark::run(function () {
    return Order::where('created_at', '>=', now()->subMonth())
        ->limit(100)
        ->get()
        ->load(['customer', 'items.product']);
});

 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

<?php

namespace App\Jobs;

use App\Events\ReportReady;
use App\Repositories\OrderRepository;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Support\Facades\Cache;

class GenerateSalesReport implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable;

    public int $timeout = 300; // 5 minuti max
    public int $tries = 2;

    public function __construct(
        private string $reportId,
        private string $startDate,
        private string $endDate,
        private int $userId
    ) {
        // Usa la connessione read-only per non impattare le scritture
        $this->onConnection('database_read');
        $this->onQueue('reports');
    }

    public function handle(OrderRepository $repository): void
    {
        // Esegui query pesanti sulla replica
        $report = [
            'generated_at' => now()->toIso8601String(),
            'period' => [
                'start' => $this->startDate,
                'end' => $this->endDate,
            ],
            'sales' => $repository->getSalesReport(
                $this->startDate,
                $this->endDate
            ),
            'top_products' => $repository->getTopProductsByRevenue(20),
            'trends' => $repository->getSalesTrends(
                $this->startDate,
                $this->endDate,
                'daily'
            ),
        ];

        // Salva in cache con TTL lungo
        Cache::store('reports')->put(
            "report:{$this->reportId}",
            $report,
            now()->addHours(24)
        );

        // Notifica il client via broadcast
        event(new ReportReady($this->reportId, $this->userId));
    }

    public function failed(\Throwable $exception): void
    {
        Cache::store('reports')->put(
            "report:{$this->reportId}",
            ['error' => 'Generazione fallita', 'message' => $exception->getMessage()],
            now()->addMinutes(30)
        );
    }
}

 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

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

return new class extends Migration
{
    public function up(): void
    {
        // Indici per il Pattern 1 (Query Scope dinamici)
        Schema::table('products', function (Blueprint $table) {
            // Indice composto per ricerche comuni
            $table->index(['status', 'category_id', 'price'], 'idx_products_filter');
            
            // Indice per full-text search (se supportato)
            $table->fullText(['name', 'description'], 'idx_products_search');
        });

        // Indici per il Pattern 4 (Aggregazioni)
        Schema::table('orders', function (Blueprint $table) {
            // Indice per report temporali
            $table->index(['created_at', 'status'], 'idx_orders_temporal');
            
            // Indice per aggregazioni per customer
            $table->index(['customer_id', 'created_at'], 'idx_orders_customer_time');
        });

        Schema::table('order_items', function (Blueprint $table) {
            // Indice per join con aggregazioni
            $table->index(['order_id', 'product_id'], 'idx_order_items_composite');
            
            // Indice covering per calcoli totali
            $table->index(
                ['order_id', 'quantity', 'unit_price'],
                'idx_order_items_totals'
            );
        });

        // Indici per il Pattern 6 (Gerarchie)
        Schema::table('categories', function (Blueprint $table) {
            // Indice per navigazione parent -> children
            $table->index(['parent_id', 'position'], 'idx_categories_tree');
            
            // Indice per path materialized (se usato)
            $table->index('path', 'idx_categories_path');
        });
    }
};

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

// ❌ ERRORE: Il with() viene ignorato se la relazione è già caricata
$users = User::all();

// Questa chiamata NON fa nulla - users già in memoria senza relazione
$users->load('posts'); // Funziona
$users->with('posts'); // NON FUNZIONA - with() è per query builder

// ✅ CORRETTO: Usa load() per eager loading su collection esistente
$users = User::all();
$users->load(['posts' => function ($query) {
    $query->where('published', true);
}]);

// Oppure carica subito
$users = User::with('posts')->get();

 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

// ❌ ERRORE: Scope che sovrascrive il select principale
class Post extends Model
{
    public function scopeWithStats($query)
    {
        // Questo SOVRASCRIVE qualsiasi select precedente!
        return $query->select([
            'posts.*',
            DB::raw('COUNT(comments.id) as comments_count')
        ])->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
          ->groupBy('posts.id');
    }
}

// Il problema si manifesta così:
Post::select('id', 'title')->withStats()->get();
// Risultato: ignora select('id', 'title'), usa quello dello scope

// ✅ CORRETTO: Usa addSelect() invece di select()
public function scopeWithStats($query)
{
    return $query->addSelect([
        DB::raw('(SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id) as comments_count')
    ]);
}

// Ancora meglio: usa withCount() nativo
Post::select('id', 'title')->withCount('comments')->get();

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

// ❌ ERRORE: Subquery restituisce NULL per record senza match
$users = User::addSelect([
    'latest_order_total' => Order::select('total')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1)
])->get();

// Per utenti senza ordini: latest_order_total = NULL
// Se fai calcoli, esplode tutto

// ✅ CORRETTO: Gestisci i NULL esplicitamente
$users = User::addSelect([
    'latest_order_total' => Order::select(DB::raw('COALESCE(total, 0)'))
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1)
])->get();

// Oppure con default nel model
protected $attributes = [
    'latest_order_total' => 0,
];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

// Tool essenziale: dd() della query SQL
$query = User::with('posts')
    ->whereHas('orders', fn($q) => $q->where('total', '>', 100))
    ->where('active', true);

// Mostra la query SQL con binding sostituiti
dd($query->toSql(), $query->getBindings());

// Versione più leggibile con vsprintf
$sql = $query->toSql();
$bindings = $query->getBindings();
dd(vsprintf(str_replace('?', "'%s'", $sql), $bindings));

// Per vedere TUTTE le query eseguite in una request
DB::enableQueryLog();

// ... il tuo codice ...

dd(DB::getQueryLog()); // Array di tutte le query con tempo di esecuzione

 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

# piano-adozione-pattern.yml
settimana_1:
  focus: "Quick Wins"
  azioni:
    - Installa Laravel Debugbar
    - Identifica top 5 endpoint più lenti
    - Applica subquery per eliminare N+1
    
settimana_2:
  focus: "Refactoring Query Complesse"  
  azioni:
    - Estrai Query Object per report
    - Implementa caching su query statiche
    - Aggiungi indici mancanti
    
settimana_3:
  focus: "Ottimizzazione Batch"
  azioni:
    - Converti job pesanti a cursor()
    - Implementa chunking per export
    - Profila memoria con memory_get_peak_usage()

settimana_4:
  focus: "Pattern Avanzati"
  azioni:
    - Pipeline per ricerche complesse
    - CTE per dati gerarchici (se applicabile)
    - Dynamic relationships per multi-tenancy

 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

// config/logging.php - Aggiungi channel per slow queries
'channels' => [
    'slow_queries' => [
        'driver' => 'daily',
        'path' => storage_path('logs/slow-queries.log'),
        'level' => 'debug',
        'days' => 14,
    ],
],

// AppServiceProvider.php
public function boot()
{
    // Log query oltre 100ms
    DB::listen(function ($query) {
        if ($query->time > 100) {
            Log::channel('slow_queries')->warning('Slow Query', [
                'sql' => $query->sql,
                'time' => $query->time,
                'bindings' => $query->bindings,
                'url' => request()->fullUrl(),
            ]);
        }
    });
}