Laravel Cte Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require staudenmeir/laravel-cte
   

   For Laravel 5.5–5.7, add the QueriesExpressions trait to Eloquent models:

   use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
   

2. First Query: Define a CTE and use it in a join:

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

First Use Case: Hierarchical Data

Replace a recursive PHP loop with a recursive CTE for a category tree:

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

$tree = DB::table('tree')
    ->withRecursiveExpression('tree', $query)
    ->get();

Implementation Patterns

Query Builder Patterns

1. CTE Chaining: Chain CTEs for complex workflows (e.g., reporting):

   $query = DB::table('orders')
       ->withExpression('users', DB::table('users')->select('id', 'name'))
       ->withExpression('recent_orders', function ($q) {
           $q->from('orders')
             ->where('created_at', '>', now()->subMonth());
       })
       ->join('users', 'users.id', '=', 'orders.user_id')
       ->join('recent_orders', 'recent_orders.user_id', '=', 'users.id');
   

2. Recursive Relationships: Use withRecursiveExpression for nested data (e.g., comments):

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

3. Materialized CTEs (PostgreSQL/SQLite): Optimize performance for large datasets:

   $query = DB::table('posts')
       ->withMaterializedExpression('popular_posts', DB::table('posts')
           ->where('views', '>', 1000)
           ->orderBy('views', 'desc')
       )
       ->join('popular_posts', 'popular_posts.id', '=', 'posts.id');
   

Eloquent Integration

1. Model-Level CTEs: Extend Eloquent models with CTEs:

   class Post extends Model {
       public function scopeWithAuthor($query) {
           return $query->withExpression('author', DB::table('users')
               ->whereColumn('users.id', 'posts.user_id')
           );
       }
   }
   

2. Recursive Relationships: Pair with staudenmeir/laravel-adjacency-list for tree structures:

   class Category extends Model {
       use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
       use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
   
       public function children() {
           return $this->hasMany(Category::class, 'parent_id');
       }
   }
   

DML Operations

1. INSERT/UPDATE/DELETE with CTEs: Use CTEs in non-SELECT queries:

   // Update
   DB::table('profiles')
       ->withExpression('u', DB::table('users')->select('id', 'name'))
       ->join('u', 'u.id', '=', 'profiles.user_id')
       ->update(['name' => DB::raw('u.name')]);
   
   // Delete
   DB::table('inactive_users')
       ->withExpression('u', DB::table('users')->where('last_login', '<', now()->subYear()))
       ->whereIn('id', DB::table('u')->select('id'))
       ->delete();
   

Gotchas and Tips

Common Pitfalls

1. Database Compatibility:

   • Recursive CTEs: Only supported in MySQL 8.0+, PostgreSQL 9.4+, SQLite 3.8.3+, etc.
   • Materialized CTEs: PostgreSQL/SQLite only. Use withMaterializedExpression().
   • Cycle Detection: MariaDB 10.5.2+/PostgreSQL 14+ support native cycle detection via withRecursiveExpressionAndCycleDetection().

2. Column Mismatches: Ensure CTE columns match the main query’s schema to avoid SQL errors:

   // ❌ Error: Column 'name' not found
   DB::table('posts')
       ->withExpression('u', DB::table('users')->select('id'))
       ->join('u', 'u.id', '=', 'posts.user_id')
       ->select('posts.*', 'u.name'); // 'name' missing in CTE
   
   // ✅ Fix: Include all required columns
   DB::table('posts')
       ->withExpression('u', DB::table('users')->select('id', 'name'))
       ->join('u', 'u.id', '=', 'posts.user_id')
       ->select('posts.*', 'u.name');
   

3. Oracle/Lumen Quirks:

   • Oracle: Requires manual builder instantiation:

     $builder = new \Staudenmeir\LaravelCte\Query\OracleBuilder(DB::connection());
     

   • Lumen: Always use the QueriesExpressions trait in Eloquent models.

4. Performance:

   • Recursive Depth: Deep recursion (e.g., >10 levels) may hit database limits. Use WITH RECURSIVE explicitly if needed.
   • Materialization: Materialized CTEs improve performance but consume more memory. Use sparingly for large datasets.

Debugging Tips

1. Inspect Raw SQL: Use toSql() and getBindings() to debug CTEs:

   $sql = $query->toSql();
   $bindings = $query->getBindings();
   

2. Cycle Detection: For infinite loops, enable cycle detection:

   $query = DB::table('tree')
       ->withRecursiveExpressionAndCycleDetection('tree', $recursiveQuery, 'id')
       ->get();
   

3. PostgreSQL-Specific:

   • Use EXPLAIN ANALYZE to profile materialized CTEs:

     EXPLAIN ANALYZE SELECT * FROM (WITH cte AS (...)) main_query;
     

Extension Points

1. Custom Grammar: Override grammar for unsupported databases by extending \Staudenmeir\LaravelCte\Query\Grammars\Grammar.

2. Query Macros: Add reusable CTE patterns:

   DB::macro('withUserData', function ($query) {
       return $query->withExpression('u', DB::table('users')->select('id', 'name'));
   });
   

3. Cycle Detection Customization (PostgreSQL): Rename cycle detection columns:

   $query->withRecursiveExpressionAndCycleDetection(
       'tree',
       $recursiveQuery,
       'id',
       'is_cycle_detected', // Custom cycle column name
       'path'              // Custom path column name
   );
   

Pro Tips

1. Hybrid Queries: Combine CTEs with Laravel’s query builder features:

   $query = DB::table('orders')
       ->withExpression('u', DB::table('users'))
       ->join('u', 'u.id', '=', 'orders.user_id')
       ->whereHasMorph('orders', [Product::class, Service::class]);
   

2. Dynamic CTEs: Build CTEs dynamically based on input:

   $cte = DB::table('products')
       ->where('category_id', $request->category_id);
   
   $query = DB::table('products as p')
       ->withExpression('filtered', $cte)
       ->join('filtered', 'filtered.id', '=', 'p.id');
   

3. Testing: Use DB::shouldReceive('select')->with(...) to test CTEs in PHPUnit:

   DB::shouldReceive('select')
       ->once()
       ->with('SELECT * FROM (WITH cte AS (...)) main_query');