What is Nested set model?

The nested set model is a technique for representing nested set collections (also known as trees or hierarchies) in relational databases (as Wikipedia).

Above image is a collection with multiple level nodes, "Devices" node parent has multiple node children which are "Phone", "Laptop", and each node child has multiple children of children... With a node will have right and left values. Yeah, this is nested set model!

Why should we use Nested set model?

As usually, we use recursion technique to get each levels. Ex: with above devices list, to get all children of "Devices" node, we must recursively loop each node to find its children, let count total of queries:

1. Find children of "Devices" ("Phone", "Laptop").
2. Find children of "Phone" ("Apple", "SamSung").
3. Find children of "Apple" ("Iphone", "Iphone Pro Max").
4. Find children of "SamSung" ("Note").
5. Find children of "Note".
6. Find children of "Laptop" .

Six queries to get children of Devices, it's seem not a big problem? But we imagine increase number of node levels up to 100 1000 or 10.000,... I can't count query total we need :D.

Okay, we try change to use nested set model, that big problem will be solved by this technique. Let count queries again:

1. Find all node children have Left > 1 and Right < 22.
2. End. Don't need step 2 :D

We only use ONE query to solve it, instead of too many queries with recursion. 1 is Left value and 22 is Right value of "Devices" node, nested set model will use the pair to calculate everything by algorithm.

How to use:

Extend Minh164\EloNest\NestableModel class for the model we need apply nested set model:

Next, you make a migration to create some necessary columns for table, add these lines into new created migration file:

$table->bigInteger('lft')->index();
$table->bigInteger('rgt')->index();
$table->unsignedMediumInteger('depth')->default(0);
$table->unsignedMediumInteger('parent_id')->default(0)->index();
$table->unsignedMediumInteger('original_number')->comment("Identifier number to determine folders belong together")->index();

CRUD methods:

Likes base Eloquent Model has Eloquent Builder class, NestableModel also has NodeBuilder class, we will use some methods in this class to implement CRUD logic.

---

Create node:

We use createNode method to create new node, this method receives two parameters:

1. data: array info we need for new node.
2. parentId: Node parent ID which new node will be child. If null, new node will be a new root node.

---

Delete node:

We use deleteNodes method:

---

Move node:

You use moveNode method to change position of node.

WARNING: this method has just only update one record, I will update for multiple records later.

Method receives two parameters:

1. previous: value at left side of position will be need move
2. next: value at right side of position will be need move

NOTICE: previous and next values IS NOT left and right values of node, we should view this image:

If we want move "Apple" to before "HP", then previous and next values are passed to moveNode: 14 and 15.

---

Get nodes:

Instead of using ->get() method to get list, we will use ->getNodes() method to return NestedCollection object, this will help you process complicated nested logic:

WARNING: for get one record (same as ->first()) will be updated later.

Likely with method to get relations, we have withNodes to get relations for ALL nested nodes, and also handle by Eager Load!

In NestableModel has implemented some relations for your Model use it:

We can use both with and withNodes, but with only get relations for parent nodes not for nested children:

And yeah! we also can create a custom node relation by extends and override some methods in abstract Minh164\EloNest\Relations\NodeRelation class:

Updating.....