- How do I install and use CTEs in Laravel 9+?
- Run `composer require staudenmeir/laravel-cte` and use `withExpression()` on a query builder instance. For example, `DB::table('posts')->withExpression('u', function($query) { $query->from('users'); })->join('u', ...)` defines a CTE named 'u'. No extra config is needed for Laravel 5.5+.
- Can I use recursive CTEs for hierarchical data (e.g., category trees)?
- Yes. Use `withRecursiveExpression()` to define recursive CTEs. For example, `DB::table('categories')->withRecursiveExpression('tree', function($query) { ... })->select('tree.*')` handles parent-child relationships efficiently, outperforming Eloquent’s nested `with()` calls.
- Does this package support materialized CTEs for performance?
- Yes, but only on databases like PostgreSQL and SQLite. Use `withMaterializedExpression()` to cache intermediate results for read-heavy workloads. Check your database version compatibility (e.g., PostgreSQL 14+ for full support).
- What Laravel versions are supported?
- The package supports Laravel 5.5 through 13.x. Each major Laravel version requires a specific package version (e.g., `1.13` for Laravel 13). Check the [version table](https://github.com/staudenmeir/laravel-cte#versions) for exact mappings.
- How do I handle cycle detection in recursive queries?
- Use `withRecursiveExpression()` with the `cycleDetection()` helper for PostgreSQL/MariaDB 10.5.2+. For older databases, implement application-layer checks or use `MAX_RECURSION` clauses manually. The package provides utilities to simplify this.
- Can I use CTEs in Eloquent models?
- Yes, but it requires manual integration. For Eloquent 8+, use `DB::table()` with CTEs and join results. For older versions, apply the `HasCTEs` trait or wrap queries in a custom scope. Recursive relationships are best handled via query builder.
- What databases are officially supported?
- MySQL 8.0+, MariaDB 10.2+, PostgreSQL 9.4+, SQLite 3.8.3+, SQL Server 2008+, Oracle 9.2+, and SingleStore 8.1+. Features like cycle detection or materialization may require newer versions of specific databases.
- How do I write INSERT/UPDATE/DELETE queries with CTEs?
- Use `withExpression()` in update/delete queries. For example, `DB::table('orders')->withExpression('high_value', function($query) { $query->from('orders')->where('amount', '>', 1000); })->whereIn('id', function($q) { $q->select('id')->from('high_value'); })->update(['status' => 'priority']);`
- Are there alternatives to this package for recursive queries?
- Yes, but they lack CTE integration. Options include raw SQL recursion, Eloquent’s nested `with()`, or packages like `spatie/laravel-activitylog` for audit trails. This package offers **10–100x performance gains** for hierarchical data by leveraging native database recursion.
- How do I test CTE queries in my CI pipeline?
- Use database-specific test containers (e.g., Dockerized PostgreSQL/MySQL) to validate CTE behavior. The package includes PHPUnit helpers for assertions. Test recursive limits, materialization, and edge cases like empty results or cycles.