Clever Geek Handbook

Hierarchical tree comment management system for Laravel

A package for the Laravel framework  that allows you to organize the management of tree-like comments. A symbiosis of two methods of storing hierarchical structures is used -  "Closure Table"  and  "Adjacency List" .

Requirements

  • Laravel 5.7 or newer framework .

  • PHP version is at least 7.3.

Repository

https://github.com/drandin/closure-table-comments

Benefits

The combined use of the Closure Table and Adjacency List methods allows:

  • Minimize the number of database queries. One request is enough to retrieve a comment thread.

  • Provide high performance. The read requests to the database are simple and the speed of selection of hierarchy nodes practically does not decrease with an increase in the amount of data.

  • Store comment text and hierarchical structure separately from each other, in two tables.

  • Control the depth of nesting of comments. The level of nodes in the hierarchy is always known; there is no need to obtain information about the ancestors of a node to determine it.

  • Ensure data integrity of the hierarchy. Adding new nodes to the hierarchy does not require any changes to the links of previously created nodes.

  • SQL-. , , .

. .

 «Adjacency List», , .

 «Path Enumeration»,  «Materialized Path» - SQL- SELECT, LIKE : '%/2/3/4%'. , .

— «Nested Sets». , . . «Nested Sets».

 «Closure Table»  , «» — .

«Closure Table» «Adjacency List» . 

 «Closure Table» «Adjacency List»:

Links of elements of the "Closure Table" tree combined with the "Adjacency List"
 «Closure Table» «Adjacency List»

 «Closure Table»  . 

«Adjacency List»  .

, , .

1.  Laravel  :

composer require drandin/closure-table-comments

2.  config/app.php -.  'providers'.

\Drandin\ClosureTableComments\ClosureTableServiceProvider::class,

3. ,  closure-table-comments.php   config  :

php artisan vendor:publish --tag=config

,  config/closure-table-comments.php  . , ,  .

4.  , :

php artisan config:cache

5. . , :

php artisan migrate

 2   .

, . 

, ,  «»  , .

 Comment  StructureTree.

subject_id —  «».  NULL. 

 subject_id  NULL, - .

user_id —  « ».  NULL. 

 user_id  NULL, - . .

1. . 

,  «»   5636   7  .

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());

$comment = " .    .";
    
$id = $commentator
        ->setSubjectId(5636)
        ->addCommentToRoot($comment, 7);

 $id,  5636.  7.

2. , . 

,  «»   5636   43  .

(  Node), . ,  1.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$comment = "   ,    .";
  
$id = $commentator
       ->setSubjectId(5636)
       ->replyToComment(1, $comment, 43);

 $id,  5636.  43.

, (  1). , (level) , .

3. . 

.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$comment = " .  .";
  
$res = $commentator->editComment(1, $comment);

,  $res   true.

4. . 

( ) .

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$res = $commentator->has(2);

2 ,  $res   true.

5. ( ) . 

,  Node  ,  2.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$node = $commentator->getNode(2);

,  2 ,  getNode   Node.  Node  .

6. .

 5636, . . , .

, . . . , , .

 getTreeBranch.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$nodes = $commentator
             ->setSubjectId(5636)
             ->getTreeBranch();

 Node.

,  getTreeBranch  .

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$nodes = $commentator
             ->setSubjectId(5636)
             ->getTreeBranch(2);

 2.

7. . 

 getTreeBranchArray.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$tree = $commentator
          ->setSubjectId(5636)
          ->getTreeBranchArray();

, ,  getTreeBranchArray .

8. . 

,  23.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$ids = $commentator->getBranchIds(23);

 $ids  .

9. . 

,  23  ,  Node  getNode  , . ,  getLevel.

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$level = $commentator->getLevel(23);

10. () . 

( ),  delete.

 delete  , , .

 64  , .

use Drandin\ClosureTableComments\ClosureTableService;
use Drandin\ClosureTableComments\Commentator;

$commentator = new Commentator(new ClosureTableService());
  
$res = $commentator->delete(64);

, .

«Closure Table» 

 «Closure Table»  , , . , . , , .

, , «Closure Table» , .

, . 

, , , , . 

, . , , , . 

, , , .

  1. «Closure Table» «Adjacency List» https://habr.com/ru/post/263629/. 2015 .

  2. «Stack Overflow». MySQL Closure Table hierarchical ? http://stackoverflow.com/questions/8252323/mysql-closure-table-hierarchical-database-how-to-pull-information-out-in-the-c,  ? http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree.

  3. (Bill Karwin). http://www.slideshare.net/billkarwin/models-for-hierarchical-data.

  4. Storing trees in the database. Part one, theoretical https://habr.com/ru/post/193166/ . year 2013.



More articles:


All Articles