Laravel Query Adviser Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require socialblue/laravel-query-adviser
   

   Publish the config file:

   php artisan vendor:publish --provider="Socialblue\QueryAdviser\QueryAdviserServiceProvider" --tag="config"
   

2. Enable Logging: Add the middleware to your app/Http/Kernel.php:

   protected $middleware = [
       // ...
       \Socialblue\QueryAdviser\Middleware\LogQueries::class,
   ];
   

3. First Use Case: Visit /query-adviser in your browser. The dashboard will display all logged queries during your session, sorted by execution time (descending). Click any query to:

   • View raw SQL
   • Copy to clipboard
   • Rerun the query (with a "Test" button)

Implementation Patterns

Core Workflows

1. Debugging Slow Endpoints:

   • Reproduce the issue in a browser session.
   • Navigate to /query-adviser to inspect queries.
   • Sort by Execution Time to identify bottlenecks.
   • Click a query to see its:
     • Duration (ms)
     • Bindings (parameters)
     • Model/Route context (if available).

2. Performance Profiling:

   • Use the "Group by" feature to aggregate queries by model/table.
   • Filter by WHERE clauses or JOINs to spot N+1 or over-fetching.
   • Rerun queries in the database client (e.g., MySQL Workbench) to analyze EXPLAIN plans.

3. Integration with Existing Tools:

   • Laravel Debugbar: Disable QueryAdviser’s frontend UI if using Debugbar, but keep the logging:

     'enabled' => env('APP_ENV') !== 'production',
     

   • Sentry/Error Tracking: Log slow queries (>500ms) as errors:

     if ($query->duration > 500) {
         Sentry::captureException(new \Exception("Slow query: {$query->sql}"));
     }
     

4. CI/CD Pipeline:

   • Add a custom Artisan command to flag slow queries in tests:

     php artisan query-adviser:check --threshold=100
     

   • Fail builds if queries exceed thresholds.

Advanced Patterns

1. Conditional Logging: Disable logging for specific routes/models in config/query-adviser.php:

   'ignore' => [
       'routes' => ['admin.*'],
       'models' => ['App\Models\CacheableModel'],
   ],
   

2. Custom Query Analysis: Extend the Query model to add metadata:

   // app/Providers/QueryAdviserServiceProvider.php
   QueryAdviser::extend(function ($query) {
       $query->setAttribute('user_id', auth()->id());
   });
   

3. Export Data: Use the query-adviser:export command to generate CSV/JSON reports:

   php artisan query-adviser:export --days=7 --to=queries.csv
   

Gotchas and Tips

Pitfalls

1. Performance Overhead:

   • Logging queries adds ~5-10% overhead. Disable in production:

     'enabled' => env('APP_ENV') !== 'production',
     

   • Use cache driver for storage to reduce I/O:

     'driver' => 'cache',
     

2. Memory Leaks:

   • Long-running sessions (e.g., CLI requests) may bloat memory. Clear logs manually:

     php artisan query-adviser:clear
     

3. Binding Data Exposure:

   • Sensitive bindings (passwords, tokens) may leak. Sanitize in app/Providers/QueryAdviserServiceProvider.php:

     QueryAdviser::filterBindings(function ($bindings) {
         return array_map(fn($val) => str_starts_with($val, '****'), $bindings);
     });
     

4. Route Caching Conflicts:

   • If using php artisan route:cache, ensure middleware is registered after caching:

     // Kernel.php
     protected $middlewareGroups = [
         'web' => [
             // ...
             \Socialblue\QueryAdviser\Middleware\LogQueries::class,
         ],
     ];
     

Debugging Tips

1. Query Not Showing?

   • Verify middleware is loaded (check Kernel.php).
   • Ensure APP_DEBUG=true in .env (some drivers require debug mode).

2. Slow Queries Missing?

   • Check config/query-adviser.php for ignored routes/models.
   • Use QueryAdviser::forceLog() in a service provider to force-log specific queries:

     QueryAdviser::forceLog();
     User::query()->where(...)->get();
     

3. Rerun Fails:

   • The "Test" button uses the same connection as the app. If it fails:
     • Check database credentials in .env.
     • Test manually in your DB client first.

4. Storage Drivers:

   • Database: Best for persistence but slower.
   • Cache: Fastest but ephemeral (cleared on cache flush).
   • File: Useful for debugging but not scalable.

Extension Points

1. Custom Query Cards: Override the Blade view (resources/views/vendor/query-adviser/card.blade.php) to add:

   • Query complexity metrics (e.g., JOIN count).
   • Suggested optimizations (e.g., "Add index to email column").

2. Webhook Alerts: Trigger alerts for slow queries via a listener:

   // app/Providers/QueryAdviserServiceProvider.php
   QueryAdviser::listen(function ($query) {
       if ($query->duration > 1000) {
           Http::post('https://your-monitoring-service', [
               'query' => $query->sql,
               'duration' => $query->duration,
           ]);
       }
   });
   

3. Query Grouping: Extend the groupBy functionality to categorize by:

   • HTTP Method (GET/POST).
   • User Role (via middleware).

   QueryAdviser::extendGrouping(function ($query) {
       return auth()->check() ? auth()->user()->role : 'guest';
   });