PostgreSQL Recursive CTEs in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ home ](https://www.msaied.com) [ articles ](https://www.msaied.com/articles) PostgreSQL Recursive CTEs in Laravel: Hierarchical Data Without the ORM Gymnastics On this page 1. [ The Problem With Adjacency Lists at Scale ](#the-problem-with-adjacency-lists-at-scale) 2. [ Anatomy of a Recursive CTE ](#anatomy-of-a-recursive-cte) 3. [ Wiring It Into Laravel ](#wiring-it-into-laravel) 4. [ Encapsulating the Query in a Scope ](#encapsulating-the-query-in-a-scope) 5. [ Cycle Detection Beyond Depth Limiting ](#cycle-detection-beyond-depth-limiting) 6. [ Testing the Query ](#testing-the-query) 7. [ Key Takeaways ](#key-takeaways) ![PostgreSQL Recursive CTEs in Laravel: Hierarchical Data Without the ORM Gymnastics](https://cdn.msaied.com/720/3e3d256d54dd4036099122e0b035e482.png) #laravel #postgresql #eloquent #performance PostgreSQL Recursive CTEs in Laravel: Hierarchical Data Without the ORM Gymnastics ==================================================================================== 30 Sep 2026 2 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said Table of contents 1. [ 01 The Problem With Adjacency Lists at Scale ](#the-problem-with-adjacency-lists-at-scale) 2. [ 02 Anatomy of a Recursive CTE ](#anatomy-of-a-recursive-cte) 3. [ 03 Wiring It Into Laravel ](#wiring-it-into-laravel) 4. [ 04 Encapsulating the Query in a Scope ](#encapsulating-the-query-in-a-scope) 5. [ 05 Cycle Detection Beyond Depth Limiting ](#cycle-detection-beyond-depth-limiting) 6. [ 06 Testing the Query ](#testing-the-query) 7. [ 07 Key Takeaways ](#key-takeaways) The Problem With Adjacency Lists at Scale ----------------------------------------- Storing a tree as an adjacency list (`parent_id` column) is simple to write but painful to query. Fetching an entire subtree in pure Eloquent means either multiple round-trips or loading the whole table and filtering in PHP — neither is acceptable at scale. PostgreSQL's `WITH RECURSIVE` solves this at the database layer. Laravel's query builder doesn't have first-class support for it, but the raw expression API is expressive enough to make it clean. --- Anatomy of a Recursive CTE -------------------------- ```sql WITH RECURSIVE category_tree AS ( -- Anchor: start node SELECT id, parent_id, name, 1 AS depth FROM categories WHERE id = :root_id UNION ALL -- Recursive: join children SELECT c.id, c.parent_id, c.name, ct.depth + 1 FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id WHERE ct.depth < 10 -- cycle / depth guard ) SELECT * FROM category_tree ORDER BY depth, name; ``` The anchor term seeds the recursion; the recursive term joins back to the CTE itself. PostgreSQL iterates until no new rows are produced. --- Wiring It Into Laravel ---------------------- Laravel's `DB::select()` accepts raw SQL with named bindings, which is the simplest entry point: ```php $rows = DB::select( for($root, 'parent')->create(); $grandchild = Category::factory()->for($child, 'parent')->create(); $subtree = Category::subtree($root->id); expect($subtree->pluck('id')) ->toContain($root->id, $child->id, $grandchild->id); }); it('does not exceed the max depth guard', function () { // build a chain 15 levels deep $ids = []; $parent = Category::factory()->create(); foreach (range(1, 14) as $_) { $parent = Category::factory()->for($parent, 'parent')->create(); $ids[] = $parent->id; } $subtree = Category::subtree($ids[0], maxDepth: 5); expect($subtree)->toHaveCount(lessThanOrEqual(6)); }); ``` --- Key Takeaways ------------- - **`WITH RECURSIVE` beats multiple round-trips** for any tree or graph traversal. - **`DB::select()` + `Model::hydrate()`** gives you raw SQL power with Eloquent ergonomics. - **Always include a depth cap or cycle guard** — a corrupt `parent_id` will loop forever without one. - **Soft-delete filtering belongs in both the anchor and recursive terms**, or deleted ancestors will silently block subtree traversal. - **PostgreSQL 14+ array-based cycle detection** is more correct than depth limiting for variable-depth trees. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-recursive-ctes-in-laravel-hierarchical-data-without-the-orm-gymnastics&text=PostgreSQL+Recursive+CTEs+in+Laravel%3A+Hierarchical+Data+Without+the+ORM+Gymnastics) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-recursive-ctes-in-laravel-hierarchical-data-without-the-orm-gymnastics) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I use Laravel's query builder fluent API instead of raw SQL for recursive CTEs? Not natively. Laravel's query builder has no `withRecursive()` method. You can use `DB::statement()` or `DB::select()` with raw SQL, or reach for a package like `staudenmeir/laravel-cte` which adds a `withRecursiveExpression()` macro to the builder. Q02 Does Model::hydrate() trigger Eloquent events or observers? No. `hydrate()` calls `newFromBuilder()` internally, which bypasses the `creating`/`created` lifecycle entirely. It is purely a data-mapping operation, so observers and global scopes applied via query builder are not involved. Q03 How do I add eager-loaded relationships to a hydrated collection from a recursive CTE? Call `$models->load('relation')` after hydration. This fires a separate query scoped to the hydrated model IDs, giving you the same result as `with()` on a normal query. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![FrankenPHP, OPcache JIT, and Preloading: Maximising Laravel Throughput](https://cdn.msaied.com/719/2129727c31378ebd8778814d6764bac1.png) laravel frankenphp performance ### FrankenPHP, OPcache JIT, and Preloading: Maximising Laravel Throughput A practical deep-dive into running Laravel under FrankenPHP with OPcache JIT and preloading enabled — covering... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 30 Sep 2026 2 min read Read ](https://www.msaied.com/articles/frankenphp-opcache-jit-and-preloading-maximising-laravel-throughput-1) [ ![Laravel Release Cycle: Versions, Support Policy, and Dates](https://cdn.msaied.com/718/05d0e66985cc7bda5c0be6c37bb8da65.png) Laravel Release Cycle Support Policy ### Laravel Release Cycle: Versions, Support Policy, and Dates Laravel ships one major version per year in Q1, with weekly minor and patch releases in between. Here is every... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 29 Sep 2026 4 min read Read ](https://www.msaied.com/articles/laravel-release-cycle-versions-support-policy-and-dates) [ ![Laravel Queues in Production: Dead-Letter Patterns, Retry Strategies, and Observability](https://cdn.msaied.com/716/262173aca18154738c1257f3a77cd2fa.png) laravel queues production ### Laravel Queues in Production: Dead-Letter Patterns, Retry Strategies, and Observability Beyond basic queue configuration: how to design retry budgets, route failed jobs to dead-letter queues, emit s... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 29 Sep 2026 1 min read Read ](https://www.msaied.com/articles/laravel-queues-in-production-dead-letter-patterns-retry-strategies-and-observability) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://www.msaied.com) - [Projects](https://www.msaied.com/projects) - [Articles](https://www.msaied.com/articles) - [Certificates](https://www.msaied.com/certificates) - [Contact](https://www.msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)