nagi/closure-table

Adjacency List’ed Closure Table database design pattern implementation for Laravel

dev-master 2024-06-12 08:32 UTC

Requires

• php: >=7.0
• illuminate/contracts: ^6.0|^7.0|^8.0|^9.0|^10.0|^11.0
• illuminate/database: ^6.0|^7.0|^8.0|^9.0|^10.0|^11.0
• illuminate/support: ^6.0|^7.0|^8.0|^9.0|^10.0|^11.0

Requires (Dev)

• orchestra/testbench: ^3.4|^4.0|^5.0|^6.0
• phpunit/phpunit: ^6.0|^7.0|^8.0|^9.0

MIT

• Jan Iwanow
• Nagi

• database
• categories
• laravel
• pages
• hierarchy
• closure table
• adjacency list

README

This is a database manipulation package for the Laravel 5.4+ framework. You may want to use it when you need to store and operate hierarchical data in your database. The package is an implementation of a well-known design pattern called closure table. However, in order to simplify and optimize SQL SELECT queries, it uses adjacency lists to query direct parent/child relationships.

Contents:

• Installation
• Setup
• Requirements
• Examples → List of Scopes
• Examples → Parent/Root
• Examples → Ancestors
• Examples → Descendants
• Examples → Children
• Examples → Siblings
• Examples → Tree
• Examples → Collection Methods

Installation

It's strongly recommended to use Composer to install the package:

$ composer require franzose/closure-table

If you use Laravel 5.5+, the package's service provider is automatically registered for you thanks to the package auto-discovery feature. Otherwise, you have to manually add it to your config/app.php:

<?php

return [
    'providers' => [
        Franzose\ClosureTable\ClosureTableServiceProvider::class
    ]
];

Setup

In a basic scenario, you can simply run the following command:

$ php artisan closuretable:make Node

Where Node is the name of the entity model. This is what you get from running the above:

1. Two models in the app directory: App\Node and App\NodeClosure
   
2. A new migration in the database/migrations directory

As you can see, the command requires a single argument, name of the entity model. However, it accepts several options in order to provide some sort of customization:

Requirements

You have to keep in mind that, by design of this package, the models/tables have a required minimum of attributes/columns:

Examples

In the examples, let's assume that we've set up a Node model which extends the Franzose\ClosureTable\Models\Entity model.

Scopes

Since ClosureTable 6, a lot of query scopes have become available in the Entity model:

ancestors()
ancestorsOf($id)
ancestorsWithSelf()
ancestorsWithSelfOf($id)
descendants()
descendantsOf($id)
descendantsWithSelf()
descendantsWithSelfOf($id)
childNode()
childNodeOf($id)
childAt(int $position)
childOf($id, int $position)
firstChild()
firstChildOf($id)
lastChild()
lastChildOf($id)
childrenRange(int $from, int $to = null)
childrenRangeOf($id, int $from, int $to = null)
sibling()
siblingOf($id)
siblings()
siblingsOf($id)
neighbors()
neighborsOf($id)
siblingAt(int $position)
siblingOfAt($id, int $position)
firstSibling()
firstSiblingOf($id)
lastSibling()
lastSiblingOf($id)
prevSibling()
prevSiblingOf($id)
prevSiblings()
prevSiblingsOf($id)
nextSibling()
nextSiblingOf($id)
nextSiblings()
nextSiblingsOf($id)
siblingsRange(int $from, int $to = null)
siblingsRangeOf($id, int $from, int $to = null)

You can learn how to use query scopes from the Laravel documentation.

Parent/Root

<?php
$nodes = [
    new Node(['id' => 1]),
    new Node(['id' => 2]),
    new Node(['id' => 3]),
    new Node(['id' => 4, 'parent_id' => 1])
];

foreach ($nodes as $node) {
    $node->save();
}

Node::getRoots()->pluck('id')->toArray(); // [1, 2, 3]
Node::find(1)->isRoot(); // true
Node::find(1)->isParent(); // true
Node::find(4)->isRoot(); // false
Node::find(4)->isParent(); // false

// make node 4 a root at the fourth position (1 => 0, 2 => 1, 3 => 2, 4 => 3)
$node = Node::find(4)->makeRoot(3);
$node->isRoot(); // true
$node->position; // 3

Node::find(4)->moveTo(0, Node::find(2)); // same as Node::find(4)->moveTo(0, 2);
Node::find(2)->getChildren()->pluck('id')->toArray(); // [4]

Ancestors

<?php
$nodes = [
    new Node(['id' => 1]),
    new Node(['id' => 2, 'parent_id' => 1]),
    new Node(['id' => 3, 'parent_id' => 2]),
    new Node(['id' => 4, 'parent_id' => 3])
];

foreach ($nodes as $node) {
    $node->save();
}

Node::find(4)->getAncestors()->pluck('id')->toArray(); // [1, 2, 3]
Node::find(4)->countAncestors(); // 3
Node::find(4)->hasAncestors(); // true
Node::find(4)->ancestors()->where('id', '>', 1)->get()->pluck('id')->toArray(); // [2, 3];
Node::find(4)->ancestorsWithSelf()->where('id', '>', 1)->get()->pluck('id')->toArray(); // [2, 3, 4];
Node::ancestorsOf(4)->where('id', '>', 1)->get()->pluck('id')->toArray(); // [2, 3];
Node::ancestorsWithSelfOf(4)->where('id', '>', 1)->get()->pluck('id')->toArray(); // [2, 3, 4];

There are several methods that have been deprecated since ClosureTable 6:

-Node::find(4)->getAncestorsTree();
+Node::find(4)->getAncestors()->toTree();

-Node::find(4)->getAncestorsWhere('id', '>', 1);
+Node::find(4)->ancestors()->where('id', '>', 1)->get();

Descendants

<?php
$nodes = [
    new Node(['id' => 1]),
    new Node(['id' => 2, 'parent_id' => 1]),
    new Node(['id' => 3, 'parent_id' => 2]),
    new Node(['id' => 4, 'parent_id' => 3])
];

foreach ($nodes as $node) {
    $node->save();
}

Node::find(1)->getDescendants()->pluck('id')->toArray(); // [2, 3, 4]
Node::find(1)->countDescendants(); // 3
Node::find(1)->hasDescendants(); // true
Node::find(1)->descendants()->where('id', '<', 4)->get()->pluck('id')->toArray(); // [2, 3];
Node::find(1)->descendantsWithSelf()->where('id', '<', 4)->get()->pluck('id')->toArray(); // [1, 2, 3];
Node::descendantsOf(1)->where('id', '<', 4)->get()->pluck('id')->toArray(); // [2, 3];
Node::descendantsWithSelfOf(1)->where('id', '<', 4)->get()->pluck('id')->toArray(); // [1, 2, 3];

There are several methods that have been deprecated since ClosureTable 6:

-Node::find(4)->getDescendantsTree();
+Node::find(4)->getDescendants()->toTree();

-Node::find(4)->getDescendantsWhere('foo', '=', 'bar');
+Node::find(4)->descendants()->where('foo', '=', 'bar')->get();

Children

<?php
$nodes = [
    new Node(['id' => 1]),
    new Node(['id' => 2, 'parent_id' => 1]),
    new Node(['id' => 3, 'parent_id' => 1]),
    new Node(['id' => 4, 'parent_id' => 1]),
    new Node(['id' => 5, 'parent_id' => 1]),
    new Node(['id' => 6, 'parent_id' => 2]),
    new Node(['id' => 7, 'parent_id' => 3])
];

foreach ($nodes as $node) {
    $node->save();
}

Node::find(1)->getChildren()->pluck('id')->toArray(); // [2, 3, 4, 5]
Node::find(1)->countChildren(); // 3
Node::find(1)->hasChildren(); // true

// get child at the second position (positions start from zero)
Node::find(1)->getChildAt(1)->id; // 3

Node::find(1)->getChildrenRange(1)->pluck('id')->toArray(); // [3, 4, 5]
Node::find(1)->getChildrenRange(0, 2)->pluck('id')->toArray(); // [2, 3, 4]

Node::find(1)->getFirstChild()->id; // 2
Node::find(1)->getLastChild()->id; // 5

Node::find(6)->countChildren(); // 0
Node::find(6)->hasChildren(); // false

Node::find(6)->addChild(new Node(['id' => 7]));

Node::find(1)->addChildren([new Node(['id' => 8]), new Node(['id' => 9])], 2);
Node::find(1)->getChildren()->pluck('position', 'id')->toArray(); // [2 => 0, 3 => 1, 8 => 2, 9 => 3, 4 => 4, 5 => 5]

// remove child by its position
Node::find(1)->removeChild(2);
Node::find(1)->getChildren()->pluck('position', 'id')->toArray(); // [2 => 0, 3 => 1, 9 => 2, 4 => 3, 5 => 4]

Node::find(1)->removeChildren(2, 4);
Node::find(1)->getChildren()->pluck('position', 'id')->toArray(); // [2 => 0, 3 => 1]

Siblings

<?php
$nodes = [
    new Node(['id' => 1]),
    new Node(['id' => 2, 'parent_id' => 1]),
    new Node(['id' => 3, 'parent_id' => 1]),
    new Node(['id' => 4, 'parent_id' => 1]),
    new Node(['id' => 5, 'parent_id' => 1]),
    new Node(['id' => 6, 'parent_id' => 1]),
    new Node(['id' => 7, 'parent_id' => 1])
];

foreach ($nodes as $node) {
    $node->save();
}

Node::find(7)->getFirstSibling()->id; // 2
Node::find(7)->getSiblingAt(0); // 2
Node::find(2)->getLastSibling(); // 7
Node::find(7)->getPrevSibling()->id; // 6
Node::find(7)->getPrevSiblings()->pluck('id')->toArray(); // [2, 3, 4, 5, 6]
Node::find(7)->countPrevSiblings(); // 5
Node::find(7)->hasPrevSiblings(); // true

Node::find(2)->getNextSibling()->id; // 3
Node::find(2)->getNextSiblings()->pluck('id')->toArray(); // [3, 4, 5, 6, 7]
Node::find(2)->countNextSiblings(); // 5
Node::find(2)->hasNextSiblings(); // true

Node::find(3)->getSiblings()->pluck('id')->toArray(); // [2, 4, 5, 6, 7]
Node::find(3)->getNeighbors()->pluck('id')->toArray(); // [2, 4]
Node::find(3)->countSiblings(); // 5
Node::find(3)->hasSiblings(); // true

Node::find(2)->getSiblingsRange(2)->pluck('id')->toArray(); // [4, 5, 6, 7]
Node::find(2)->getSiblingsRange(2, 4)->pluck('id')->toArray(); // [4, 5, 6]

Node::find(4)->addSibling(new Node(['id' => 8]));
Node::find(4)->getNextSiblings()->pluck('id')->toArray(); // [5, 6, 7, 8]

Node::find(4)->addSibling(new Node(['id' => 9]), 1);
Node::find(1)->getChildren()->pluck('position', 'id')->toArray();
// [2 => 0, 9 => 1, 3 => 2, 4 => 3, 5 => 4, 6 => 5, 7 => 6, 8 => 7]

Node::find(8)->addSiblings([new Node(['id' => 10]), new Node(['id' => 11])]);
Node::find(1)->getChildren()->pluck('position', 'id')->toArray();
// [2 => 0, 9 => 1, 3 => 2, 4 => 3, 5 => 4, 6 => 5, 7 => 6, 8 => 7, 10 => 8, 11 => 9]

Node::find(2)->addSiblings([new Node(['id' => 12]), new Node(['id' => 13])], 3);
Node::find(1)->getChildren()->pluck('position', 'id')->toArray();
// [2 => 0, 9 => 1, 3 => 2, 12 => 3, 13 => 4, 4 => 5, 5 => 6, 6 => 7, 7 => 8, 8 => 9, 10 => 10, 11 => 11]

Tree

<?php
Node::createFromArray([
    'id' => 1,
    'children' => [
        [
            'id' => 2,
            'children' => [
                [
                    'id' => 3,
                    'children' => [
                        [
                            'id' => 4,
                            'children' => [
                                [
                                    'id' => 5,
                                    'children' => [
                                        [
                                            'id' => 6,
                                        ]
                                    ]
                                ]
                            ]
                        ]
                    ]
                ]
            ]
        ]
    ]
]);

Node::find(4)->deleteSubtree();
Node::find(1)->getDescendants()->pluck('id')->toArray(); // [2, 3, 4]

Node::find(4)->deleteSubtree(true);
Node::find(1)->getDescendants()->pluck('id')->toArray(); // [2, 3]

There are several methods that have been deprecated since ClosureTable 6:

-Node::getTree();
-Node::getTreeByQuery(...);
-Node::getTreeWhere('foo', '=', 'bar');
+Node::where('foo', '=', 'bar')->get()->toTree();

Collection methods

This library uses an extended collection class which offers some convenient methods:

<?php
Node::createFromArray([
    'id' => 1,
    'children' => [
        ['id' => 2],
        ['id' => 3],
        ['id' => 4],
        ['id' => 5],
        [
            'id' => 6,
            'children' => [
                ['id' => 7],
                ['id' => 8],
            ]
        ],
    ]
]);

/** @var Franzose\ClosureTable\Extensions\Collection $children */
$children = Node::find(1)->getChildren();
$children->getChildAt(1)->id; // 3
$children->getFirstChild()->id; // 2
$children->getLastChild()->id; // 6
$children->getRange(1)->pluck('id')->toArray(); // [3, 4, 5, 6]
$children->getRange(1, 3)->pluck('id')->toArray(); // [3, 4, 5]
$children->getNeighbors(2)->pluck('id')->toArray(); // [3, 5]
$children->getPrevSiblings(2)->pluck('id')->toArray(); // [2, 3]
$children->getNextSiblings(2)->pluck('id')->toArray(); // [5, 6]
$children->getChildrenOf(4)->pluck('id')->toArray(); // [7, 8]
$children->hasChildren(4); // true
$tree = $children->toTree();