PHP code example of karimalihussein / laravel-query-sentinel

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

karimalihussein / laravel-query-sentinel example snippets

use QuerySentinel\Facades\QuerySentinel;

// Analyze a raw SQL query
$report = QuerySentinel::analyzeSql('SELECT * FROM users WHERE email = ?');

echo $report->grade;          // 'A'
echo $report->compositeScore; // 92.5
echo $report->passed;         // true

// Analyze an Eloquent builder (without executing it)
$builder = User::where('status', 'active')->select('id', 'name');
$report = QuerySentinel::analyzeBuilder($builder);

// Profile all queries in a closure (transaction-wrapped, rolled back)
$profile = QuerySentinel::profile(function () {
    $users = User::with('posts', 'comments')->paginate(15);
});

echo $profile->totalQueries;      // 3
echo $profile->nPlusOneDetected;  // false
echo $profile->worstGrade();      // 'B'

// Full deep diagnostics (22-step pipeline)
$diagnostic = QuerySentinel::diagnose('SELECT * FROM orders WHERE status = "pending"');

echo $diagnostic->effectiveGrade();       // Confidence-adjusted grade
echo $diagnostic->memoryPressure;         // Memory footprint analysis
echo $diagnostic->concurrencyRisk;        // Lock contention risk
echo count($diagnostic->findings);        // Severity-sorted findings

return [

    // Database driver: 'mysql', 'pgsql', or 'sqlite'
    'driver' => env('QUERY_SENTINEL_DRIVER', 'mysql'),

    // Database connection name (null = default)
    'connection' => env('QUERY_SENTINEL_CONNECTION'),

    // Scoring weights (must sum to 1.0)
    'scoring' => [
        'weights' => [
            'execution_time'  => 0.30,
            'scan_efficiency' => 0.25,
            'index_quality'   => 0.20,
            'join_efficiency' => 0.15,
            'scalability'     => 0.10,
        ],
        'grade_thresholds' => [
            'A' => 90, 'B' => 75, 'C' => 50, 'D' => 25, 'F' => 0,
        ],
    ],

    // Rules to enable
    'rules' => [
        'enabled' => [
            \QuerySentinel\Rules\FullTableScanRule::class,
            \QuerySentinel\Rules\TempTableRule::class,
            \QuerySentinel\Rules\WeedoutRule::class,
            \QuerySentinel\Rules\DeepNestedLoopRule::class,
            \QuerySentinel\Rules\IndexMergeRule::class,
            \QuerySentinel\Rules\StaleStatsRule::class,
            \QuerySentinel\Rules\LimitIneffectiveRule::class,
            \QuerySentinel\Rules\QuadraticComplexityRule::class,
            \QuerySentinel\Rules\NoIndexRule::class,
        ],
    ],

    // Performance thresholds
    'thresholds' => [
        'max_execution_time_ms' => 1000,
        'max_rows_examined'     => 100_000,
        'max_loops'             => 10_000,
        'max_cost'              => 1_000_000,
        'max_nested_loop_depth' => 4,
    ],

    // Scalability projection targets
    'projection' => [
        'targets' => [1_000_000, 10_000_000],
    ],

    // Attribute-based automatic profiling (#[QueryDiagnose])
    'diagnostics' => [
        'enabled'              => env('QUERY_SENTINEL_DIAGNOSTICS_ENABLED', true),
        'global_sample_rate'   => (float) env('QUERY_SENTINEL_SAMPLE_RATE', 1.0),
        'default_threshold_ms' => (int) env('QUERY_SENTINEL_THRESHOLD_MS', 0),
        'classes' => [
            // Service classes to auto-profile:
            // \App\Services\LeadQueryService::class,
        ],
    ],

    // Interactive query scanning (#[DiagnoseQuery])
    'scan' => [
        'paths' => ['app', 'Modules'],
    ],

    // Deep analysis feature configs
    'cardinality_drift' => [
        'warning_threshold'  => 0.5,
        'critical_threshold' => 0.9,
    ],

    'anti_patterns' => [
        'or_chain_threshold'          => 3,
        'missing_limit_row_threshold' => 10000,
    ],

    'index_synthesis' => [
        'max_recommendations'   => 3,
        'max_columns_per_index' => 5,
    ],

    'memory_pressure' => [
        'high_threshold_bytes'     => 268435456,   // 256MB
        'moderate_threshold_bytes' => 67108864,     // 64MB
        'concurrent_sessions'      => 10,
    ],

    'hypothetical_index' => [
        'enabled'              => false,
        'max_simulations'      => 3,
        'timeout_seconds'      => 5,
        'allowed_environments' => ['local', 'testing'],
    ],

    'workload' => [
        'enabled'                 => true,
        'frequency_threshold'     => 10,
        'export_row_threshold'    => 100_000,
        'network_bytes_threshold' => 52428800,  // 50MB
    ],

    'regression' => [
        'enabled'                  => true,
        'storage_path'             => null,  // defaults to storage_path('query-sentinel/baselines')
        'max_history'              => 10,
        'score_warning_threshold'  => 10,
        'score_critical_threshold' => 25,
        'time_warning_threshold'   => 50,
        'time_critical_threshold'  => 200,
        'noise_floor_ms'           => 3,
        'minimum_measurable_ms'    => 5,
    ],
];

use QuerySentinel\Facades\QuerySentinel;

$report = QuerySentinel::analyzeSql(
    "SELECT u.*, COUNT(p.id) as post_count
     FROM users u
     LEFT JOIN posts p ON p.user_id = u.id
     WHERE u.status = 'active'
     GROUP BY u.id
     ORDER BY post_count DESC
     LIMIT 20"
);

echo $report->grade;                        // 'B'
echo $report->compositeScore;               // 78.4
echo $report->result->metrics['rows_examined'];  // 15000
echo $report->result->metrics['has_filesort'];   // true

foreach ($report->recommendations as $rec) {
    echo "- {$rec}\n";
}

$builder = User::query()
    ->where('status', 'active')
    ->where('created_at', '>=', now()->subDays(30))
    ->select('id', 'name', 'email');

$report = QuerySentinel::analyzeBuilder($builder);
echo $report->grade;  // 'A'

$profile = QuerySentinel::profile(function () {
    $users = User::with(['posts', 'comments'])->where('active', true)->get();
    foreach ($users as $user) {
        $user->updateQuietly(['last_seen' => now()]);
    }
});

echo $profile->totalQueries;       // 12
echo $profile->analyzedQueries;    // 3 (SELECTs only)
echo $profile->nPlusOneDetected;   // false
echo $profile->worstGrade();       // 'C'
echo $profile->slowestQuery->result->executionTimeMs;  // 18.5

$profile = QuerySentinel::profileClass(
    \App\Services\LeadQueryService::class,
    'getFilteredLeads',
    [$filterDTO, $page = 1],
);

echo $profile->totalQueries;
echo $profile->worstGrade();

$diagnostic = QuerySentinel::diagnose(
    "SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50"
);

// Confidence-adjusted results
echo $diagnostic->effectiveGrade();          // 'B' (may differ from base grade)
echo $diagnostic->effectiveCompositeScore(); // 82.3

// Deep analysis sections (all nullable — available when analyzers are enabled)
$diagnostic->environment;          // Server config (buffer pool, InnoDB settings)
$diagnostic->executionProfile;     // Nested loops, B-tree depths, complexity
$diagnostic->cardinalityDrift;     // Estimation accuracy per table
$diagnostic->antiPatterns;         // 10 SQL anti-pattern detections
$diagnostic->indexSynthesis;       // ERS-ordered index recommendations with DDL
$diagnostic->confidence;           // 8-factor confidence score (0-1.0)
$diagnostic->concurrencyRisk;      // Lock scope, deadlock risk, contention
$diagnostic->memoryPressure;       // Sort/join/temp buffers, network transfer
$diagnostic->regression;           // Score/time/rows changes vs baseline
$diagnostic->hypotheticalIndexes;  // Simulated index impact (local/testing)
$diagnostic->workload;             // Export/burst/transfer patterns

// Severity-sorted findings with root-cause awareness
foreach ($diagnostic->findings as $finding) {
    echo "[{$finding->severity->value}] {$finding->title}\n";
    echo "  {$finding->description}\n";
    if ($finding->recommendation) {
        echo "  -> {$finding->recommendation}\n";
    }
}

use QuerySentinel\Attributes\DiagnoseQuery;
use Illuminate\Database\Eloquent\Builder;

class OrderService
{
    #[DiagnoseQuery(label: 'Pending orders', description: 'Orders awaiting fulfillment')]
    public function pendingOrdersQuery(): Builder
    {
        return Order::query()
            ->where('status', 'pending')
            ->where('created_at', '>=', now()->subDays(30))
            ->with('customer')
            ->orderByDesc('created_at');
    }

    #[DiagnoseQuery(label: 'Revenue report')]
    public function revenueReportQuery(): Builder
    {
        return Order::query()
            ->selectRaw('DATE(created_at) as date, SUM(total) as revenue')
            ->where('status', 'completed')
            ->groupByRaw('DATE(created_at)')
            ->orderByDesc('date');
    }
}

class ClientService
{
    // Production method — takes dto): LengthAwarePaginator
    {
        return $this->buildFilteredQuery($dto)->paginate($dto->perPage);
    }

    // Diagnosis method — no ere('active', true)
            ->where('created_at', '>=', now()->subMonth())
            ->whereNotNull('email')
            ->orderByDesc('created_at');
    }
}

// config/query-diagnostics.php
'scan' => [
    'paths' => ['app', 'Modules'],  // Relative to base_path()
],

// app/Http/Kernel.php (Laravel 10)
protected $routeMiddleware = [
    'query.diagnose' => \QuerySentinel\Interception\QueryDiagnoseMiddleware::class,
];

Route::middleware(['auth:sanctum', 'query.diagnose'])->group(function () {
    Route::get('/leads', [LeadsController::class, 'index']);
});

use QuerySentinel\Attributes\QueryDiagnose;

class LeadsController extends Controller
{
    #[QueryDiagnose]
    public function index(LeadFilterDTO $dto)
    {
        return LeadResource::collection(
            $this->service->getFilteredLeads($dto)
        );
    }

    #[QueryDiagnose(thresholdMs: 100, sampleRate: 0.25)]
    public function search(Request $request)
    {
        // Profiled 25% of the time, logged only if queries take > 100ms
        return $this->service->search($request->input('q'));
    }
}

'diagnostics' => [
    'classes' => [
        \App\Services\LeadQueryService::class,
        \App\Services\ReportService::class,
    ],
],

use QuerySentinel\Attributes\QueryDiagnose;

class LeadQueryService
{
    #[QueryDiagnose(thresholdMs: 50)]
    public function getFilteredLeads(LeadFilterDTO $dto): LengthAwarePaginator
    {
        return Client::query()
            ->with(['submissions', 'branch'])
            ->filter($dto)
            ->paginate($dto->perPage);
    }
}

#[QueryDiagnose(sampleRate: 0.05)]  // Profile 5% of invocations

#[QueryDiagnose(thresholdMs: 200)]  // Only log if cumulative time >= 200ms

#[QueryDiagnose(failOnCritical: true)]
public function criticalEndpoint() { ... }

try {
    $service->criticalEndpoint();
} catch (PerformanceViolationException $e) {
    $e->report;  // ProfileReport
    $e->class;   // 'App\Services\LeadQueryService'
    $e->method;  // 'criticalEndpoint'
}

#[QueryDiagnose(logChannel: 'performance')]

$diagnostic->cardinalityDrift;
// [
//     'composite_drift_score' => 0.35,
//     'per_table' => [
//         'orders' => [
//             'estimated_rows' => 1000,
//             'actual_rows' => 5200,
//             'drift_ratio' => 0.81,
//             'direction' => 'under_estimated',
//             'severity' => 'warning',
//         ],
//     ],
//     'tables_needing_analyze' => ['orders'],
// ]

$diagnostic->indexSynthesis;
// [
//     'recommendations' => [
//         [
//             'table' => 'orders',
//             'columns' => ['status', 'created_at', 'total'],
//             'type' => 'covering',
//             'ddl' => 'CREATE INDEX idx_orders_status_created_total ON orders(status, created_at, total)',
//             'estimated_improvement' => 'high',
//             'rationale' => 'Covers WHERE equality + range + SELECT columns',
//         ],
//     ],
// ]

$diagnostic->concurrencyRisk;
// [
//     'lock_scope' => 'none',          // none, row, gap, range, table
//     'deadlock_risk' => 0.0,          // 0-1.0
//     'deadlock_risk_label' => 'low',  // low, moderate, high
//     'contention_score' => 0.0,
//     'isolation_impact' => 'MVCC consistent read — no locking',
//     'recommendations' => [],
// ]

$diagnostic->memoryPressure;
// [
//     'memory_risk' => 'moderate',
//     'total_estimated_bytes' => 67108864,
//     'buffer_pool_pressure' => 0.15,
//     'network_pressure' => 'MODERATE',
//     'components' => [
//         'sort_buffer' => 2097152,
//         'join_buffers' => 524288,
//         'temp_table' => 8388608,
//     ],
//     'concurrency_adjusted' => [
//         'concurrent_sessions' => 10,
//         'concurrent_execution_memory' => 109051904,
//         'concurrent_network_transfer' => 524288000,
//     ],
// ]

$diagnostic->regression;
// [
//     'has_baseline' => true,
//     'baseline_count' => 5,
//     'trend' => 'stable',           // stable, improving, degrading
//     'regressions' => [],           // Score/time/rows degradations
//     'improvements' => [
//         ['metric' => 'execution_time', 'baseline_value' => 12.5, 'current_value' => 8.3, 'change_pct' => -33.6],
//     ],
// ]

// Enable in config
'hypothetical_index' => [
    'enabled' => true,
    'allowed_environments' => ['local', 'testing'],
],

$diagnostic->hypotheticalIndexes;
// [
//     'simulations' => [
//         [
//             'index_ddl' => 'CREATE INDEX idx_orders_status_created ON orders(status, created_at)',
//             'before' => ['access_type' => 'ALL', 'rows' => 50000],
//             'after' => ['access_type' => 'ref', 'rows' => 150],
//             'improvement' => 'significant',
//             'validated' => true,
//         ],
//     ],
//     'best_recommendation' => 'CREATE INDEX idx_orders_status_created ON orders(status, created_at)',
// ]

$diagnostic->stabilityAnalysis;
// [
//     'volatility_score' => 25,        // 0-100
//     'volatility_label' => 'stable',  // stable (<30), moderate (30-59), volatile (60+)
//     'plan_flip_risk' => [
//         'is_risky' => false,
//         'deviations' => [],
//     ],
//     'optimizer_hints' => [],          // USE INDEX, FORCE INDEX, STRAIGHT_JOIN
//     'statistics_drift' => [],
// ]

$report->grade;            // string — 'A', 'B', 'C', 'D', or 'F'
$report->compositeScore;   // float — 0.0 to 100.0
$report->passed;           // bool — true if no critical findings
$report->summary;          // string — human-readable summary
$report->recommendations;  // string[] — actionable suggestions
$report->scalability;      // array — growth projections
$report->mode;             // string — 'sql', 'builder', or 'profiler'
$report->analyzedAt;       // DateTimeImmutable

$report->toArray();
$report->toJson(JSON_PRETTY_PRINT);
$report->findingCounts();  // ['critical' => 0, 'warning' => 1, ...]

$diagnostic->report;              // Report — base analysis
$diagnostic->findings;            // Finding[] — severity-sorted
$diagnostic->environment;         // ?EnvironmentContext
$diagnostic->executionProfile;    // ?ExecutionProfile
$diagnostic->indexAnalysis;       // ?array
$diagnostic->joinAnalysis;        // ?array
$diagnostic->stabilityAnalysis;   // ?array
$diagnostic->safetyAnalysis;      // ?array
$diagnostic->cardinalityDrift;    // ?array
$diagnostic->antiPatterns;        // ?array
$diagnostic->indexSynthesis;      // ?array
$diagnostic->confidence;          // ?array
$diagnostic->concurrencyRisk;     // ?array
$diagnostic->memoryPressure;      // ?array
$diagnostic->regression;          // ?array
$diagnostic->hypotheticalIndexes; // ?array
$diagnostic->workload;            // ?array

$diagnostic->effectiveGrade();          // Confidence-capped grade
$diagnostic->effectiveCompositeScore(); // Confidence-capped score
$diagnostic->findingsByCategory('anti_pattern');
$diagnostic->findingCounts();           // By severity
$diagnostic->worstSeverity();

$diagnostic->toArray();
$diagnostic->toJson(JSON_PRETTY_PRINT);

$profile->totalQueries;       // int
$profile->analyzedQueries;    // int — SELECT queries analyzed
$profile->cumulativeTimeMs;   // float
$profile->slowestQuery;       // ?Report
$profile->worstQuery;         // ?Report — lowest score
$profile->duplicateQueries;   // array — normalized SQL => count
$profile->nPlusOneDetected;   // bool
$profile->individualReports;  // Report[]
$profile->skippedQueries;     // string[] — non-SELECT queries

$profile->worstGrade();
$profile->hasCriticalFindings();

use QuerySentinel\Rules\BaseRule;

class SlowQueryRule extends BaseRule
{
    public function evaluate(array $metrics): ?array
    {
        $time = $metrics['execution_time_ms'] ?? 0;

        if ($time > 500) {
            return $this->finding(
                severity: 'critical',
                title: 'Slow query detected',
                description: sprintf('Query took %.0fms.', $time),
                recommendation: 'Add indexes or optimize the query.',
            );
        }

        return null;
    }

    public function key(): string { return 'slow_query'; }
    public function name(): string { return 'Slow Query Detection'; }
}

'rules' => [
    'enabled' => [
        // Built-in rules...
        \App\QueryRules\SlowQueryRule::class,
    ],
],

use QuerySentinel\Contracts\DriverInterface;

$this->app->singleton(DriverInterface::class, MyCustomDriver::class);

use QuerySentinel\Contracts\ScoringEngineInterface;

$this->app->singleton(ScoringEngineInterface::class, MyCustomScoringEngine::class);

bash
php artisan vendor:publish --tag=query-sentinel-config

json
{
  "type": "query_sentinel_profile",
  "class": "App\\Services\\LeadQueryService",
  "method": "getFilteredLeads",
  "total_queries": 5,
  "cumulative_time_ms": 45.23,
  "grade": "B",
  "n_plus_one": false,
  "analyzed_at": "2026-02-27T14:30:00+00:00"
}

bash
# Full deep diagnostic report
php artisan query:diagnose "SELECT * FROM users WHERE email = '[email protected]'"

# JSON output (CI-friendly)
php artisan query:diagnose "SELECT * FROM users WHERE id = 1" --json

# Shallow analysis (skip deep analyzers)
php artisan query:diagnose "SELECT * FROM users" --shallow

# Fail on warnings (CI gate)
php artisan query:diagnose "SELECT * FROM users" --fail-on-warning

# Specific database connection
php artisan query:diagnose "SELECT * FROM users" --connection=reporting