Новая версия

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

TreeClosureTable

Хранение иерархических древовидных структур в базе данных методом «Closure Table» совмещённым с «Adjacency List».

Статья на Habrahabr

http://habrahabr.ru/post/263629/

Как использовать TreeClosureTable?

1. Создать две таблицы в базе данных.
2. Настроить параметры.

В каталоге example приведён пример использования TreeClosureTable.

Схемы таблиц comments and commentsTree:

CREATE TABLE `comments` (
  `idEntry` int(11) NOT NULL AUTO_INCREMENT,
  `idUser` int(11) NOT NULL DEFAULT '0',
  `content` text NOT NULL,
  `dateCreate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dateUpdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`idEntry`),
  KEY `idEntry` (`idEntry`,`idUser`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `commentsTree` (
  `idAncestor` int(11) NOT NULL,
  `idDescendant` int(11) NOT NULL,
  `idNearestAncestor` int(11) NOT NULL DEFAULT '0',
  `level` smallint(6) NOT NULL DEFAULT '1',
  `idSubject` int(11) NOT NULL,
  `dateCreate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idAncestor`,`idDescendant`),
  KEY `idDescendant` (`idDescendant`),
  KEY `idSubject` (`idSubject`),
  KEY `main` (`idAncestor`,`idDescendant`,`idNearestAncestor`,`level`),
  KEY `idNearestAncestor` (`idNearestAncestor`),
  CONSTRAINT `commentsTree_ibfk_1` 
  FOREIGN KEY (`idAncestor`) 
  REFERENCES `comments` (`idEntry`) 
  ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `commentsTree_ibfk_2` 
  FOREIGN KEY (`idDescendant`) 
  REFERENCES `comments` (`idEntry`) 
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Настройки параметров работы TreeClosureTable и получение объекта класса Commentator для работы с деревом:

$parameters = array(

    /**
     * It is the name of table in DB where stored comments
     */
    'tableData' => 'comments',

    /**
     * It is the name of table in db where stored hierarchic structure of tree
     */
    'tableTree' => 'commentsTree',

    /**
     * Is is name of class, that describes entity 'Comments'
     */
    'entity' => 'TreeClosureTable\Comments',

    /**
     * It is name of field which is ID of comment
     */
    'idTableData' => 'idEntry'
    
);
    
/**
* Object PDO
* $dsn, $user, $password - your parameter for connect DB
*/
$pdo = new PDO($dsn, $user, $password);
    
/**
* Object Commentator
*/    
$commentator = new \TreeClosureTable\Commentator($parameters, $pdo);

Получение всех комментарив, относящихся к $idSubject

    $comments = $commentator->setIdSubject($idSubject)->getTree();

Описание методов для работы с деревом

Check having element in tree

    public bool Commentator::hasEntry(int $idEntry);

Delete branch of tree

    public int Commentator::deleteBranch(int $idEntry);

Add one new element into tree

    public bool Commentator::add(ClosureTableData $obj, int $idEntry = 0);

Return part of tree or entire hierarchy from root as array

    public array Commentator::getArrayTree(int $idEntry = 0);

Return part of tree or entire hierarchy from root as object ClosureTableCollection

    public ClosureTableCollection Commentator::getDescendants(int $idEntry = 0);

Return part of properly constructed tree or entire properly constructed hierarchy from root as object ClosureTableCollection

    public ClosureTableCollection|null Commentator::getTree(int $idEntry = 0);

Return ID all elements of branch of tree

    public array Commentator::getIdEntriesBranch(int $idEntry);

Return part tree or entire tree as multidimensional array

    public array|null Commentator::getHierarchyArrayTree(int $idEntry = 0);

Return level of element

    public int Commentator::getLevel($idEntry);

Return count of elements in tree which belongs to $idSubject

    public int Commentator::countItemsBySubject(int $idSubject);

Author

[Igor Drandin] (https://github.com/drandin)

License

MIT Public License