mysql 实现左右值无限分类
本帖最后由 xutwt@qq.com 于 2013-12-21 08:49 编辑product_relation数据表
id intprimary key 标识lft int index 左值rgt int inxex 右值title char(50) 名称product_id int type eunm product,class
layer，产品深度
给定词条id获取树子节点delimiter $$DROP PROCEDURE IF EXISTS GetTreeListByNode $$# MySQL 返回的查询结果为空 (即零行)。
CREATE PROCEDURE GetTreeListByNode(in var_id int)begindeclare var_lft int DEFAULT 0;declare var_rgt int DEFAULT 0;declare var_item int DEFAULT 0;select count(*) into var_item from product_relation where id=var_id;if(var_item) thenselectlft,rgt into var_lft,var_rgtfromproduct_relationwhere id=var_id;select * fromproduct_relationwhere lft between var_lft and var_rgt order by lft asc;
end if;end $$# MySQL 返回的查询结果为空 (即零行)。
添加产品关系节点delimiter $$DROP PROCEDURE IF EXISTS AddSubNodeByNode $$# MySQL 返回的查询结果为空 (即零行)。# MySQL 返回的查询结果为空 (即零行)。
CREATE PROCEDURE AddSubNodeByNode(in var_id int,in var_title char(50),in var_type char(50),var_product_id int)
begin
declare var_rgt int DEFAULT 0;declare var_item int DEFAULT 0;declare var_layer int DEFAULT 0;
select count(*) into var_item from product_relation where id=var_id;if(var_item) thenSTART TRANSACTION;select rgt ,layer into var_rgt ,var_layer from product_relation where id=var_id;update product_relation set rgt=rgt+2 where rgt>=var_rgt;update product_relation set lft=lft+2 where lft>=var_rgt;insert into product_relation (title,lft,rgt,type,layer,product_id) values (var_title,var_rgt,var_rgt+1,var_type,var_layer+1,var_product_id) ;COMMIT;end if;end删除产品关系节点(全部)
delimiter $$DROP PROCEDURE IF EXISTS DelNode $$# MySQL 返回的查询结果为空 (即零行)。
CREATE PROCEDURE DelNode(in var_id int)begindeclare var_lft int DEFAULT 0;declare var_rgt int DEFAULT 0;declare var_item int DEFAULT 0;select count(*) into var_item from product_relation where id=var_id;if(var_item) thenSTART TRANSACTION; select lft,rgt into var_lft,var_rgt from product_relation where id=var_id;delete from product_relation where lft>=var_lft and rgt<=var_rgt;update product_relation set lft=lft-(var_rgt-var_lft+1) where lft>var_lft;update product_relation set rgt=rgt-(var_rgt-var_lft+1) where rgt>var_rgt;COMMIT;end if;end# MySQL 返回的查询结果为空 (即零行)。
删除单一节点，子节点上移delimiter $$DROP PROCEDURE IF EXISTS DelOneNode $$# MySQL 返回的查询结果为空 (即零行)。
CREATE PROCEDURE DelOneNode(in var_id int)begindeclare var_lft int DEFAULT 0;declare var_rgt int DEFAULT 0;declare var_layer int DEFAULT 0;declare var_item int DEFAULT 0;select count(*) into var_item from product_relation where id=var_id;if(var_item) thenSTART TRANSACTION; select lft,rgt,layer into var_lft,var_rgt ,var_layer from product_relation where id=var_id;delete from product_relation where id=var_id;update product_relation set lft=lft-1,rgt=rgt-1,layer=layer-1 where lft>var_lft and rgt<var_rgt;update product_relation set lft=lft-2 where lft>var_rgt;update product_relation set rgt=rgt-2 where rgt>var_rgt;
COMMIT;end if;end# MySQL 返回的查询结果为空 (即零行)。
同层节点上移delimiter $$DROP PROCEDURE IF EXISTS MoveNodeUp $$CREATE PROCEDURE MoveNodeUp(in var_id int)begin declare var_lft int DEFAULT 0;declare var_rgt int DEFAULT 0;declare var_layer int DEFAULT 0;declare var_item int DEFAULT 0;declare var_brotherItem int DEFAULT 0;declare var_brother_lft int DEFAULT 0;declare var_brother_rgt int DEFAULT 0;select count(*) into var_item from product_relation where id=var_id;if(var_item) thenSTART TRANSACTION; select lft,rgt,layer into var_lft,var_rgt,var_layer fromproduct_relation where id=var_id;select count(*) into var_brotherItem from product_relation where rgt=var_lft-1 and layer=var_layer;if var_brotherItem thenselect lft,rgt into var_brother_lft, var_brother_rgt from product_relation where rgt=var_lft-1 and layer=var_layer;
update product_relation set lft=lft-(var_brother_rgt-var_brother_lft+1),rgt=rgt-(var_brother_rgt-var_brother_lft+1) where lft>=var_lft and rgt<=var_rgt;
update product_relation set lft=lft+(var_rgt-var_lft+1),rgt=rgt+(var_rgt-var_lft+1) where lft>=var_brother_lft and rgt<=var_brother_rgt;
end if;COMMIT;end if;end同层节点下移
delimiter $$DROP PROCEDURE IF EXISTS MoveNodeDown $$CREATE PROCEDURE MoveNodeDown(in var_id int)begin declare var_lft int DEFAULT 0;declare var_rgt int DEFAULT 0;declare var_layer int DEFAULT 0;declare var_item int DEFAULT 0;declare var_brotherItem int DEFAULT 0;declare var_brother_lft int DEFAULT 0;declare var_brother_rgt int DEFAULT 0;declare done int default 0;declare childId int default 0;declare cur1 CURSOR FOR (select id from product_relation where lft >= var_lft and rgt <=var_rgt);declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
select count(*) into var_item from product_relation where id=var_id;if(var_item) thenSTART TRANSACTION; select lft,rgt,layer into var_lft,var_rgt,var_layer from product_relation where id=var_id;open cur1;select count(*) into var_brotherItem from product_relation where lft=var_rgt+1 and layer=var_layer;if var_brotherItem thenselect lft,rgt into var_brother_lft, var_brother_rgt from product_relation where lft=var_rgt+1 and layer=var_layer;
update product_relation set lft=lft-(var_rgt-var_lft+1),rgt=rgt-(var_rgt-var_lft+1) where lft>=var_brother_lft and rgt<=var_brother_rgt;
Repeatfetch cur1 into childId;if not done then update product_relation set lft=lft+(var_brother_rgt-var_brother_lft+1),rgt=rgt+(var_brother_rgt-var_brother_lft+1) where id=childId;end if;until done end repeat;close cur1;
end if;COMMIT;end if;end
修改节点信息delimiter $$DROP PROCEDURE IF EXISTS UpdateNode $$CREATE PROCEDURE UpdateNode(in var_id int,in parent_id int,in var_title char(50))top:begindeclare var_lft int DEFAULT 0;declare var_rgt int DEFAULT 0;declare var_layer int DEFAULT 0;declare var_item int DEFAULT 0;declare var_parent_item int DEFAULT 0;declare var_oldparent_item int DEFAULT 0;declare var_oldparent_id int DEFAULT 0;declare var_parent_lft int DEFAULT 0;declare var_parent_rgt int DEFAULT 0;declare var_parent_layer int DEFAULT 0;declare var_betweenval int DEFAULT 0;declare var_val int DEFAULT 0;declare done int default 0;declare childId int default 0;declare cur1 CURSOR FOR (select id from product_relation where lft >= var_lft and rgt <=var_rgt);declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; select count(*) into var_item from product_relation where id=var_id;if(var_item) thenselect lft,rgt,layer into var_lft,var_rgt,var_layer from product_relation where id=var_id;open cur1;select count(*) into var_oldparent_item from product_relation where layer=var_layer-1 and lft<var_lft and rgt>var_rgt;if(var_oldparent_item) thenselect id into var_oldparent_id from product_relation where layer=var_layer-1 and lft<var_lft and rgt>var_rgt;if(var_oldparent_id=parent_id) then #没有移动节点update product_relation set title=var_title where id=var_id;#修改基本信息leave top;#跳出来end if;end if; select count(*) into var_parent_item from product_relation where id=parent_id;if(var_parent_item)thenset var_betweenval=var_rgt-var_lft;select lft,rgt,layer into var_parent_lft ,var_parent_rgt ,var_parent_layer from product_relation where id=parent_id;if(var_parent_rgt > var_rgt) then #右移START TRANSACTION; update product_relation set title=var_title where id=var_id;#修改基本信息update product_relation set lft=lft - var_betweenval - 1 where lft>var_rgt and lft < var_parent_rgt;update product_relation set rgt=rgt-var_betweenval-1 where rgt>var_rgt and rgt < var_parent_rgt;set var_val = var_parent_rgt - var_rgt-1; Repeatfetch cur1 into childId;if not done then update product_relation set lft=lft+var_val,rgt =rgt+var_val ,layer=layer-var_layer+var_parent_layer+1 where id=childId;end if;until done end repeat;close cur1; COMMIT;elseif (var_parent_rgt <var_rgt && var_parent_lft < var_lft) then #左移START TRANSACTION; update product_relation set title=var_title where id=var_id;#修改基本信息update product_relation set lft=lft+var_betweenval+1 where lft< var_lft and lft > var_parent_rgt;update product_relation set rgt=rgt+var_betweenval+1 where rgt< var_lft and rgt >=var_parent_rgt;set var_val =var_lft-var_parent_rgt;Repeatfetch cur1 into childId;if not done then update product_relation set lft=lft-var_val,rgt =rgt-var_val ,layer=layer+var_parent_layer-var_layer+1 where id=childId;end if;until done end repeat;close cur1; COMMIT;else #下移update product_relation set title=var_title where id=var_id;#修改基本信息end if;end if; end if; end
// -------------------------------------------------------------------- /**
* 获取父节点
*
*
*/
function getParentNode($item)
{
return $this->db->where(array('layer' => $item['layer']-1,'lft <'=>$item['lft'],'rgt >'=>$item['rgt']))->get('product_relation')->row_array();
} // -------------------------------------------------------------------- /**
* 创建
*
*
*/
function create($editData)
{
$sql = "CALL AddSubNodeByNode(".$editData['parentId'].",'".$editData['title']."' , '".$editData['type']."',-1)";
$this->db->query($sql);
} // -------------------------------------------------------------------- /**
* 获取最新添加的数据
*
*
*/
function _get_newly_one()
{
$this->db->from('product_cat');
$this->db->order_by("updated_at", "desc");
$this->db->limit('1');
$query =$this->db->get();
return $query->row_array();
}
// -------------------------------------------------------------------- /**
* 结果集
*
*
*/
function find_all_categorys()
{
$this->db->from('product_relation');
$this->db->order_by('lft','asc'); $itmes = $this->db->where(array('type' =>'class'))->get()->result_array();
return $itmes;
}
// -------------------------------------------------------------------- /**
* 更新
*
*
*/
function update($id,$editData)
{
$this -> db -> query('call UpdateNode('.$id.','.$editData['parentId'].', "'.$editData['title'].'");');
} // -------------------------------------------------------------------- /**
* 删除单一节点，子节点上移
*
*
*/
function deleteOne($id)
{
$sql = "CALL DelOneNode (".$id.")";
return $this->db->query($sql);
} // -------------------------------------------------------------------- /**
* 删除节点，包括子节点一并删除
*
*
*/
function deleteAll($id)
{
$sql = "CALL DelNode(".$id.")";
return $this->db->query($sql);
}
// -------------------------------------------------------------------- /**
* 子孙分类
* 1、不包含自己所有子类
* 2、包含自己的所有子类
* 3、不包含自己所有父类
* 4、包含自己所有父类
*
*
*/
function getNodeById($id=0,$type=1)
{
$view = $this->getDataById($id);
$this->db->from('product_relation');switch($type){
case '1':
$this->db->where(array('lft >'=>$view['lft'],'rgt <'=>$view['rgt']));
break;
case '2':
$this->db->where(array('lft >='=>$view['lft'],'rgt <='=>$view['rgt']));
break;
case '3':
$this->db->where(array('lft <'=>$view['lft'],'rgt >'=>$view['rgt'])); break;
case '4':
$this->db->where(array('lft <='=>$view['lft'],'rgt >='=>$view['rgt']));
break;
}
$this->db->order_by('lft','asc');
$data = $this->db->get()->result_array();
return $data;
}
// -------------------------------------------------------------------- /**
* 按id获取节点
*
*
*/
function getDataById($id){
return $this->db->where('id',$id)->get('product_relation')->row_array();
}
// --------------------------------------------------------------------
function move_up($id){
$sql = "CALL MoveNodeUp (".$id.")";
return $this->db->query($sql);
}
function move_down($id){
$sql = "CALL MoveNodeDown(".$id.")";
return $this->db->query($sql);
}
}
页:
[1]