Laravel CTE (staudenmeir/laravel-cte)

Laravel CTE

https://github.com/staudenmeir/laravel-cte
Admin
This Laravel extension adds support for common table expressions (CTE) to the query builder and...

Tokens:6,501
Snippets:42
Trust Score:8.6
Update:3 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# Laravel CTE

## Introduction

Laravel CTE is a Laravel extension package that adds comprehensive support for Common Table Expressions (CTEs) to the query builder and Eloquent ORM. CTEs are temporary named result sets that can be referenced within SELECT, INSERT, UPDATE, or DELETE statements, providing a powerful way to organize complex queries, implement recursive queries, and improve query readability. This package seamlessly integrates with Laravel's database layer, requiring minimal configuration and maintaining compatibility with Laravel's fluent query builder interface.

The package supports both simple and recursive CTEs across multiple database systems including MySQL 8.0+, MariaDB 10.2+, PostgreSQL 9.4+, SQLite 3.8.3+, SQL Server 2008+, Oracle 9.2+, SingleStore 8.1+, and Firebird. It extends Laravel's native query builder with new methods for defining CTEs, supports materialized and non-materialized expressions, includes cycle detection for recursive queries, and provides recursion limits. The package automatically registers itself in Laravel applications and can be used with both the query builder and Eloquent models.

## API Reference

### withExpression() - Define a Common Table Expression

Adds a non-recursive common table expression to a query, allowing you to define a named subquery that can be referenced in the main query. This is useful for breaking down complex queries into more manageable parts, reusing subquery results multiple times, or improving query readability.

```php
use Illuminate\Support\Facades\DB;

// Using query builder instance
$posts = DB::table('p')
    ->select('p.*', 'u.name')
    ->withExpression('p', DB::table('posts')->where('published', true))
    ->withExpression('u', DB::table('users')->where('active', true))
    ->join('u', 'u.id', '=', 'p.user_id')
    ->orderBy('p.created_at', 'desc')
    ->get();

// Using closure
$users = DB::table('u')
    ->withExpression('u', function ($query) {
        $query->from('users')
              ->where('created_at', '>', '2024-01-01')
              ->select('id', 'name', 'email');
    })
    ->where('name', 'like', '%John%')
    ->get();

// Using raw SQL string
$result = DB::table('active_users')
    ->withExpression('active_users', "SELECT * FROM users WHERE status = 'active'")
    ->get();

// Result: Collection of users with their published posts
```

### withRecursiveExpression() - Define a Recursive CTE

Creates a recursive common table expression that can reference itself, enabling hierarchical or tree-structured data queries such as organizational charts, category trees, or graph traversals. The recursive CTE consists of an anchor member (base case) and a recursive member joined with UNION or UNION ALL.

```php
use Illuminate\Support\Facades\DB;

// Hierarchical user tree traversal
$query = DB::table('users')
    ->select('id', 'parent_id', 'name', 'email')
    ->whereNull('parent_id')  // Anchor: root nodes
    ->unionAll(
        DB::table('users')
            ->select('users.id', 'users.parent_id', 'users.name', 'users.email')
            ->join('tree', 'tree.id', '=', 'users.parent_id')  // Recursive: children
    );

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

// Generate number sequence
$numberQuery = 'select 1 as number union all select number + 1 from numbers where number < 10';

$numbers = DB::table('numbers')
    ->withRecursiveExpression('numbers', $numberQuery, ['number'])
    ->get();
// Result: Collection with numbers 1 through 10

// Category hierarchy with depth
$categoryQuery = DB::table('categories')
    ->selectRaw('id, parent_id, name, 0 as depth')
    ->whereNull('parent_id')
    ->unionAll(
        DB::table('categories')
            ->selectRaw('categories.id, categories.parent_id, categories.name, cat_tree.depth + 1')
            ->join('cat_tree', 'cat_tree.id', '=', 'categories.parent_id')
    );

$categories = DB::table('cat_tree')
    ->withRecursiveExpression('cat_tree', $categoryQuery)
    ->orderBy('depth')
    ->get();
```

### withRecursiveExpressionAndCycleDetection() - Recursive CTE with Cycle Prevention

Defines a recursive CTE with built-in cycle detection to prevent infinite loops when traversing graphs with circular references. This is particularly useful for data structures like social networks, linked lists with potential loops, or organizational hierarchies where cycles might exist.

```php
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

// Detect cycles in user relationships (PostgreSQL or MariaDB only)
$query = DB::table('users')
    ->select('id', 'parent_id', 'name')
    ->where('id', 1)
    ->unionAll(
        DB::table('users')
            ->select('users.id', 'users.parent_id', 'users.name')
            ->join('ancestors', 'ancestors.parent_id', '=', 'users.id')
    );

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

// With custom cycle column names (PostgreSQL)
$result = DB::table('ancestors')
    ->withRecursiveExpressionAndCycleDetection(
        'ancestors',
        $query,
        'id',              // Column(s) that indicate a cycle
        'is_cycle',        // Column name for cycle detection flag
        'path'             // Column name for path tracking
    )
    ->get();

// Each row includes:
// - is_cycle: boolean indicating if this row creates a cycle
// - path: array showing the traversal path
// PostgreSQL result example: is_cycle = false, path = '{(1)}'

// Multiple columns for cycle detection
$graphQuery = DB::table('edges')
    ->select('from_node', 'to_node', 'weight')
    ->where('from_node', 1)
    ->unionAll(
        DB::table('edges')
            ->select('edges.from_node', 'edges.to_node', 'edges.weight')
            ->join('graph', 'graph.to_node', '=', 'edges.from_node')
    );

$graph = DB::table('graph')
    ->withRecursiveExpressionAndCycleDetection('graph', $graphQuery, ['from_node', 'to_node'])
    ->get();
// Tracks cycles based on both from_node and to_node combinations
```

### withMaterializedExpression() - Materialized CTE

Creates a materialized common table expression that forces the database to compute and store the CTE result before executing the main query. This is beneficial for CTEs that are referenced multiple times or when you want to ensure the subquery is executed only once.

```php
use Illuminate\Support\Facades\DB;

// PostgreSQL or SQLite only
$posts = DB::table('p')
    ->select('p.*', 'u.name')
    ->withMaterializedExpression('p', DB::table('posts')->where('views', '>', 1000))
    ->withMaterializedExpression('u', function ($query) {
        $query->from('users')
              ->where('verified', true)
              ->select('id', 'name');
    })
    ->join('u', 'u.id', '=', 'p.user_id')
    ->get();

// Forces database to materialize the subquery
// Useful when CTE is referenced multiple times
$expensiveQuery = DB::table('analytics')
    ->selectRaw('user_id, SUM(revenue) as total_revenue')
    ->groupBy('user_id')
    ->havingRaw('SUM(revenue) > 10000');

$result = DB::table('top_users')
    ->withMaterializedExpression('top_users', $expensiveQuery)
    ->join('orders', 'orders.user_id', '=', 'top_users.user_id')
    ->select('top_users.*', DB::raw('COUNT(orders.id) as order_count'))
    ->groupBy('top_users.user_id', 'top_users.total_revenue')
    ->get();
// Result: Database materializes top_users once, then joins with orders
```

### withNonMaterializedExpression() - Non-Materialized CTE

Creates a non-materialized common table expression that hints to the database optimizer to inline the CTE into the main query. This can improve performance when the CTE is simple and referenced only once, allowing the optimizer to apply optimizations across the entire query.

```php
use Illuminate\Support\Facades\DB;

// PostgreSQL or SQLite only
$users = DB::table('u')
    ->select('u.id', 'u.name', 'u.email')
    ->withNonMaterializedExpression('u', DB::table('users')->where('active', true))
    ->where('created_at', '>', '2024-01-01')
    ->orderBy('name')
    ->get();

// Database may inline this CTE into the main query
// Good for simple CTEs referenced once
$recentOrders = DB::table('recent')
    ->withNonMaterializedExpression('recent', function ($query) {
        $query->from('orders')
              ->where('created_at', '>', now()->subDays(7))
              ->select('id', 'user_id', 'total');
    })
    ->where('total', '>', 100)
    ->get();
// Optimizer can merge WHERE clauses and optimize the entire query
```

### recursionLimit() - Set Recursion Depth Limit

Sets a maximum recursion depth for recursive CTEs, preventing infinite loops and controlling query execution time. This is particularly important for SQL Server, where recursion limits must be explicitly defined.

```php
use Illuminate\Support\Facades\DB;

// SQL Server requires explicit recursion limit
$query = 'select 1 as number union all select number + 1 from numbers where number < 200';

$numbers = DB::table('numbers')
    ->withRecursiveExpression('numbers', $query, ['number'])
    ->recursionLimit(150)
    ->get();
// Stops at recursion depth of 150

// Organizational hierarchy with depth limit
$orgQuery = DB::table('employees')
    ->selectRaw('id, manager_id, name, 1 as level')
    ->whereNull('manager_id')
    ->unionAll(
        DB::table('employees')
            ->selectRaw('employees.id, employees.manager_id, employees.name, org.level + 1')
            ->join('org', 'org.id', '=', 'employees.manager_id')
            ->whereRaw('org.level < 10')
    );

$org = DB::table('org')
    ->withRecursiveExpression('org', $orgQuery)
    ->recursionLimit(10)
    ->get();
// Limits hierarchy traversal to 10 levels maximum
```

### insertUsing() - INSERT with CTE

Inserts records into a table using data from a common table expression. This allows complex data transformations and filtering before insertion, combining the power of CTEs with INSERT operations.

```php
use Illuminate\Support\Facades\DB;

// Insert filtered user data into profiles table
$userQuery = DB::table('users')
    ->select('id as user_id', 'name', 'email')
    ->where('verified', true)
    ->where('created_at', '>', '2024-01-01');

DB::table('profiles')
    ->withExpression('verified_users', $userQuery)
    ->insertUsing(
        ['user_id', 'name', 'email'],
        DB::table('verified_users')
    );
// Inserts verified users into profiles table

// Complex transformation with multiple CTEs
$activeUsers = DB::table('users')->where('active', true);
$recentOrders = DB::table('orders')
    ->selectRaw('user_id, COUNT(*) as order_count, SUM(total) as total_spent')
    ->where('created_at', '>', now()->subMonths(3))
    ->groupBy('user_id');

DB::table('user_stats')
    ->withExpression('active', $activeUsers)
    ->withExpression('orders', $recentOrders)
    ->insertUsing(
        ['user_id', 'name', 'order_count', 'total_spent'],
        DB::table('active')
            ->select('active.id', 'active.name', 'orders.order_count', 'orders.total_spent')
            ->leftJoin('orders', 'orders.user_id', '=', 'active.id')
    );
// Combines multiple CTEs for complex data aggregation before insert
```

### update() - UPDATE with CTE

Updates records using data from common table expressions, enabling complex conditional updates based on joined data or aggregations.

```php
use Illuminate\Support\Facades\DB;

// Update profiles using CTE data
DB::table('profiles')
    ->withExpression('u', DB::table('users')->select('id', 'name', 'email'))
    ->join('u', 'u.id', '=', 'profiles.user_id')
    ->update(['profiles.name' => DB::raw('u.name')]);

// Update with aggregated data
$orderStats = DB::table('orders')
    ->selectRaw('user_id, COUNT(*) as total_orders, SUM(amount) as total_spent')
    ->where('status', 'completed')
    ->groupBy('user_id');

DB::table('users')
    ->withExpression('stats', $orderStats)
    ->join('stats', 'stats.user_id', '=', 'users.id')
    ->update([
        'users.total_orders' => DB::raw('stats.total_orders'),
        'users.total_spent' => DB::raw('stats.total_spent'),
        'users.updated_at' => now()
    ]);
// Updates user statistics from aggregated order data

// Conditional update based on CTE
$inactiveUsers = DB::table('users')
    ->select('id')
    ->whereNotIn('id', function ($query) {
        $query->select('user_id')
              ->from('login_logs')
              ->where('created_at', '>', now()->subMonths(6));
    });

DB::table('users')
    ->withExpression('inactive', $inactiveUsers)
    ->join('inactive', 'inactive.id', '=', 'users.id')
    ->update(['users.status' => 'inactive']);
```

### delete() - DELETE with CTE

Deletes records using common table expressions to identify which records should be removed based on complex criteria or joined data.

```php
use Illuminate\Support\Facades\DB;

// Delete profiles for inactive users
$inactiveUsers = DB::table('users')
    ->select('id')
    ->where('active', false)
    ->where('last_login', '<', now()->subYear());

DB::table('profiles')
    ->withExpression('u', $inactiveUsers)
    ->whereIn('user_id', DB::table('u')->select('id'))
    ->delete();

// Delete old records with complex criteria
$obsoleteRecords = DB::table('logs')
    ->select('id')
    ->where('created_at', '<', now()->subYears(2))
    ->where('level', 'debug');

DB::table('logs')
    ->withExpression('obsolete', $obsoleteRecords)
    ->whereIn('id', DB::table('obsolete')->select('id'))
    ->delete();
// Deletes old debug logs using CTE

// Delete using recursive CTE (cascade delete)
$childCategories = DB::table('categories')
    ->select('id')
    ->where('id', 10)
    ->unionAll(
        DB::table('categories')
            ->select('categories.id')
            ->join('tree', 'tree.id', '=', 'categories.parent_id')
    );

DB::table('categories')
    ->withRecursiveExpression('tree', $childCategories)
    ->whereIn('id', DB::table('tree')->select('id'))
    ->delete();
// Recursively deletes category and all descendants
```

### QueriesExpressions Trait - Eloquent Integration

Enables CTE functionality in Eloquent models by providing a custom query builder that supports expression methods. Required for Laravel 5.5-5.7 and all Lumen versions; automatic in Laravel 5.8+.

```php
use Illuminate\Database\Eloquent\Model;
use Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;

class User extends Model
{
    use QueriesExpressions;

    protected $fillable = ['name', 'email', 'parent_id'];
}

// Use CTEs in Eloquent queries
$query = User::select('id', 'parent_id', 'name')
    ->whereNull('parent_id')
    ->unionAll(
        User::select('users.id', 'users.parent_id', 'users.name')
            ->join('tree', 'tree.id', '=', 'users.parent_id')
    );

$tree = User::from('tree')
    ->withRecursiveExpression('tree', $query)
    ->orderBy('id')
    ->get();

// Complex Eloquent query with CTEs
$activeUsers = User::where('status', 'active')
    ->where('email_verified_at', '!=', null);

$users = User::from('active')
    ->withExpression('active', $activeUsers)
    ->with(['posts', 'comments'])
    ->orderBy('created_at', 'desc')
    ->paginate(20);

// Recursive relationship traversal
class Category extends Model
{
    use QueriesExpressions;

    public function descendants()
    {
        $query = static::select('id', 'parent_id', 'name')
            ->where('id', $this->id)
            ->unionAll(
                static::select('categories.id', 'categories.parent_id', 'categories.name')
                    ->join('tree', 'tree.id', '=', 'categories.parent_id')
            );

        return static::from('tree')
            ->withRecursiveExpression('tree', $query)
            ->where('id', '!=', $this->id);
    }
}

$category = Category::find(1);
$allDescendants = $category->descendants()->get();
```

## Summary and Integration

Laravel CTE is designed to integrate seamlessly into existing Laravel applications with minimal configuration changes. The primary use cases include hierarchical data queries (organizational charts, category trees, file systems), recursive graph traversal (social networks, dependency resolution), complex reporting queries that benefit from breaking down logic into manageable CTEs, data migration and transformation tasks, and performance optimization through materialized expressions. The package is particularly valuable when working with self-referential data structures, implementing bill-of-materials queries, or simplifying complex joins and subqueries.

Integration is straightforward: install via Composer, and the package auto-registers through Laravel's service provider discovery. For standard databases (MySQL, PostgreSQL, SQLite, SQL Server), the query builder automatically gains CTE methods. For Eloquent models in Laravel 5.5-5.7 or Lumen, add the QueriesExpressions trait. For Oracle and Firebird, instantiate the appropriate builder manually. The API follows Laravel's fluent interface conventions, making it intuitive for developers familiar with Laravel's query builder. All CTE methods return the builder instance, allowing method chaining with standard query builder methods like where(), join(), orderBy(), and get(). Error handling follows Laravel's standard exceptions, and the package respects Laravel's connection configuration, supporting multiple database connections and read/write splitting.