Query Builder Parser Laravel Package

Getting Started

Minimal Setup

1. Install the package via Composer:

   composer require timgws/query-builder-parser
   

2. Basic usage in a controller:

   use timgws\QueryBuilderParser;
   use Illuminate\Support\Facades\DB;
   
   public function filterData(Request $request)
   {
       $allowedFields = ['name', 'email', 'status']; // Whitelist columns
       $qbp = new QueryBuilderParser($allowedFields);
   
       $query = $qbp->parse($request->input('querybuilder'), DB::table('users'));
       return $query->get();
   }
   

3. Frontend integration:
   • Include jQuery QueryBuilder in your view.
   • Pass the generated JSON (querybuilder.getRules()) to your Laravel endpoint.

First Use Case: Admin Dashboard Filtering

• Scenario: Build a user management dashboard with dynamic filtering (e.g., "Active users with orders > $100").
• Steps:
  1. Define allowed fields in QueryBuilderParser (e.g., ['name', 'email', 'status', 'order_count']).
  2. Parse the frontend’s QueryBuilder JSON into a Laravel query.
  3. Return results to a DataTable or custom grid.

Implementation Patterns

Core Workflow

1. Whitelist Fields:

   $qbp = new QueryBuilderParser(['id', 'username', 'created_at']);
   

   • Restricts filtering to specified columns (security/performance).

2. Parse Frontend Rules:

   $query = $qbp->parse($request->querybuilder, DB::table('users'));
   

   • Converts JSON like {"condition":"AND","rules":[{"field":"status","operator":"equal","value":"active"}]} into SQL: WHERE status = 'active'.

3. Extend for Complex Queries:

   • Joins: Use JoinSupportingQueryBuilderParser for nested relationships:

     $joinFields = ['orders' => ['from_table' => 'users', 'to_table' => 'orders', 'from_col' => 'id', 'to_col' => 'user_id']];
     $jsqbp = new JoinSupportingQueryBuilderParser(['name'], $joinFields);
     

   • MongoDB: Replace DB::table() with DB::collection() for NoSQL queries.

Integration Tips

• DataTables: Combine with yajra/laravel-datatables for server-side processing:

  return Datatable::query($query)
      ->showColumns(['id', 'name'])
      ->make();
  

  • Pass QueryBuilder rules via aoData in DataTables’ fnServerParams.

• APIs: For React/Vue apps, return filtered data as JSON:

  return response()->json($query->get());
  

  • Validate frontend rules with Laravel’s Validator before parsing.

• Caching: Cache parsed queries if rules are static (e.g., admin dashboards):

  $cachedQuery = Cache::remember('user_filters', 60, function() use ($qbp) {
      return $qbp->parse($request->querybuilder, DB::table('users'));
  });
  

• Testing: Mock QueryBuilderParser in unit tests:

  $parser = $this->createMock(QueryBuilderParser::class);
  $parser->method('parse')->willReturn(DB::table('users')->where('active', 1));
  

Gotchas and Tips

Pitfalls

1. Field Mismatches:

   • Issue: QueryBuilder JSON references a field not in the whitelist (e.g., {"field":"deleted_at"}).
   • Fix: Validate frontend rules against the whitelist:

     $allowed = ['name', 'email'];
     $rules = json_decode($request->querybuilder, true);
     foreach ($rules['rules'] as $rule) {
         if (!in_array($rule['field'], $allowed)) {
             throw new \InvalidArgumentException("Field {$rule['field']} not allowed.");
         }
     }
     

2. Operator Quirks:

   • begins_with/ends_with: Reversed in v1.1.2. Update frontend rules if using older versions.
   • NOT BETWEEN: Supported in v1.5+, but test edge cases (e.g., NULL values).

3. MongoDB Escaping:

   • Issue: Regex queries may break if $ or \ aren’t escaped.
   • Fix: Use addcslashes() for user input:

     $value = addcslashes($request->input('value'), '$\\');
     

4. Join Complexity:

   • Issue: JoinSupportingQueryBuilderParser may generate inefficient SQL for deep joins.
   • Fix: Limit joins to essential relationships or use eager loading:

     $query->with(['orders' => function($q) { /* ... */ }]);
     

5. Laravel Version Gaps:

   • Issue: v1.5.4+ drops Laravel 5 support. Use v1.5.3 for legacy apps.
   • Fix: Pin the version in composer.json:

     "timgws/query-builder-parser": "1.5.3"
     

Debugging Tips

• Log Raw Rules:

  \Log::debug('QueryBuilder Rules:', ['rules' => $request->querybuilder]);
  

  • Compare with expected JSON structure from the demo.

• SQL Dump:

  $query->toSql(); // Check generated SQL
  $query->getBindings(); // Verify parameters
  

• MongoDB Debugging: Enable MongoDB logging in config/mongodb.php:

  'logger' => [
      'enabled' => true,
      'level' => 'debug',
  ],
  

Extension Points

1. Custom Operators: Extend QueryBuilderParser to support domain-specific operators (e.g., is_archived):

   class CustomQueryBuilderParser extends QueryBuilderParser
   {
       protected function addCustomOperator($operator, $field, $value, $query)
       {
           if ($operator === 'is_archived') {
               return $query->where('archived_at', '>', now()->subDays(30));
           }
       }
   }
   

2. Dynamic Whitelisting: Fetch allowed fields from a database table:

   $allowedFields = DB::table('filterable_columns')
       ->where('table_name', 'users')
       ->pluck('column_name')
       ->toArray();
   

3. Query Modifiers: Hook into the parsing process to add clauses:

   $qbp->parse($rules, $query)->where('deleted_at', null);
   

4. Performance:

   • Indexing: Ensure filtered columns are indexed in the database.
   • Pagination: Add ->paginate(10) to queries for large datasets.

5. Security:

   • Sanitize Input: Use Laravel’s Validator or Str::of($value)->slug() for user-provided values.
   • Rate Limiting: Protect endpoints from abuse:

     RateLimiter::hit($request->ip(), 60); // 60 requests/minute