Laravel Cte Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require staudenmeir/laravel-cte
   

   For Laravel 5.5–5.7, add use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions; to your Eloquent models.

2. First Use Case: Replace a nested query with a CTE for cleaner, more efficient SQL:

   $recentPosts = DB::table('posts')
       ->withExpression('recent', function ($query) {
           $query->from('posts')
               ->where('created_at', '>', now()->subDays(7))
               ->select('id', 'title');
       })
       ->join('recent', 'recent.id', '=', 'posts.category_id')
       ->get();
   

Where to Look First

• README.md: Focus on the Usage section for syntax and examples.
• Compatibility Table: Verify your Laravel/DB version is supported (e.g., MySQL 8.0+).
• Recursive Expressions: Start here if working with hierarchical data (e.g., org charts).

Implementation Patterns

Core Workflows

1. Simple CTEs: Use withExpression() for non-recursive CTEs (e.g., subqueries for joins):

   DB::table('orders')
       ->withExpression('high_value', function ($query) {
           $query->from('orders')
               ->where('amount', '>', 1000)
               ->select('user_id');
       })
       ->whereIn('user_id', DB::table('high_value'))
       ->get();
   

2. Recursive CTEs: For hierarchical data (e.g., category trees), use withRecursiveExpression():

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

3. Eloquent Integration: Extend Eloquent models for recursive relationships:

   class Category extends Model
   {
       use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
   }
   
   $tree = Category::whereNull('parent_id')
       ->unionAll(
           Category::select('categories.*')
               ->join('tree', 'tree.id', '=', 'categories.parent_id')
       )
       ->withRecursiveExpression('tree', $query)
       ->get();
   

4. Write Operations: Use CTEs in INSERT/UPDATE/DELETE:

   DB::table('users')
       ->withExpression('active', DB::table('users')->where('status', 'active'))
       ->update(['last_login' => now()])
       ->whereIn('id', DB::table('active'));
   

Integration Tips

• Query Builder Extensions: Override the 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());
      });
  }
  

• Custom Columns: Explicitly define columns for CTEs to avoid ambiguity:

  DB::table('numbers')
      ->withRecursiveExpression('numbers', 'SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 10', ['number'])
      ->get();
  

• Cycle Detection: Prevent infinite recursion in hierarchical data (PostgreSQL/MariaDB 10.5.2+):

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

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

  DB::table('reports')
      ->withMaterializedExpression('temp_data', DB::table('logs')->where('status', 'completed'))
      ->get();
  

Gotchas and Tips

Pitfalls

1. Database Compatibility:

   • MySQL/MariaDB: No native cycle detection in versions < 10.5.2/PostgreSQL < 14. Use application-level checks if needed.
   • SQLite: Limited support for UPDATE/DELETE with CTEs. Test thoroughly.
   • Oracle: Requires manual builder instantiation (new \Staudenmeir\LaravelCte\Query\OracleBuilder).

2. Performance:

   • Recursive CTEs: Can be resource-intensive. Add LIMIT clauses or cycle detection.
   • Materialized CTEs: Improve performance but consume more memory. Use sparingly in high-traffic apps.

3. Query Builder Overrides:

   • Custom query builders may break CTE functionality. Ensure they extend \Staudenmeir\LaravelCte\Query\Builder.

4. Eloquent Traits:

   • Laravel 5.5–5.7 requires manual trait inclusion. Newer versions auto-register.

5. Column Ambiguity:

   • Omit column definitions in CTEs if the schema is unambiguous, but explicitly define them for complex queries.

Debugging

• SQL Dumping: Use toSql() to inspect generated CTEs:

  $sql = DB::table('posts')
      ->withExpression('recent', function ($query) { /* ... */ })
      ->toSql();
  

• Cycle Detection: If queries hang, check for infinite recursion or missing WHERE clauses in recursive CTEs.

• PostgreSQL Errors: Materialized CTEs may fail with ERROR: cannot use RETURNING to modify a materialized CTE. Use non-materialized CTEs instead.

Extension Points

1. Custom Grammar: Extend \Staudenmeir\LaravelCte\Query\Grammars\Grammar for database-specific syntax (e.g., Oracle’s WITH clause).

2. Query Macros: Add reusable CTE patterns:

   DB::macro('hierarchy', function ($model, $alias) {
       return $model->whereNull('parent_id')
           ->unionAll($model->join('tree', 'tree.id', '=', $model->getTable().'.parent_id'));
   });
   

3. Event Listeners: Log or validate CTE usage:

   DB::listen(function ($query) {
       if (str_contains($query->toSql(), 'WITH')) {
           Log::debug('CTE used:', ['sql' => $query->toSql()]);
       }
   });
   

Configuration Quirks

• Lumen: Requires manual builder instantiation for both Query Builder and Eloquent.
• Firebird: Limited support; use custom connection resolvers if needed.
• SingleStore: Test thoroughly for performance differences with materialized CTEs.

Pro Tips

• Readability: Use descriptive CTE names (e.g., withExpression('user_activity', ...)) for maintainability.
• Testing: Mock CTEs in unit tests with raw SQL strings to avoid database dependencies:

  $query = DB::table('users')
      ->withExpression('active', 'SELECT id FROM users WHERE status = "active"')
      ->get();
  

• Recursive Relationships: Pair with staudenmeir/laravel-adjacency-list for ORM-level support.