laravel-adjacency-list: A PHP repository from burnz

Introduction

This Laravel Eloquent extension provides recursive relationships using common table expressions (CTE).

Supports Laravel 5.5.29+.

Compatibility

MySQL 8.0+
MariaDB 10.2+
PostgreSQL 9.4+
SQLite 3.8.3+
SQL Server 2008+

Installation

composer require staudenmeir/laravel-adjacency-list:"^1.0"

Use this command if you are in PowerShell on Windows (e.g. in VS Code):

composer require staudenmeir/laravel-adjacency-list:"^^^^1.0"

Getting Started

Consider the following table schema for hierarchical data:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('parent_id')->nullable();
});

Use the HasRecursiveRelationships trait in your model to work with recursive relationships:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
}

By default, the trait expects a parent key named parent_id. You can customize it by overriding getParentKeyName():

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
    
    public function getParentKeyName()
    {
        return 'parent_id';
    }
}

By default, the trait uses the model's primary key as the local key. You can customize it by overriding getLocalKeyName():

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
    
    public function getLocalKeyName()
    {
        return 'id';
    }
}

Included Relationships

The trait provides various relationships:

ancestors(): The model's recursive parents.
ancestorsAndSelf(): The model's recursive parents and itself.
bloodline(): The model's ancestors, descendants and itself.
children(): The model's direct children.
childrenAndSelf(): The model's direct children and itself.
descendants(): The model's recursive children.
descendantsAndSelf(): The model's recursive children and itself.
parent(): The model's direct parent.
parentAndSelf(): The model's direct parent and itself.
rootAncestor(): The model's topmost parent.
siblings(): The parent's other children.
siblingsAndSelf(): All the parent's children.

$ancestors = User::find($id)->ancestors;

$users = User::with('descendants')->get();

$users = User::whereHas('siblings', function ($query) {
    $query->where('name', '=', 'John');
})->get();

$total = User::find($id)->descendants()->count();

User::find($id)->descendants()->update(['active' => false]);

User::find($id)->siblings()->delete();

Trees

The trait provides the tree() query scope to get all models, beginning at the root(s):

$tree = User::tree()->get();

treeOf() allows you to query trees with custom constraints for the root model(s). Consider a table with multiple separate lists:

$constraint = function ($query) {
    $query->whereNull('parent_id')->where('list_id', 1);
};

$tree = User::treeOf($constraint)->get();

Filters

The trait provides query scopes to filter models by their position in the tree:

hasChildren(): Models with children.
hasParent(): Models with a parent.
isLeaf(): Models without children.
isRoot(): Models without a parent.

$noLeaves = User::hasChildren()->get();

$noRoots = User::hasParent()->get();

$leaves = User::isLeaf()->get();

$roots = User::isRoot()->get();

Order

The trait provides query scopes to order models breadth-first or depth-first:

breadthFirst(): Get siblings before children.
depthFirst(): Get children before siblings.

$tree = User::tree()->breadthFirst()->get();

$descendants = User::find($id)->descendants()->depthFirst()->get();

Depth

The results of ancestor, bloodline, descendant and tree queries include an additional depth column.

It contains the model's depth relative to the query's parent. The depth is positive for descendants and negative for ancestors:

$descendantsAndSelf = User::find($id)->descendantsAndSelf()->depthFirst()->get();

echo $descendantsAndSelf[0]->depth; // 0
echo $descendantsAndSelf[1]->depth; // 1
echo $descendantsAndSelf[2]->depth; // 2

You can customize the column name by overriding getDepthName():

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function getDepthName()
    {
        return 'depth';
    }
}

Depth Constraints

You can use the whereDepth() query scope to filter models by their relative depth:

$descendants = User::find($id)->descendants()->whereDepth(2)->get();

$descendants = User::find($id)->descendants()->whereDepth('<', 3)->get();

Queries with whereDepth() constraints that limit the maximum depth still build the entire (sub)tree internally. Both tree scopes allow you to provide a maximum depth that improves query performance by only building the requested section of the tree:

$tree = User::tree(3)->get();

$tree = User::treeOf($constraint, 3)->get();

Path

The results of ancestor, bloodline, descendant and tree queries include an additional path column.

It contains the dot-separated path of local keys from the query's parent to the model:

$descendantsAndSelf = User::find(1)->descendantsAndSelf()->depthFirst()->get();

echo $descendantsAndSelf[0]->path; // 1
echo $descendantsAndSelf[1]->path; // 1.2
echo $descendantsAndSelf[2]->path; // 1.2.3

You can customize the column name and the separator by overriding the respective methods:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function getPathName()
    {
        return 'path';
    }

    public function getPathSeparator()
    {
        return '.';
    }
}

Custom Paths

You can add custom path columns to the query results:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function getCustomPaths()
    {
        return [
            [
                'name' => 'slug_path',
                'column' => 'slug',
                'separator' => '/',
            ],
        ];
    }
}

$descendantsAndSelf = User::find(1)->descendantsAndSelf;

echo $descendantsAndSelf[0]->slug_path; // user-1
echo $descendantsAndSelf[1]->slug_path; // user-1/user-2
echo $descendantsAndSelf[2]->slug_path; // user-1/user-2/user-3

Nested Results

Use the toTree() method on the result collection to generate a nested tree:

$users = User::tree()->get();

$tree = $users->toTree();

This recursively sets children relationships:

[
  {
    "id": 1,
    "children": [
      {
        "id": 2,
        "children": [
          {
            "id": 3,
            "children": []
          }
        ]
      },
      {
        "id": 4,
        "children": [
          {
            "id": 5,
            "children": []
          }
        ]
      }
    ]
  }
]

Recursive Query Constraints

You can add custom constraints to the CTE's recursive query. Consider a query where you want to traverse a tree while skipping inactive users and their subtrees:

$tree = User::withRecursiveQueryConstraint(function (Builder $query) {
   $query->where('users.active', true);
}, function () {
   return User::tree()->get();
});

Custom Relationships

You can also define custom relationships to retrieve related models recursively.

HasManyOfDescendants

Consider a HasMany relationship between User and Post:

class User extends Model
{
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

Define a HasManyOfDescendants relationship to get all posts of a user and its descendants:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function recursivePosts()
    {
        return $this->hasManyOfDescendantsAndSelf(Post::class);
    }
}

$recursivePosts = User::find($id)->recursivePosts;

$users = User::withCount('recursivePosts')->get();

Use hasManyOfDescendants() to only get the descendants' posts:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function descendantPosts()
    {
        return $this->hasManyOfDescendants(Post::class);
    }
}

BelongsToManyOfDescendants

Consider a BelongsToMany relationship between User and Role:

class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }
}

Define a BelongsToManyOfDescendants relationship to get all roles of a user and its descendants:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function recursiveRoles()
    {
        return $this->belongsToManyOfDescendantsAndSelf(Role::class);
    }
}

$recursiveRoles = User::find($id)->recursiveRoles;

$users = User::withCount('recursiveRoles')->get();

Use belongsToManyOfDescendants() to only get the descendants' roles:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function descendantRoles()
    {
        return $this->belongsToManyOfDescendants(Role::class);
    }
}

MorphToManyOfDescendants

Consider a MorphToMany relationship between User and Tag:

class User extends Model
{
    public function tags()
    {
        return $this->morphToMany(Tag::class, 'taggable');
    }
}

Define a MorphToManyOfDescendants relationship to get all tags of a user and its descendants:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function recursiveTags()
    {
        return $this->morphToManyOfDescendantsAndSelf(Tag::class, 'taggable');
    }
}

$recursiveTags = User::find($id)->recursiveTags;

$users = User::withCount('recursiveTags')->get();

Use morphToManyOfDescendants() to only get the descendants' tags:

class User extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function descendantTags()
    {
        return $this->morphToManyOfDescendants(Tag::class, 'taggable');
    }
}

MorphedByManyOfDescendants

Consider a MorphedByMany relationship between Category and Post:

class Category extends Model
{
    public function posts()
    {
        return $this->morphedByMany(Post::class, 'categorizable');
    }
}

Define a MorphedByManyOfDescendants relationship to get all posts of a category and its descendants:

class Category extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function recursivePosts()
    {
        return $this->morphedByManyOfDescendantsAndSelf(Post::class, 'categorizable');
    }
}

$recursivePosts = Category::find($id)->recursivePosts;

$categories = Category::withCount('recursivePosts')->get();

Use morphedByManyOfDescendants() to only get the descendants' posts:

class Category extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function descendantPosts()
    {
        return $this->morphedByManyOfDescendants(Post::class, 'categorizable');
    }
}

Intermediate Scopes

You can adjust the descendants query (e.g. child users) by adding or removing intermediate scopes:

User::find($id)->recursivePosts()->withTrashedDescendants()->get();

User::find($id)->recursivePosts()->withIntermediateScope('active', new ActiveScope())->get();

User::find($id)->recursivePosts()->withoutIntermediateScope('active')->get();

Package Conflicts

staudenmeir/eloquent-eager-limit: Replace both packages with staudenmeir/eloquent-eager-limit-x-laravel-adjacency-list to use them on the same model.
staudenmeir/eloquent-param-limit-fix: Replace both packages with staudenmeir/eloquent-param-limit-fix-x-laravel-adjacency-list to use them on the same model.

Usage outside of Laravel

If you are using the package outside of Laravel or have disabled package discovery for staudenmeir/laravel-cte, you need to add support for common table expressions to the related model:

class Post extends Model
{
    use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
}

Contributing

Please see CONTRIBUTING and CODE OF CONDUCT for details.

burnz/laravel-adjacency-list