Si vous utilisez le Zend Framework et que vous cherchez à représenter une arborescence type Menu, Organigramme, ...
C'est juste une simple mise en forme de cet article concernant la représentation intervallaire d'une arborescence
Il me reste la gestion des niveaux à prendre en compte, histoire de faire des filtres supplémentaire. Mais bon, chaque chose en son temps.
Comme toujours, je suis tout ouïe aux remarques voire même aux critiques
Appel de la classe
<?php
class Kernel_Models_Data_Role_Table extends ND_Db_Table_Hierarchical_Table{
protected $_name = 'role';
protected $_rowClass = 'Kernel_Models_Data_Role_Row';
protected $_left = 'bg'; // defini la borne gauche (obligatoire)
protected $_right = 'bd'; // defini la borne droite (obligatoire)
protected $_level = 'level' // defini la profondeur de l'arborescence (obligatoire)
}
Et pour ceux qui ont la flemme de télécharger le fichier
<?php
/**
*
* @author ndesaleux
* @package ND
* @subpackage Db
*
*/
class ND_Db_Table_Hierarchical_Table extends Zend_Db_Table{
protected $_left = null ;
protected $_right = null ;
protected $_level = null ;
/**
*
* @param (array) $config
* @return null
*/
public function __construct($config = array()){
parent::__construct($config);
if (null == $this->_left) throw new ND_Db_Table_Hierarchical_Exception(ND_Db_Table_Hierarchical_Exception::NO_LEFT_BORDER_FIELD_DEFINED);
if (null == $this->_right) throw new ND_Db_Table_Hierarchical_Exception(ND_Db_Table_Hierarchical_Exception::NO_RIGHT_BORDER_FIELD_DEFINED);
if (null == $this->_level) throw new ND_Db_Table_Hierarchical_Exception(ND_Db_Table_Hierarchical_Exception::NO_LEVEL_FIELD_DEFINED);
}
/**
*
* @param (int) $id
* @return (mixed) false|Zend_Db_Table_Row_Abstract
*/
public function getById($id) {
$rows = $this->find($id);
if ($rows) {
return $rows->current();
}
return false;
}
/**
* Delete an element get by id (this delete also node and leaf)
* Update bor
* @param (int) $id
* @return (boolean)
*/
public function deleteById($id){
$elt = $this->getById($id) ;
if ($elt instanceof Zend_Db_Table_Row_Abstract ){
$left = $this->_left;
$right = $this->_right;
$dif = $elt->$right- $elt->$left + 1;
// on supprime les elements noeuds et feuilles
$dlElt = 'DELETE FROM '.$this->_name.'
WHERE '.$this->_left.' >= '.$elt->$left.'
AND '.$this->_right.' <= '.$elt->$right ;
// MAJ des bornes droites
$upRb = 'UPDATE '.$this->_name.'
SET '.$this->_right.' = '.$this->_right.' - '.$dif.'
WHERE '.$this->_right.' >= '.$elt->$left ;
// MAj des bornes gauches
$upLb = 'UPDATE '.$this->_name.'
SET '.$this->_left.' = '.$this->_left.' - '.$dif.'
WHERE '.$this->_left.' > '.$elt->$left ;
try{
$this->_db->beginTransaction();
$this->_db->prepare($dlElt)->execute();
$this->_db->prepare($upRb)->execute();
$this->_db->prepare($upLb)->execute();
$this->_db->commit();
return true;
}catch(Exception $e){
$this->_db->rollBack();
return false ;
}
}
}
/**
* Add a leaf at the node $id
*
* @param (array) $data
* @param (int) $id
* @return (mixed) false|int
*/
public function insertByParent(array $data, $id){
$elt = $this->getById($id);
if ($elt instanceof Zend_Db_Table_Row_Abstract ){
$right = $this->_right;
$level = $this->_level;
// MAJ des bornes droites
$upRb = 'UPDATE '.$this->_name.'
SET '.$this->_right.' = '.$this->_right.' + 2
WHERE '.$this->_right.' >= '.$elt->$right ;
// MAj des bornes gauches
$upLb = 'UPDATE '.$this->_name.'
SET '.$this->_left.' = '.$this->_left.' + 2
WHERE '.$this->_left.' >= '.$elt->$right ;
$inElt = 'INSERT INTO '.$this->_name.'
( '.$this->_left.', '.$this->_right.', '.$this->_level.' )
VALUES ( '.$elt->$right.', '.($elt->$right+1).', '.($elt->$level+1).' ) ';
try{
$this->_db->beginTransaction();
$this->_db->prepare($upRb)->execute();
$this->_db->prepare($upLb)->execute();
$this->_db->prepare($inElt)->execute();
$this->_db->commit();
$oElt = $this->fetchRow($this->_left.' = '.$elt->$right.' AND '.$this->_right.' = '.($elt->$right+1));
// clean data
foreach( $this->_primary as $primaryK ){
unset( $data[$primaryK] );
}
unset( $data[$this->_left] );
unset( $data[$this->_right] );
unset( $data[$this->_level] );
$oElt->setFromArray($data);
Zend_Debug::dump($data);
return $oElt->save();
}catch(Exception $e){
$this->_db->rollBack();
}
}
return false ;
}
/**
* Update a node or a leaf, left and right border can not be update
*
* @param (array) $data
* @param (int) $id
* @return boolean
*/
public function updateById(array $data, $id){
unset( $data[$this->_left] );
unset( $data[$this->_right] );
unset( $data[$this->_level] );
$elt = $this->getById($id);
if ( $elt instanceof Zend_Db_Table_Row_Abstract ){
try{
$elt->setFromArray($data);
$elt->save();
return true;
}catch(Zend_Db_Exception $e){
//@todo
}
}
return false;
}
/**
* Get node and leaf come from node identified by $id
* @param (int) $id
* @param (int) $level optional
* @return (mixed) array|false
*/
public function getChildren($id, $lvl = null){
$elt = $this->getById($id);
if ( $elt instanceof Zend_Db_Table_Row_Abstract ){
$left = $this->_left;
$right = $this->_right;
$select = $this->select()->from($this->_name)
->where($this->_left.' > ? ', $elt->$left )
->where($this->_right.' < ? ', $elt->$right );
if ( $lvl !== null && (int) $lvl > 0 ){
$level = $this->_level ;
$select->where($this->_level.' <= ? ', $elt->$level + (int) $lvl );
}
return $select->query()->fetchAll();
}else{
return false;
}
}
/**
* get parent of the element
* @param (int) $id
* @return (mixed) Zend_Db_Table_Row_Abstract|false
*/
public function getParent($id){
$elt = $this->getById($id);
if ( $elt instanceof Zend_Db_Table_Row_Abstract ){
$left = $this->_left ;
$level = $this->_level ;
$select = $this->select()->from($this->_name)
->where($this->_left.' < ?',$elt->$left )
->where($this->_level.' = ?',$elt->$level-1)
->order($this->_left.' DESC' )
->limit(1);
return $select->query()->fetchAll();
}else{
return false;
}
}
/**
* count children of an element
* @param $id
* @return (mixed) false|int
*/
public function countChildren($id){
$return = $this->getChildren($id) ;
return $return === false ? false : count($return) ;
}
/**
* return number of node of the element $id
* @param (int) $id
* @return (mixed) false|int
*/
public function countNode($id){
$oElt = $this->getById($id);
if ( $oElt instanceof Zend_Db_Table_Row_Abstract ){
$left = $this->_left ;
$right = $this->_right ;
$select = $this->select()
->from($this->_name, array('count(id) as nb') )
->where($this->_right.' - '.$this->_left.' <> 1 ')
->where($this->_left.' > ? ' , $oElt->$left )
->where($this->_right.' < ? ', $oElt->$right );
return $select->query()->fetchColumn(0);
}
return false ;
}
/**
*
* @param (int) $id
* @return (mixed) false|int
*/
public function countLeaf($id){
$oElt = $this->getById($id);
if ( $oElt instanceof Zend_Db_Table_Row_Abstract ){
$left = $this->_left ;
$right = $this->_right ;
$select = $this->select()
->from($this->_name, array('count(id) as nb') )
->where($this->_right.' - '.$this->_left.' = 1 ')
->where($this->_left.' > ? ' , $oElt->$left )
->where($this->_right.' < ? ', $oElt->$right );
return $select->query()->fetchColumn(0);
}
return false ;
}
/**
* Move a subtree (node or leaf are allowed to move)
*
* @param (int) $id
* @param (int) $newParentId
* @return (boolean)
*/
public function moveTree($id, $newParentId){
$oEltToMove = $this->getById($id);
$oEltToReceive = $this->getById($newParentId);
if ( $oEltToMove instanceof Zend_Db_Table_Row_Abstract &&
$oEltToReceive instanceof Zend_Db_Table_Row_Abstract ){
$left = $this->_left ;
$right = $this->_right ;
if ( $this->countChildren($id) >= 0 ){
$data = $oEltToMove->toArray();
$newId = $this->insertByParent($data, $newParentId);
$children = $this->getChildren($id, 1);
foreach( $children as $child ){
$this->moveTree($child['id'], $newId );
}
$this->deleteById($id);
}
}
return false;
}
}
NB: moveTree est une fonction récursive elle fonctionne de manière atomique mais il peut potentiellement avoir des données orphelines si la procédure est interrompue pour X raisons :'(. Il faut que je regardes comment je pourrais gérer ca de manière transactionnelle, histoire qu'une interruption ne cause aucun soucis.