PostgreSQL Recursive CTEs 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. PostgreSQL Recursive CTEs in Laravel: Hierarchical Data Without the ORM Gymnastics PostgreSQL Recursive CTEs in Laravel: Hierarchical Data Without the ORM Gymnastics =================================================================================== Recursive CTEs let PostgreSQL walk tree structures in a single query. Learn how to wire them into Laravel's query builder, map results to Eloquent models, and avoid the classic infinite-loop trap. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 30 Sep 2026 · Updated 30 Sep 2026 · 2 min read ShareCopy linkCopied ![PostgreSQL Recursive CTEs in Laravel: Hierarchical Data Without the ORM Gymnastics](https://cdn.msaied.com/720/3e3d256d54dd4036099122e0b035e482.png) 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) 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. - [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) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- 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. 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. 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. ![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 articleFrankenPHP, OPcache JIT, and Preloading: Maximising Laravel Throughput](https://www.msaied.com/public/articles/frankenphp-opcache-jit-and-preloading-maximising-laravel-throughput-1) [Next articleMigrating Pinkary from Laravel Forge to Laravel Cloud: An Engineering Playbook](https://www.msaied.com/public/articles/migrating-pinkary-from-laravel-forge-to-laravel-cloud-an-engineering-playbook) 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) ### 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)