用户
 找回密码
 入住 CI 中国社区
搜索
CodeIgniter 中国开发者社区»论坛 › Developers › Web 开发综合讨论 › mysql 实现左右值无限分类
返回列表 发新帖
查看: 2774|回复: 0
收起左侧

mysql 实现左右值无限分类

[复制链接]
发表于 2013-12-11 16:42:01 | 显示全部楼层 |阅读模式
本帖最后由 xutwt@qq.com 于 2013-12-21 08:49 编辑

product_relation数据表

id                        int  primary 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)
begin
declare 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) then
select  lft,rgt into var_lft,var_rgt  from  product_relation  where id=var_id;
select * from  product_relation  where 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) then
START 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)
begin
declare 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) then
START 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)
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;
select count(*) into var_item from product_relation where id=var_id;
if(var_item) then
START 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) then
START TRANSACTION;
select lft,rgt,layer into var_lft,var_rgt,var_layer from  product_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 then
select 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) then
START 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 then
select 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;

Repeat
fetch 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: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_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) then
select 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) then
select 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)then
set 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;
Repeat
fetch 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;
Repeat
fetch 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);
}
}






mysql, 左右

相关帖子
回复

使用道具 举报

返回列表 发新帖

本版积分规则