Recursive CTEs and Hierarchical Data in Laravel with PostgreSQL

The Problem With Nested Sets and Closure Tables

Most Laravel tutorials reach for nested sets or closure tables when modelling hierarchical data. Both work, but they add write complexity: every insert or move must update auxiliary columns or rows. PostgreSQL's WITH RECURSIVE gives you the same read power from a plain adjacency-list table—just a parent_id foreign key—with no extra bookkeeping.

The Schema

CREATE TABLE categories (
    id         BIGSERIAL PRIMARY KEY,
    parent_id  BIGINT REFERENCES categories(id) ON DELETE CASCADE,
    name       TEXT NOT NULL
);

CREATE INDEX idx_categories_parent ON categories(parent_id);

In Laravel the migration is straightforward:

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->foreignId('parent_id')->nullable()->constrained('categories')->cascadeOnDelete();
    $table->string('name');
});

Writing the Recursive CTE

A recursive CTE has two parts joined by UNION ALL: the anchor (the starting row) and the recursive member (the self-join that walks the tree).

WITH RECURSIVE subtree AS (
    -- anchor: the root we care about
    SELECT id, parent_id, name, 0 AS depth
    FROM   categories
    WHERE  id = :root_id

    UNION ALL

    -- recursive member: children of the current frontier
    SELECT c.id, c.parent_id, c.name, s.depth + 1
    FROM   categories c
    JOIN   subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree ORDER BY depth, name;

PostgreSQL iterates until no new rows are produced, so you get the full subtree in one round-trip.

Integrating With Eloquent

The cleanest approach is a local scope that swaps the base query for the CTE result:

class Category extends Model
{
    public function scopeSubtreeOf(Builder $query, int $rootId): Builder
    {
        $sql = <<<'SQL'
            WITH RECURSIVE subtree AS (
                SELECT id, parent_id, name, 0 AS depth
                FROM   categories
                WHERE  id = ?
                UNION ALL
                SELECT c.id, c.parent_id, c.name, s.depth + 1
                FROM   categories c
                JOIN   subtree s ON c.parent_id = s.id
            )
            SELECT * FROM subtree
        SQL;

        return $query
            ->fromSub(DB::raw("({$sql})"), 'categories')
            ->addBinding($rootId, 'from')
            ->orderBy('depth')
            ->orderBy('name');
    }
}

Usage is ergonomic:

$tree = Category::subtreeOf(42)->get();

Because fromSub replaces the FROM clause, all subsequent Eloquent constraints (where, with, select) still compose correctly.

Fetching Ancestors (Upward Traversal)

Flip the join direction to walk toward the root:

public function scopeAncestorsOf(Builder $query, int $leafId): Builder
{
    $sql = <<<'SQL'
        WITH RECURSIVE ancestors AS (
            SELECT id, parent_id, name, 0 AS depth
            FROM   categories
            WHERE  id = ?
            UNION ALL
            SELECT c.id, c.parent_id, c.name, a.depth + 1
            FROM   categories c
            JOIN   ancestors a ON c.id = a.parent_id
        )
        SELECT * FROM ancestors
    SQL;

    return $query
        ->fromSub(DB::raw("({$sql})"), 'categories')
        ->addBinding($leafId, 'from')
        ->orderByDesc('depth');
}

This is ideal for breadcrumb generation: Category::ancestorsOf($currentId)->pluck('name') returns the path from root to leaf.

Guarding Against Infinite Loops

PostgreSQL stops when the recursive member returns zero rows, but a corrupted parent_id cycle will loop forever. Add a depth guard:

WHERE s.depth < 50  -- inside the recursive member's WHERE clause

Or use the CYCLE clause available in PostgreSQL 14+:

WITH RECURSIVE subtree AS ( ... )
CYCLE id SET is_cycle USING path
SELECT * FROM subtree WHERE NOT is_cycle;

Performance Notes

• The index on parent_id is critical; PostgreSQL uses it on every recursive iteration.
• For very wide trees (thousands of siblings per level), add a composite index (parent_id, name) to cover the ORDER BY.
• EXPLAIN (ANALYZE, BUFFERS) will show a CTE Scan node; ensure it reads from the index rather than a sequential scan on the base table.

Takeaways

• A plain adjacency-list table plus WITH RECURSIVE handles most tree use-cases without closure tables or nested sets.
• Wrap the CTE in a fromSub scope so Eloquent constraints remain composable.
• Walk downward for subtrees, upward for breadcrumbs—same pattern, reversed join.
• Add a depth guard or PostgreSQL 14's CYCLE clause to protect against corrupt data.
• Index parent_id (and optionally cover with sort columns) to keep recursive iterations fast.