Query Builder Parser Laravel Package

Getting Started

Minimal Setup

1. Install the package via Composer:

   composer require timgws/query-builder-parser
   

2. Basic usage with jQuery QueryBuilder frontend:

   use timgws\QueryBuilderParser;
   use Illuminate\Support\Facades\DB;
   
   $qbp = new QueryBuilderParser(['name', 'email']); // Whitelist allowed fields
   $query = $qbp->parse($request->input('querybuilder'), DB::table('users'));
   $results = $query->get();
   

3. Frontend integration: Include jQuery QueryBuilder and pass rules via AJAX to your Laravel endpoint.

First Use Case

Dynamic filtering for a user management dashboard:

• Configure jQuery QueryBuilder with radio buttons for active status, text inputs for name, and dropdowns for role.
• Parse the rules server-side with QueryBuilderParser to generate SQL.
• Return filtered results to a DataTable for display.

Implementation Patterns

Core Workflow

1. Frontend:
   • Initialize jQuery QueryBuilder with rules for each filter field.
   • Serialize rules to JSON on form submission:

     const rules = $('#querybuilder').queryBuilder('getRules');
     $.post('/admin/users/filter', { querybuilder: JSON.stringify(rules) });
     

2. Backend:
   • Parse rules into a query builder:

     $qbp = new QueryBuilderParser(['id', 'name', 'email']);
     $query = $qbp->parse($request->querybuilder, DB::table('users'));
     

   • Execute and return results (e.g., for DataTables):

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

Integration Tips

• DataTables: Use fnServerParams to pass QueryBuilder rules to Laravel:

  oTable = $('.datatable').DataTable({
      "serverSide": true,
      "ajax": {
          "url": "/admin/users",
          "data": function(d) {
              d.querybuilder = JSON.stringify($('#querybuilder').queryBuilder('getRules'));
          }
      }
  });
  

• MongoDB: Replace DB::table() with DB::collection() and ensure jenssegers/mongodb is installed.
• Validation: Whitelist fields in QueryBuilderParser to prevent SQL injection:

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

Advanced Patterns

1. Joins: Use JoinSupportingQueryBuilderParser for complex relationships:

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

2. Operator Handling: Customize operators (e.g., NOT BETWEEN) by extending the parser or using the built-in support:

   // Automatically supported in v1.5+
   $rules = ['field' => 'price', 'operator' => 'not_between', 'value' => [100, 500]];
   

Gotchas and Tips

Common Pitfalls

1. Field Whitelisting:

   • Issue: Forgetting to whitelist fields in QueryBuilderParser constructor.
   • Fix: Always pass an array of allowed columns to prevent SQL injection:

     $qbp = new QueryBuilderParser(['id', 'name']); // Only these fields are queryable
     

   • Debug: Check for SQLSTATE[42S22]: Column not found errors if unwhitelisted fields are used.

2. Operator Mismatches:

   • Issue: begins_with/ends_with operators may behave counterintuitively (fixed in v1.1.2).
   • Fix: Use contains for partial matches or verify operator logic in custom rules.

3. MongoDB Quirks:

   • Issue: Regex syntax differs from SQL (e.g., \ escapes in MongoDB).
   • Fix: Test MongoDB queries thoroughly; use jenssegers/mongodb for proper escaping.

4. Date Handling:

   • Issue: Dates may not parse correctly without Carbon conversion.
   • Fix: Ensure Laravel’s date_format config matches your frontend date inputs.

Debugging Tips

• Log Raw Rules:

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

• Inspect Generated SQL:

  $toSql = $query->toSql();
  $bindings = $query->getBindings();
  \Log::debug($toSql, $bindings);
  

• Test Edge Cases:
  • Empty rules ([]).
  • NULL values (is_null/is_not_null).
  • Complex nested conditions (e.g., OR groups).

Extension Points

1. Custom Operators: Extend QueryBuilderParser to add support for domain-specific operators:

   class CustomQueryBuilderParser extends QueryBuilderParser {
       protected function registerCustomOperators() {
           $this->operators['custom_op'] = function ($query, $field, $value) {
               $query->where($field, 'LIKE', "%{$value}%");
           };
       }
   }
   

2. Post-Parse Hooks: Modify the query after parsing:

   $query = $qbp->parse($rules, $builder);
   $query->select(['id', 'name']); // Add custom selections
   

3. MongoDB Adapters: Override the parse method to handle MongoDB-specific logic (e.g., $lookup for joins).

Configuration Quirks

• Laravel 12/13:
  • Use app()->make('db') for dependency injection:

    $qbp = new QueryBuilderParser(['name'], app()->make('db'));
    

  • Leverage Laravel’s improved query caching for performance.
• Time Zones: Ensure Carbon’s timezone matches your database (e.g., config(['app.timezone' => 'UTC'])).

Performance

• Avoid N+1: Use with() or eager loading if relationships are queried.
• Batch Parsing: For bulk operations, parse rules once and reuse the query builder:

  $qbp = new QueryBuilderParser(['active', 'created_at']);
  $query = $qbp->parse($rules, DB::table('users'));
  $results = $query->get(); // Reuse $query for pagination/sorting