README

A lightweight, high-performance Eloquent trait for self-referential (recursive) models.
It provides parent/children relations, O(1) hasParent(), EXISTS-based hasChildren(), depth calculation, ancestor/descendant utilities (CTE fast path + BFS fallback), and handy query scopes.

Features

• Relations: parent() and children() (self-referencing)
• Fast checks: hasParent() (O(1), no query), hasChildren() (single EXISTS)
• Depth: depth() with eager-load shortcut, cycle/missing-parent guards
• Ancestors/Descendants:
  • Fast path: single-query recursive CTE (MySQL 8+, PostgreSQL, SQLite)
  • Fallback: batched BFS traversal for engines without recursive CTE
  • Stream descendants as LazyCollection
• Scopes: roots() (no parent), leaves() (no children)
• Customizable: override FK/PK naming via constant or methods
• External SQL templates: editable CTE SQL in src/Sql/*.sql

Requirements

• PHP 8.0+ (tested up to PHP 8.3+)
• Laravel / Illuminate Database v10+ (works with 10 / 11 / 12)
• Database:
  • CTE fast path: MySQL 8+, PostgreSQL, SQLite
  • BFS fallback: works anywhere Eloquent runs (e.g., SQL Server)

The package ships SQL templates for CTE queries in src/Sql/descendant_ids_cte.sql and src/Sql/descendants_exist_cte.sql.

Installation

composer require tetthys/eloquent-hierarchy

If you are developing this repo locally (using the included Docker setup) and see Composer plugin prompts (e.g., for Pest), allow it explicitly:

composer config --no-plugins allow-plugins.pestphp/pest-plugin true

File Layout (key parts)

src/
  Concerns/
    HasHierarchy.php            # The trait
  Sql/
    descendant_ids_cte.sql      # CTE for collecting descendant IDs
    descendants_exist_cte.sql   # CTE for existence probe (LIMIT 1)

HasHierarchy loads the SQL templates at runtime and replaces placeholders like {{table}}, {{pk}}, {{parent_fk}}, and {{depth_limit}}.

Quick Start

1) Add the trait to your model

use Illuminate\Database\Eloquent\Model;
use Tetthys\EloquentHierarchy\Concerns\HasHierarchy;

class Category extends Model
{
    use HasHierarchy;

    // Optional: override the parent FK via a constant
    // public const HIERARCHY_PARENT_FOREIGN_KEY = 'parent_id';

    protected $fillable = ['name', 'parent_id'];
}

2) Migration (example)

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->unsignedBigInteger('parent_id')->nullable()->index();
    $table->timestamps();
});

3) Basic usage

$root = Category::create(['name' => 'Root']);
$child = Category::create(['name' => 'Child', 'parent_id' => $root->id]);

$child->hasParent();   // true (O(1), no query)
$root->hasChildren();  // true (EXISTS query)

$child->parent;        // BelongsTo relation (Category)
$root->children;       // HasMany relation (Collection<Category>)
$child->depth();       // 1

Ancestors & Depth

$leaf = Category::create(['name' => 'Leaf', 'parent_id' => $child->id]);

$leaf->depth();                // 2
$leaf->ancestors()->all();     // [Child, Root]
$leaf->ancestorIds()->all();   // [child_id, root_id]

$root->isAncestorOf($leaf);    // true
$leaf->isDescendantOf($root);  // true

// Limit how far to walk upward
$leaf->ancestors(1)->all();    // [Child]

Tip: If you eager load parent.parent..., depth() and ancestors() can walk in memory with 0 queries.

Descendants (CTE fast path + BFS fallback)

These APIs automatically use a recursive CTE when your driver supports it; otherwise they switch to a BFS strategy optimized to minimize data transfer.

$root->descendantsExist();        // true if any descendant exists
$root->descendantsExist(1);       // true if any direct child exists
$root->descendantsExist(2);       // true if child or grandchild exists

$ids = $root->descendantIds()->all();     // [child_id, grandchild_id, ...]
$ids = $root->descendantIds(1)->all();    // only depth=1

// Stream IDs level-by-level (no big arrays in memory)
foreach ($root->descendantIds() as $id) {
    // process each descendant id
}

// Materialize models (keeps level-order)
$models = $root->descendants(['id', 'name']);  // Collection<Category>

Query Scopes

Category::roots()->get();   // parent_id IS NULL
Category::leaves()->get();  // no children

Customization

Change the parent foreign key column (no method override needed)

class Category extends Model
{
    use HasHierarchy;

    public const HIERARCHY_PARENT_FOREIGN_KEY = 'parent_uuid';
}

Or override the methods directly

class Category extends Model
{
    use HasHierarchy;

    protected function parentForeignKeyName(): string
    {
        return 'parent_uuid';
    }

    protected function ownerKeyName(): string
    {
        return 'uuid';
    }
}

How it works (short version)

• Depth & Ancestors: walk up using eager-loaded parents when available; otherwise perform tiny per-hop lookups (SELECT pk, parent_fk).

• Descendants:

  • CTE: WITH RECURSIVE to expand the subtree in one query. SQL lives in src/Sql/*.sql and is loaded + templated at runtime.
  • BFS: level-order scan using whereIn(parent_fk, frontier) and pluck(pk) in batches to reduce memory and round-trips.

All traversals include cycle and missing-parent guards.

Performance Notes

• Prefer CTE-capable DBs (MySQL 8+, PostgreSQL, SQLite) to enable single-query descendant expansion.
• When falling back to BFS, tune the chunkSize argument of descendantIds($maxDepth, $chunkSize).
• Eager load parent.parent... for top-down depth/ancestor operations to avoid extra queries.

Testing locally (optional)

This repo includes a minimal Docker + Pest setup for local testing.

# build + install + run tests
bash ./run/test.sh

# pass flags through to Pest
bash ./run/test.sh -- --filter=Descendants

If Composer blocks a dev plugin (like Pest), you can allow it:

composer config --no-plugins allow-plugins.pestphp/pest-plugin true

FAQ

Q: Does this require a full Laravel app? A: No. It only needs Eloquent. Tests use Orchestra Testbench.

Q: Can I customize the SQL? A: Yes. Edit the files in src/Sql/. The trait replaces placeholders and binds parameters positionally.

Q: Do I need to call anything to choose CTE or BFS? A: No. The trait auto-detects driver support and picks the best path.

License

MIT