Laravel Cte Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require staudenmeir/laravel-cte
   

   For Laravel 13.x, use ^1.13.0; for 12.x, use ^1.12.0, etc. (see version compatibility).

2. First use case: Replace a nested subquery with a CTE for a simple join:

   $posts = DB::table('posts')
       ->withExpression('users', DB::table('users')->select('id', 'name'))
       ->join('users', 'users.id', '=', 'posts.user_id')
       ->get();
   

Where to Look First

• README.md#Usage: Focus on withExpression() for basic CTEs and withRecursiveExpression() for hierarchical data.
• Database Compatibility: Check supported databases before using recursive features (e.g., MySQL 8.0+ for WITH RECURSIVE).
• Eloquent Integration: For Eloquent, add the QueriesExpressions trait (Laravel 5.5–5.7 only).

Implementation Patterns

Core Workflows

1. Hierarchical Data (Recursive CTEs)

• Pattern: Replace adjacency-list recursion with a single query.
• Example: Category tree with parent-child relationships:

  $categories = DB::table('categories')
      ->whereNull('parent_id')
      ->unionAll(
          DB::table('categories')
              ->select('categories.*')
              ->join('tree', 'tree.id', '=', 'categories.parent_id')
      )
      ->withRecursiveExpression('tree', $query)
      ->get();
  

• Optimization: Use withRecursiveExpressionAndCycleDetection() for PostgreSQL/MySQL 10.5.2+ to avoid infinite loops.

2. Multi-Step Aggregations (Materialized CTEs)

• Pattern: Chain CTEs for complex calculations (e.g., rolling averages, path analysis).
• Example: PostgreSQL materialized CTE for a running total:

  $query = DB::table('orders')
      ->select('customer_id', DB::raw('sum(amount) as total'))
      ->groupBy('customer_id');
  
  $results = DB::table('customers')
      ->withMaterializedExpression('order_totals', $query)
      ->join('order_totals', 'order_totals.customer_id', '=', 'customers.id')
      ->get();
  

3. Bulk Operations (INSERT/UPDATE/DELETE)

• Pattern: Use CTEs to reference intermediate results in DML statements.
• Example: Batch update with a CTE:

  DB::table('profiles')
      ->withExpression('inactive_users', DB::table('users')->where('active', false))
      ->update(['last_updated' => now()])
      ->whereIn('user_id', DB::table('inactive_users')->select('id'));
  

4. Eloquent Integration

• Pattern: Extend Eloquent models for recursive relationships.
• Example: User hierarchy with the QueriesExpressions trait:

  class User extends Model {
      use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
  }
  
  $tree = User::from('tree')
      ->withRecursiveExpression('tree', User::whereNull('parent_id')->unionAll(...))
      ->get();
  

Integration Tips

• Query Builder Extensions: Override the default query builder in AppServiceProvider for global CTE support:

  public function boot() {
      DB::extend('cte', function ($app, $config) {
          return new \Staudenmeir\LaravelCte\Query\Builder($app['db']->connection());
      });
  }
  

• Dynamic CTEs: Build CTEs dynamically based on request parameters:

  $cte = DB::table('logs')
      ->where('created_at', '>', request('date'))
      ->select('user_id', DB::raw('count(*) as count'));
  
  $results = DB::table('users')
      ->withExpression('recent_logs', $cte)
      ->join('recent_logs', 'recent_logs.user_id', '=', 'users.id')
      ->get();
  

• Performance: For large datasets, limit CTE results early:

  $cte = DB::table('products')
      ->where('price', '>', 100)
      ->limit(1000); // Cap intermediate results
  

Gotchas and Tips

Pitfalls

1. Database-Specific Syntax:

   • MySQL/MariaDB: Requires WITH RECURSIVE syntax (8.0+/10.5.2+). Older versions throw errors.
   • SQLite: Supports CTEs but lacks WITH RECURSIVE (use WITH for non-recursive).
   • Oracle: Requires manual builder instantiation (OracleBuilder).
   • Firebird: Limited support; test thoroughly.

2. Cycle Detection:

   • Only works on PostgreSQL 14+ and MariaDB 10.5.2+. Fallback to manual cycle checks for other databases:

     $query->where('depth', '<', 100); // Arbitrary depth limit
     

3. Column Mismatches:

   • CTEs must align columns with the outer query. Explicitly define columns to avoid ambiguity:

     // Bad: Implicit columns may not match.
     $cte = DB::table('orders')->select('*');
     
     // Good: Explicit columns.
     $cte = DB::table('orders')->select('id', 'user_id', 'amount');
     

4. Eloquent Limitations:

   • Laravel 5.8+: No trait needed; CTEs work out-of-the-box with the query builder.
   • Laravel 5.5–5.7: Require the QueriesExpressions trait for Eloquent support.

5. Performance Quirks:

   • Materialized CTEs: PostgreSQL/SQLite materialize results, which can improve performance but consume memory.
   • Recursive Depth: Deep recursion may hit database limits (e.g., MySQL’s max_recursion_depth).

Debugging Tips

• Inspect SQL: Use toSql() to verify CTE syntax:

  $query = DB::table('posts')->withExpression('users', ...);
  dd($query->toSql());
  

• Log Bindings: Check for missing or incorrect bindings:

  $query->toSql(); // SQL
  $query->getBindings(); // Bindings
  

• Test Incrementally: Start with simple CTEs, then add complexity (e.g., joins, unions).

Extension Points

1. Custom Grammar: Override the grammar for database-specific tweaks (e.g., Oracle’s WITH syntax):

   use Staudenmeir\LaravelCte\Query\Grammars\OracleGrammar;
   
   DB::connection()->setQueryGrammar(new OracleGrammar());
   

2. Query Macros: Add reusable CTE patterns:

   DB::macro('hierarchy', function ($table, $parentColumn) {
       return DB::table($table)
           ->whereNull($parentColumn)
           ->unionAll(
               DB::table($table)
                   ->select('*')
                   ->join('tree', 'tree.id', '=', $parentColumn)
           );
   });
   
   // Usage:
   $tree = DB::table('categories')
       ->withRecursiveExpression('tree', DB::hierarchy('categories', 'parent_id'))
       ->get();
   

3. Event Listeners: Hook into query events to modify CTEs dynamically:

   DB::listen(function ($query) {
       if ($query->hasCte('users')) {
           $query->where('users.active', true);
       }
   });
   

Configuration Quirks

• Lumen: Requires manual builder instantiation:

  $builder = new \Staudenmeir\LaravelCte\Query\Builder(app('db')->connection());
  $results = $builder->withExpression('users', ...)->get();
  

• Oracle: Use OracleBuilder explicitly:

  $builder = new \Sta