Recursive CTEs in MySQL 8 with 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 Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists

 Recursive Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists
==================================================================================

 MySQL 8 supports recursive CTEs. Learn how to query adjacency-list hierarchies in Laravel without pulling the whole table into PHP, using raw expressions, query macros, and Eloquent integration.

 ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 14 Jun 2026 · Updated 14 Jun 2026 · 2 min read

ShareCopy linkCopied

 ![Recursive Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists](https://cdn.msaied.com/176/e761e34c4eeac562198091ba035908ca.png) 

  On this page +1. [Why Recursive CTEs Matter for Laravel Apps](#why-recursive-ctes-matter-for-laravel-apps)
2. [The Schema](#the-schema)
3. [Writing the Recursive CTE](#writing-the-recursive-cte)
4. [Calling It from Laravel](#calling-it-from-laravel)
5. [Hydrating Eloquent Models](#hydrating-eloquent-models)
6. [Finding All Ancestors (Upward Walk)](#finding-all-ancestors-upward-walk)
7. [Macro for Reuse](#macro-for-reuse)
8. [Key Takeaways](#key-takeaways)

 Why Recursive CTEs Matter for Laravel Apps
------------------------------------------

Adjacency-list tables — categories, org charts, threaded comments — are everywhere. The naive fix is loading every row and building the tree in PHP. At a few thousand rows that is fine; at hundreds of thousands it is a silent killer.

MySQL 8.0 shipped recursive common table expressions (CTEs). Laravel's query builder does not expose a dedicated `withRecursive()` method, but a small amount of raw SQL inside `DB::statement` or `fromRaw` gets you there cleanly.

---

The Schema
----------

```sql
CREATE TABLE categories (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    parent_id   BIGINT UNSIGNED NULL REFERENCES categories(id),
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) NOT NULL
);

CREATE INDEX idx_categories_parent ON categories (parent_id);

```

Every row points to its parent. Root nodes have `parent_id = NULL`.

---

Writing the Recursive CTE
-------------------------

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

```sql
WITH RECURSIVE category_tree AS (
    -- anchor: start at the chosen root
    SELECT id, parent_id, name, slug, 0 AS depth
    FROM   categories
    WHERE  id = ?

    UNION ALL

    -- recursive member: join children onto the previous level
    SELECT c.id, c.parent_id, c.name, c.slug, ct.depth + 1
    FROM   categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

```

MySQL enforces a default recursion depth of 1 000 (`cte_max_recursion_depth`). For pathological trees you can raise it per-session, but 1 000 levels is already a data-modelling problem.

---

Calling It from Laravel
-----------------------

The cleanest approach is `DB::select` with bound parameters:

```php
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

function categorySubtree(int $rootId): Collection
{
    $sql =  **Security note:** Never interpolate user input directly. Validate `$rootId` as an integer before embedding it, or use a prepared statement via `DB::select`.

---

Finding All Ancestors (Upward Walk)
-----------------------------------

Flip the join direction to walk *up* the tree — useful for breadcrumb generation:

```php
function categoryAncestors(int $leafId): Collection
{
    $sql =