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: Create a basic API endpoint to filter a model:

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

   Test with:

   GET /users?filter[name]=John
   

3. Where to Look First:

   • Documentation (especially the Features section).
   • config/query-builder.php for package-wide defaults.
   • AllowedFilter, AllowedSort, and AllowedInclude classes for custom logic.

Implementation Patterns

Core Workflows

1. API Resource Filtering:

   // Controller
   public function index(Request $request) {
       return QueryBuilder::for(User::class)
           ->allowedFilters([
               'name', // partial match
               AllowedFilter::exact('email'), // exact match
               AllowedFilter::callback('active', fn($query, $value) =>
                   $query->where('is_active', $value)
               ),
           ])
           ->allowedSorts('name', 'created_at')
           ->paginate(15);
   }
   

2. Complex Relationships:

   // Nested includes + counts
   QueryBuilder::for(User::class)
       ->allowedIncludes([
           'posts.comments', // nested
           AllowedInclude::count('postsCount'), // only count
           AllowedInclude::exists('friendsExists'), // only exists
       ])
       ->get();
   

3. Custom Logic:

   // Custom filter for date ranges
   AllowedFilter::callback('created_at', function ($query, $value) {
       if (str_contains($value, '..')) {
           [$start, $end] = explode('..', $value);
           return $query->whereBetween('created_at', [$start, $end]);
       }
       return $query->where('created_at', $value);
   })
   

4. Integration with Existing Queries:

   // Start from a pre-built query (e.g., with scopes)
   $baseQuery = User::withTrashed()->where('role', 'admin');
   QueryBuilder::for($baseQuery)
       ->allowedFilters('name')
       ->allowedIncludes('posts')
       ->get();
   

5. API Versioning:

   // Route-based configuration
   Route::prefix('v1')->group(function () {
       Route::get('/users', function () {
           return QueryBuilder::for(User::class)
               ->allowedFilters('name') // v1-specific filters
               ->get();
       });
   });
   

Common Patterns

• Reusable Builders:

  // app/QueryBuilders/UserQueryBuilder.php
  class UserQueryBuilder {
      public static function build() {
          return QueryBuilder::for(User::class)
              ->allowedFilters('name', 'email')
              ->allowedSorts('name', 'created_at')
              ->allowedFields('id', 'name', 'email');
      }
  }
  

  Usage:

  UserQueryBuilder::build()->get();
  

• Dynamic Allowed Fields:

  // Dynamically allow fields based on user role
  $allowedFields = auth()->user()->can('admin')
      ? ['*'] // all fields
      : ['id', 'name', 'email'];
  QueryBuilder::for(User::class)
      ->allowedFields(...$allowedFields)
      ->get();
  

• Pagination Defaults:

  QueryBuilder::for(User::class)
      ->defaultPaginationOptions(10, 20, 50)
      ->get();
  

• Error Handling:

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

Gotchas and Tips

Pitfalls

1. Case Sensitivity:

   • Filter values (e.g., filter[name]=John) are case-sensitive by default. Use AllowedFilter::caseInsensitive() to override:

     AllowedFilter::caseInsensitive('name')
     

2. Reserved Keywords:

   • Avoid using Laravel/Eloquent reserved keywords (e.g., order, group) as filter/sort/include names. Use aliases:

     AllowedFilter::field('order', 'order_number')
     

3. Nested Relationships:

   • Deeply nested includes (e.g., posts.comments.users) can cause N+1 query issues. Use with() or eager load manually:

     QueryBuilder::for(User::class)
         ->with(['posts.comments.users' => function($query) {
             $query->select('id', 'name'); // limit fields
         }])
         ->allowedIncludes('posts.comments.users')
         ->get();
     

4. Default Values:

   • Default filters/sorts/includes override request parameters. Use sparingly:

     // Overrides any `sort` in the request
     QueryBuilder::for(User::class)
         ->defaultSort('name')
         ->get();
     

5. Mass Assignment:

   • Fields selected via allowedFields won’t be mass assignable by default. Use ->with() or ->select() to ensure data is available:

     QueryBuilder::for(User::class)
         ->allowedFields('id', 'name')
         ->with('email') // ensure email is loaded for mass assignment
         ->get();
     

6. Performance:

   • Avoid * in allowedFields for large datasets. Explicitly list fields:

     ->allowedFields('id', 'name', 'email') // instead of ['*']
     

   • Combine with select() for fine-grained control:

     QueryBuilder::for(User::class)
         ->select(['id', 'name as full_name'])
         ->allowedFields('id', 'full_name')
         ->get();
     

7. Caching:

   • QueryBuilder does not cache results. Cache the final collection or use Laravel’s query caching:

     QueryBuilder::for(User::class)
         ->allowedFilters('name')
         ->remember(60) // cache for 60 minutes
         ->get();
     

8. Validation:

   • No built-in validation for filter/sort/include values. Validate manually:

     $request->validate([
         'filter[name]' => 'sometimes|string|max:255',
     ]);
     

Debugging Tips

1. Log Raw Queries: Enable Laravel’s query logging in config/database.php:

   'log_queries' => true,
   

   Then check storage/logs/laravel.log.

2. Inspect Allowed Rules: Dump allowed filters/sorts/includes before execution:

   $query = QueryBuilder::for(User::class)
       ->allowedFilters('name')
       ->allowedSorts('name');
   dump($query->getAllowedFilters(), $query->getAllowedSorts());
   

3. Test Edge Cases:

   • Empty filter values (filter[name]=).
   • Malformed requests (filter[name]=John&filter[name]=Doe).
   • Invalid sorts/includes (sort=invalid_column).

4. Use toQuery(): Inspect the final query builder before execution:

   $query = QueryBuilder::for(User::class)
       ->allowedFilters('name');
   dump($query->toQuery()->toSql());
   

Extension Points

1. Custom Filter Logic: Extend AllowedFilter for reusable logic:

   class DateRangeFilter extends AllowedFilter {
       public function __construct(string $property) {
           parent::__construct($property, fn($query, $value) => $query->whereBetween($property, explode('..', $value)));
       }
   }
   

   Usage:

   ->allowedFilters(new DateRangeFilter('created_at'))
   

2. Middleware for Global Rules: Apply common rules (e.g., tenant filtering) via middleware:

   // app/Http/Middleware/QueryBuilderMiddleware.php
   public function handle($request, Closure $next) {
       $request->merge([
           'filter' => array_merge($request->filter ?? [], [
               'tenant_id' => auth()->user()->tenant_id,
           ]),
       ]);
       return $next($request);
   }
   

3. **Dynamic