Mastering Common Table Expressions (CTEs) in Laravel Eloquent

· 3 min read
#laravel #database #eloquent #sql #performance

Common Table Expressions (CTEs) are temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. They are incredibly useful for breaking down complex queries and handling recursive data structures (like categories or comments).

Starting with recent Laravel versions, Eloquent has native support for CTEs, making raw SQL strings a thing of the past for these scenarios.

Why use CTEs?

  1. Readability: Break a complex nested subquery into named, logical steps.
  2. Recursion: Easily query hierarchical data (parents, children, trees).
  3. Performance: Often more efficient than calculating logic in PHP.

Basic Example: High Value Users

Imagine you want to find users who have placed orders totaling more than $1000.

Without CTE, you might use a whereHas or a subquery.

// Using CTE with Laravel's new query builder methods

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->withExpression('big_spenders', function (Builder $query) {
        $query->select('user_id')
              ->from('orders')
              ->groupBy('user_id')
              ->havingRaw('SUM(total) > ?', [1000]);
    })
    ->join('big_spenders', 'users.id', '=', 'big_spenders.user_id')
    ->get();

Recursive CTEs: The Category Tree

This is the "killer feature" of CTEs. Imagine a categories table with id and parent_id. How do you get a category and all its descendants?

$categoryId = 1;

$categories = Category::query()
    ->withRecursiveExpression('descendants', function (Builder $query) use ($categoryId) {
        // Anchor member: select the starting category
        $query->from('categories')
              ->where('id', $categoryId);

        // Recursive member: join with the anchor
        $query->unionAll(
            DB::table('categories')
                ->join('descendants', 'categories.parent_id', '=', 'descendants.id')
        );
    })
    ->from('descendants')
    ->get();

This single query fetches the entire tree structure efficiently.

Using CTEs to Clean Up Logic

Instead of creating a temporary table or doing heavy processing in PHP Collections, use CTEs to prepare data.

Example: Calculate daily stats first, then query them.

$stats = DB::table('daily_stats')
    ->withExpression('marketing_stats', function ($query) {
        $query->selectRaw('DATE(created_at) as date, COUNT(*) as clicks')
              ->from('clicks')
              ->groupBy('date');
    })
    ->withExpression('sales_stats', function ($query) {
        $query->selectRaw('DATE(created_at) as date, SUM(amount) as revenue')
              ->from('orders')
              ->groupBy('date');
    })
    ->select('marketing_stats.date', 'clicks', 'revenue')
    ->from('marketing_stats')
    ->join('sales_stats', 'marketing_stats.date', '=', 'sales_stats.date')
    ->get();

Tips for Laravel Developers

  1. Driver Support: CTEs work on MySQL 8.0+, PostgreSQL, SQLite, and SQL Server. Ensure your production DB supports them.
  2. Debugging: Use ->toSql() or dd() to inspect the generated SQL if you get syntax errors.
  3. Eloquent Integration: While DB::table is common, you can also use Model::withExpression(...) to keep returning Model instances instead of generic objects.

Conclusion

CTEs turn "impossible" queries into manageable, readable code. By leveraging Laravel's native support, you can push data-heavy logic to the database layer where it belongs, keeping your PHP application fast and light.

Comments