Recursive CTEs for Hierarchical Data in Laravel | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. Recursive CTEs and Hierarchical Data in Laravel with PostgreSQL Recursive CTEs and Hierarchical Data in Laravel with PostgreSQL ================================================================ Learn how to query trees and hierarchies—categories, org charts, threaded comments—using recursive CTEs in PostgreSQL, wired cleanly into Eloquent with raw expressions and custom scopes. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 29 Aug 2026 · Updated 29 Aug 2026 · 3 min read ShareCopy linkCopied ![Recursive CTEs and Hierarchical Data in Laravel with PostgreSQL](https://cdn.msaied.com/607/ac508dd27011f0f2c57b0bee7707b740.png) On this page +1. [The Problem With Nested Sets and Closure Tables](#the-problem-with-nested-sets-and-closure-tables) 2. [The Schema](#the-schema) 3. [Writing the Recursive CTE](#writing-the-recursive-cte) 4. [Integrating With Eloquent](#integrating-with-eloquent) 5. [Fetching Ancestors (Upward Traversal)](#fetching-ancestors-upward-traversal) 6. [Guarding Against Infinite Loops](#guarding-against-infinite-loops) 7. [Performance Notes](#performance-notes) 8. [Takeaways](#takeaways) 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 ```sql 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: ```php 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). ```sql 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: ```php class Category extends Model { public function scopeSubtreeOf(Builder $query, int $rootId): Builder { $sql = addBinding($rootId, 'from') ->orderBy('depth') ->orderBy('name'); } } ``` Usage is ergonomic: ```php $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: ```php public function scopeAncestorsOf(Builder $query, int $leafId): Builder { $sql = 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: ```sql WHERE s.depth < 50 -- inside the recursive member's WHERE clause ``` Or use the `CYCLE` clause available in PostgreSQL 14+: ```sql 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) - [database](https://www.msaied.com/public/articles?search=database) Frequently asked questions --------------------------- Can I eager-load relationships on the result of a recursive CTE scope?Yes. Because the scope uses `fromSub` to replace the FROM clause rather than wrapping the entire query, Eloquent's `with()` calls still append the standard relationship sub-queries. Just chain `->with('products')` as normal after `subtreeOf()`. Is `WITH RECURSIVE` significantly slower than a closure table for reads?For moderate tree depths (under ~20 levels) and a proper index on `parent\_id`, the difference is negligible. Closure tables win on very deep trees with millions of rows because they trade write cost for a flat read. Profile with `EXPLAIN ANALYZE` for your specific data shape before optimising prematurely. Does this approach work with MySQL?MySQL 8.0+ supports `WITH RECURSIVE`, so the SQL is portable. However, the `CYCLE` detection clause is PostgreSQL-specific. On MySQL you must rely on a depth guard in the WHERE clause of the recursive member. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleLaravel API Resources: Sparse Fieldsets, Cursor Pagination, and Per-Route Rate Limiting](https://www.msaied.com/public/articles/laravel-api-resources-sparse-fieldsets-cursor-pagination-and-per-route-rate-limiting) [Next articleBlackfire & Xdebug Profiling in Laravel: Finding Real Bottlenecks](https://www.msaied.com/public/articles/blackfire-xdebug-profiling-in-laravel-finding-real-bottlenecks-2) On this page ------------- 1. [The Problem With Nested Sets and Closure Tables](#the-problem-with-nested-sets-and-closure-tables) 2. [The Schema](#the-schema) 3. [Writing the Recursive CTE](#writing-the-recursive-cte) 4. [Integrating With Eloquent](#integrating-with-eloquent) 5. [Fetching Ancestors (Upward Traversal)](#fetching-ancestors-upward-traversal) 6. [Guarding Against Infinite Loops](#guarding-against-infinite-loops) 7. [Performance Notes](#performance-notes) 8. [Takeaways](#takeaways) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)