franzose / Closuretable
Projects that are alternatives of or similar to Closuretable
ClosureTable
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:
- Two models in the
app
directory:App\Node
andApp\NodeClosure
- 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:
Option | Alias | Meaning
----------------| ------| -------
namespace | ns | Custom namespace for generated models. Keep in mind that the given namespace will override model namespaces: php artisan closuretable:make Foo\\Node --namespace=Qux --closure=Bar\\NodeTree
will generate Qux\Node
and Qux\NodeTree
models.
entity-table | et | Database table name for the entity model
closure | c | Class name for the closure model
closure-table | ct | Database table name for the closure model
models-path | mdl | Directory in which to put generated models
migrations-path | mgr | Directory in which to put generated migrations
use-innodb | i | This flag will tell the generator to set database engine to InnoDB. Useful only if you use MySQL
Requirements
You have to keep in mind that, by design of this package, the models/tables have a required minimum of attributes/columns:
Entity | ||
---|---|---|
Attribute/Column | Customized by | Meaning |
parent_id | Entity::getParentIdColumn() |
ID of the node's immediate parent, simplifies queries for immediate parent/child nodes. |
position | Entity::getPositionColumn() |
Node position, allows to order nodes of the same depth level |
ClosureTable | ||
Attribute/Column | Customized by | Meaning |
id | ||
ancestor | ClosureTable::getAncestorColumn() |
Parent (self, immediate, distant) node ID |
descendant | ClosureTable::getDescendantColumn() |
Child (self, immediate, distant) node ID |
depth | ClosureTable::getDepthColumn() |
Current nesting level, 0+ |
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();