Query Builder Parser Laravel Package

Getting Started

Minimal Setup

1. Install the Package:

   composer require timgws/query-builder-parser
   

   Ensure compatibility with your Laravel version (supports 5.5+ up to 13).

2. Basic Usage:

   use timgws\QueryBuilderParser;
   use Illuminate\Support\Facades\DB;
   
   // Define allowed fields (columns) for filtering
   $allowedFields = ['name', 'email', 'status'];
   
   // Initialize parser
   $parser = new QueryBuilderParser($allowedFields);
   
   // Parse frontend query rules into a Laravel query builder
   $query = $parser->parse($request->input('querybuilder'), DB::table('users'));
   
   // Execute and return results
   return $query->get();
   

3. Frontend Integration:

   • Include jQuery QueryBuilder in your view.
   • Send parsed rules via AJAX to your Laravel endpoint (e.g., rules field in POST data).

First Use Case: Filterable Admin Table

• Goal: Add dynamic filtering to a Laravel admin panel (e.g., users table).
• Steps:
  1. Add QueryBuilderParser to your controller.
  2. Configure allowed fields (e.g., ['name', 'email', 'created_at']).
  3. Parse frontend rules and apply to Eloquent/QueryBuilder.
  4. Return filtered results to Datatables or a custom frontend.

Implementation Patterns

Core Workflows

1. Frontend → Backend Query Translation

• Frontend: jQuery QueryBuilder generates JSON rules (e.g., {"condition":"AND","rules":[{"field":"email","operator":"contains","value":"gmail"}]}).
• Backend: Parse rules into SQL/MongoDB queries:

  $parser = new QueryBuilderParser(['email', 'status']);
  $query = $parser->parse($request->rules, DB::table('users'));
  

2. Integration with Datatables

• Server-Side Processing:

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

• Frontend Sync:

  // Update Datatables with new rules
  function reloadData() {
      const rules = $('#querybuilder').queryBuilder('getRules');
      $.post('/admin/users', { rules }, (data) => {
          oTable.fnDraw();
      });
  }
  

3. Complex Joins

• Use JoinSupportingQueryBuilderParser for nested queries:

  $joinFields = [
      'orders' => [
          'from_table' => 'users',
          'from_col' => 'id',
          'to_table' => 'orders',
          'to_col' => 'user_id',
      ]
  ];
  $parser = new JoinSupportingQueryBuilderParser(['name'], $joinFields);
  $query = $parser->parse($request->rules, DB::table('users'));
  

4. MongoDB Support

• Replace DB::table() with DB::collection():

  $parser = new QueryBuilderParser(['name', 'email']);
  $query = $parser->parse($request->rules, DB::collection('users'));
  

Best Practices

• Field Whitelisting: Always restrict allowed fields to prevent SQL injection:

  $parser = new QueryBuilderParser(['safe_column1', 'safe_column2']);
  

• Operator Mapping: Customize operators (e.g., begins_with, ends_with) by extending the parser.
• Pagination: Combine with Laravel’s pagination:

  $query->paginate(10);
  

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

Gotchas and Tips

Pitfalls

1. Field Mismatches:

   • Issue: Parsing fails if frontend rules reference fields not in $allowedFields.
   • Fix: Validate fields server-side or use strictMode (if available):

     $parser = new QueryBuilderParser(['name', 'email'], ['strict' => true]);
     

2. MongoDB Quirks:

   • Issue: Regex operators ($regex) may not work as expected for case-sensitive searches.
   • Fix: Use $options: 'i' for case-insensitive queries:

     // Customize MongoDB operators in the parser or post-process the query.
     

3. Join Complexity:

   • Issue: JoinSupportingQueryBuilderParser may generate inefficient SQL for deep joins.
   • Fix: Limit joins to essential relationships or optimize with select():

     $query->select('users.*', 'orders.total');
     

4. Date Handling:

   • Issue: Dates in frontend rules may not parse correctly (e.g., YYYY-MM-DD vs. MM/DD/YYYY).
   • Fix: Normalize dates in the parser or use Carbon:

     $parser->setDateFormat('Y-m-d'); // Align with frontend format.
     

5. Operator Conflicts:

   • Issue: Custom operators (e.g., custom_function) may not map to SQL.
   • Fix: Extend the parser or register custom mappings:

     $parser->addOperator('custom_function', function ($field, $value) {
         return DB::raw("SOME_CUSTOM_FUNCTION($field, '$value')");
     });
     

Debugging Tips

• Log Raw Rules: Inspect frontend rules before parsing:

  \Log::info('Frontend rules:', $request->rules);
  

• SQL Dump: Use Laravel’s query logging:

  DB::enableQueryLog();
  $query->get();
  \Log::info(DB::getQueryLog());
  

• MongoDB Debugging: Dump the parsed MongoDB query:

  $query = $parser->parse($rules, DB::collection('users'));
  \Log::info('MongoDB query:', $query->toJson());
  

Extension Points

1. Custom Operators:

   $parser->addOperator('custom_op', function ($field, $value) {
       return DB::raw("UPPER($field) = UPPER('$value')");
   });
   

2. Field Transformations:

   $parser->setFieldTransformer('email', function ($value) {
       return strtolower($value);
   });
   

3. Post-Processing:

   • Modify the query after parsing:

     $query = $parser->parse($rules, DB::table('users'));
     $query->where('active', 1); // Add static conditions.
     

4. MongoDB Customization:

   • Override MongoDB operators:

     $parser->setMongoOperator('contains', '$regex', function ($value) {
         return ['$regex' => ".*$value.*", '$options' => 'i'];
     });
     

Performance

• Avoid N+1 Queries: Use with() for eager loading in joins:

  $query->with(['orders' => function ($q) {
      $q->where('status', 'completed');
  }]);
  

• Indexing: Ensure filtered fields (e.g., email, status) are indexed in the database.
• Batch Processing: For large datasets, use cursor-based pagination in MongoDB:

  $query->cursor()->limit(100);