Laravel Query Builder Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require spatie/laravel-query-builder
   

   Publish the config (optional):

   php artisan vendor:publish --provider="Spatie\QueryBuilder\QueryBuilderServiceProvider"
   

2. First Use Case: Filter a basic API endpoint for User models:

   use Spatie\QueryBuilder\QueryBuilder;
   
   Route::get('/users', function () {
       return QueryBuilder::for(User::class)
           ->allowedFilters('name', 'email')
           ->get();
   });
   

   Test with:

   GET /users?filter[name]=John
   

Where to Look First

• Documentation: Start with the "Basic Usage" section.
• QueryBuilder Facade: Core class for building queries.
• AllowedFilter, AllowedSort, AllowedInclude: Classes for defining allowed operations.

Implementation Patterns

Core Workflow

1. Define Allowed Operations:

   QueryBuilder::for(User::class)
       ->allowedFilters('name', 'email')
       ->allowedSorts('name', 'created_at')
       ->allowedIncludes('posts', 'permissions')
       ->allowedFields('id', 'name', 'email');
   

2. Chain with Existing Queries:

   $baseQuery = User::where('active', true);
   QueryBuilder::for($baseQuery)
       ->allowedFilters('name')
       ->get();
   

3. API Route Integration:

   Route::get('/users', function () {
       return QueryBuilder::for(User::class)
           ->allowedFilters('name', 'role')
           ->paginate(15);
   });
   

Common Patterns

• Grouped Filters (OR/AND Logic):

  QueryBuilder::for(User::class)
      ->allowedFilters(
          AllowedFilter::groupOr('search', [
              AllowedFilter::partial('name'),
              AllowedFilter::partial('email'),
          ])
      )
      ->get();
  

  Request: /users?filter[search]=John

• Custom Sorting:

  QueryBuilder::for(User::class)
      ->allowedSorts(
          AllowedSort::custom('name-length', new StringLengthSort(), 'name')
      )
      ->get();
  

• Nested Includes:

  QueryBuilder::for(User::class)
      ->allowedIncludes('posts.comments', 'permissions')
      ->get();
  

• Field Selection:

  QueryBuilder::for(User::class)
      ->allowedFields('id', 'name', 'email')
      ->get();
  

  Request: /users?fields[users]=id,name

Integration Tips

• Laravel Scout: Combine with scout for search-as-you-type:

  QueryBuilder::for(User::class)
      ->allowedFilters('name')
      ->scout()
      ->get();
  

• API Resources: Use QueryBuilder with Illuminate\Http\Resources\Json\JsonResource for consistent API responses.
• Testing: Mock QueryBuilder in unit tests:

  $builder = QueryBuilder::for(User::class);
  $builder->shouldReceive('get')->andReturn(collect([new User()]));
  

Gotchas and Tips

Pitfalls

1. Case Sensitivity in Filters:

   • Partial filters (partial) are case-insensitive by default. Use exact for case-sensitive matches:

     ->allowedFilters(AllowedFilter::exact('name'))
     

2. Default Values Override:

   • Default filters/sorts are applied after request parameters. Ensure logic aligns with expectations:

     // GET /users?filter[name]=John&filter[active]=true
     // Overrides default `active=true` if present.
     

3. Nested Includes and Scopes:

   • Nested includes (e.g., posts.comments) may fail if intermediate relationships lack proper constraints. Test thoroughly.

4. Field Selection Conflicts:

   • Selecting fields (allowedFields) can break eager-loaded relationships. Use with() for related data:

     QueryBuilder::for(User::class)
         ->allowedFields('id', 'name')
         ->with('posts:id,title') // Explicitly load related fields
         ->get();
     

5. Custom Sort Direction:

   • Default direction for custom sorts (AllowedSort::custom) is ASC. Explicitly set if needed:

     AllowedSort::custom('name-length', new StringLengthSort(), 'name')
         ->defaultDirection(SortDirection::Descending)
     

Debugging Tips

• Log Raw SQL:

  QueryBuilder::for(User::class)
      ->allowedFilters('name')
      ->toSql(); // Log the generated SQL
  

• Validate Requests: Use Laravel’s ValidatesRequests to sanitize input before processing:

  public function rules()
  {
      return [
          'filter.name' => 'sometimes|string|max:255',
          'sort' => 'sometimes|string',
      ];
  }
  

• Exception Handling: Catch InvalidFilterQuery, InvalidSortQuery, etc., for graceful API responses:

  try {
      return QueryBuilder::for(User::class)->get();
  } catch (\Spatie\QueryBuilder\Exceptions\InvalidFilterQuery $e) {
      return response()->json(['error' => $e->getMessage()], 400);
  }
  

Extension Points

1. Custom Filter Logic: Extend Spatie\QueryBuilder\Filters\Filter for reusable filters:

   class AgeFilter extends Filter
   {
       protected $name = 'age';
       protected $callback;
   
       public function __construct()
       {
           $this->callback = function ($query, $value) {
               return $query->where('age', '>=', $value);
           };
       }
   }
   

   Usage:

   QueryBuilder::for(User::class)
       ->allowedFilters(new AgeFilter())
       ->get();
   

2. Middleware for Global Config: Apply QueryBuilder settings globally via middleware:

   public function handle($request, Closure $next)
   {
       QueryBuilder::setDefaultOperator('and');
       return $next($request);
   }
   

3. Dynamic Allowed Operations: Fetch allowed filters/sorts from a database table:

   $allowedFilters = FilterConfig::where('model', User::class)->pluck('field');
   QueryBuilder::for(User::class)
       ->allowedFilters($allowedFilters)
       ->get();
   

4. Testing Helpers: Create a trait for consistent test setups:

   trait QueryBuilderTests
   {
       protected function buildQuery($model, array $allowed = [])
       {
           return QueryBuilder::for($model)
               ->allowedFilters($allowed)
               ->allowedSorts('id');
       }
   }
   

Configuration Quirks

• Operator Overrides: Change the default logical operator (and/or) globally in config/query-builder.php:

  'default_operator' => 'or',
  

• Empty Filter Handling: Disable empty filter values with:

  QueryBuilder::for(User::class)
      ->ignoreMissingFilters()
      ->get();
  

• Pagination Defaults: Override default pagination settings:

  QueryBuilder::for(User::class)
      ->paginate(20)
      ->appends(request()->except('page'));