Laravel Query Expressions Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require tpetry/laravel-query-expressions
   

   No publisher or config required—just use it.

2. First Use Case Replace raw SQL in whereRaw(), orderByRaw(), or selectRaw() with expressions:

   // Before
   User::whereRaw('age > ? AND name LIKE ?', [25, '%John%']);
   
   // After
   User::where('age', '>', 25)
        ->where('name', 'like', '%John%');
   

   The package automatically converts raw queries into expressive syntax when possible.

3. Where to Look First

   • app/Providers/AppServiceProvider.php: Check if the package is booting (it’s auto-discovered).
   • config/database.php: No extra config needed, but verify query_builder is set to Illuminate\Database\Query\Builder.

Implementation Patterns

1. Replacing Raw Queries

• whereRaw → where/orWhere

  // Raw
  User::whereRaw('status = ? AND created_at > NOW() - INTERVAL ? DAY', [1, 7]);
  
  // Expressive
  User::where('status', 1)
       ->where('created_at', '>', now()->subDays(7));
  

  Works for: where, orWhere, having, orderByRaw, selectRaw.

• orderByRaw → orderBy

  // Raw
  User::orderByRaw('CASE WHEN active = 1 THEN 0 ELSE 1 END, name');
  
  // Expressive
  User::orderByRaw('CASE WHEN active = 1 THEN 0 ELSE 1 END, name')
       // OR (if supported)
       ->orderBy(fn($query) => $query->when(true, fn($q) => $q->orderBy('active', 'asc')));
  

  Note: Complex CASE statements may still need raw SQL.

2. Dynamic Query Building

• Conditional Expressions Use when() to toggle clauses dynamically:

  $query = User::query();
  $query->when($request->has('active'), fn($q) => $q->where('active', 1));
  $query->when($request->has('name'), fn($q) => $q->where('name', 'like', '%'.$request->name.'%'));
  

  The package preserves these expressive chains.

• Joins with Expressions

  User::join('posts', function($join) {
      $join->on('users.id', '=', 'posts.user_id')
           ->where('posts.published', 1);
  });
  

  No change needed—raw joins remain untouched.

3. Migration Queries

• DB::statement() → Expressive Alternatives Avoid raw statement() when possible:

  // Before (raw)
  DB::statement('UPDATE users SET votes = votes + 1 WHERE id = ?', [$userId]);
  
  // After (expressive)
  DB::table('users')->where('id', $userId)->increment('votes');
  

4. Query Scopes

• Convert Raw Scopes to Expressive

  // Raw scope
  class UserQuery extends QueryBuilder {
      public function scopeActive($query) {
          return $query->whereRaw('active = 1 AND deleted_at IS NULL');
      }
  }
  
  // Expressive scope
  class UserQuery extends QueryBuilder {
      public function scopeActive($query) {
          return $query->where('active', 1)
                       ->whereNull('deleted_at');
      }
  }
  

Gotchas and Tips

1. Limitations

• Unsupported Functions Some database functions (e.g., JSON_EXTRACT, REGEXP) cannot be converted and will fall back to raw SQL. Workaround: Use whereRaw with placeholders for these cases.

• Subqueries Nested subqueries in raw SQL may not convert cleanly:

  // May fail to convert
  User::whereRaw('id IN (SELECT user_id FROM orders WHERE amount > 100)');
  

  Tip: Rewrite as a join or use whereExists().

• Database-Specific Syntax MySQL’s NOW() converts to now(), but PostgreSQL’s CURRENT_TIMESTAMP may not. Test across databases.

2. Debugging

• Check Conversion Logs Enable query logging to see if expressions are being converted:

  DB::enableQueryLog();
  User::whereRaw('age > ?', [25])->get();
  dd(DB::getQueryLog());
  

  Look for bindings—if they’re missing, the conversion likely failed.

• Fallback to Raw If an expression fails to convert, the package silently falls back to raw SQL. Add a comment to document intentional raw usage:

  // Intentionally raw due to unsupported function
  User::whereRaw('MATCH(name) AGAINST(?)', [$searchTerm]);
  

3. Performance Tips

• Avoid Over-Conversion Complex raw queries (e.g., multi-table joins with JOIN ... ON) may not convert cleanly. Benchmark before/after.

• Use select() Instead of selectRaw()

  // Raw
  User::selectRaw('CONCAT(name, \' \', surname) as full_name');
  
  // Expressive (if possible)
  User::select([
      'name',
      DB::raw('CONCAT(name, \' \', surname) as full_name'),
  ]);
  

4. Extending the Package

• Custom Expression Rules Override the convertRawExpression() method in a service provider:

  public function boot() {
      \Tpetry\QueryExpressions\QueryBuilder::macro('customRule', function($query, $expression) {
          // Add logic to handle custom raw expressions
      });
  }
  

• Whitelist/Blacklist Functions Configure allowed functions in config/query-expressions.php (if available) or extend the ExpressionConverter class.

5. Testing

• Test Raw Fallbacks Ensure your app handles raw SQL gracefully:

  $this->assertDatabaseHas('users', [
      'id' => 1,
  ]);
  

  Use assertSql() from laravel-debugbar to verify query structure.

• Database-Specific Tests Test on MySQL, PostgreSQL, and SQLite if supporting multiple databases. Some functions (e.g., DATE_FORMAT) are MySQL-only.