Recursive CTEs in MySQL 8 with Laravel | Mohamed Said        [  ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png)   Mohamed Said Laravel Backend Engineer  ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles  ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) 

       [  ](https://github.com/EG-Mohamed)       

 [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) 

  [ home ](https://msaied.com)    [ articles ](https://msaied.com/articles)    Recursive Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists        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)

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

  #laravel   #mysql   #database   #eloquent   #performance  

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

     14 Jun 2026      2 min read    ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg)  Mohamed Said  

       Table of contents

1. [  01   Why Recursive CTEs Matter for Laravel Apps  ](#why-recursive-ctes-matter-for-laravel-apps)
2. [  02   The Schema  ](#the-schema)
3. [  03   Writing the Recursive CTE  ](#writing-the-recursive-cte)
4. [  04   Calling It from Laravel  ](#calling-it-from-laravel)
5. [  05   Hydrating Eloquent Models  ](#hydrating-eloquent-models)
6. [  06   Finding All Ancestors (Upward Walk)  ](#finding-all-ancestors-upward-walk)
7. [  07   Macro for Reuse  ](#macro-for-reuse)
8. [  08   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 =