确定“以下用户数量”在多层会员数据库中

Question

我为客户编写了一个会员网站，其中会员的加入低于其他用户。例如

userid | name  | subof
1      | John  | 0
2      | Joe   | 1
3      | Jill  | 0
4      | Janet | 2
5      | Juan  | 1
6      | George| 2

，约翰和吉尔在顶部，乔和胡安在约翰下面，珍妮特和乔治在乔下面。分级用于传递佣金。我的客户希望能够查看有多少用户低于任何给定用户，（至少限制为 8 层）

现在，我已将附加字段“num_below”添加到用户表中，每当有人加入或离开用户时，该字段就会递增或递减。

第一个问题是，感觉它违反了良好的数据库规范化实践~因为它存储的数据已经存在于数据库中。

第二个问题是，当我的客户过来说“哦，乔治”时，它会变得毛茸茸的打算加入胡安的领导下，请移动他”

我考虑过每次要求时动态计算下面的数字，但数据库查询似乎呈指数级增长。

我写了一个 rectifySubs() 函数，可以遍历并修复所有 `num_below` 字段，但是随着成员越来越多，运行起来会越来越密集~

function rectifySubs(){
    $NumBelow=array();//UID=>NUM_BELOW
    $SubOf=array();//UID=>IS_A_SUB_OF_UID
    $Uids=array();//UID
    $r=mysql_query("SELECT uid,subof FROM user");
    if(!$r || mysql_num_rows($r)==0){return 'Invalid';}
    while(list($uid,$subof)=mysql_fetch_row($r)){
        $NumBelow[$uid]=0;
        $SubOf[$uid]=$subof;
        $Uids[]=$uid;
    }
    mysql_free_result($r);

    $RungsUp=8;
    foreach($Uids as $uid){
        $r=1;
        $parent=$SubOf[$uid];
        while($parent>0 && $r<=$RungsUp){
            $NumBelow[$parent]+=1;
            $parent=$SubOf[$parent];
            $r++;
        }
    }
    $QueryByNum=array();
    foreach($NumBelow as $uid=>$num){
        if(!isset($QueryByNum[$num])){$QueryByNum[$num]=array();}
        $QueryByNum[$num][]=$uid;
    }
    unset($QueryByNum[0]);
    mysql_query("UPDATE user SET below=0");
    foreach($QueryByNum as $num=>$uids){
        $where=$or='';
        foreach($uids as $uid){
            $where.=$or."`uid`=".$uid;
            $or=" OR ";
        }
        mysql_query("UPDATE user SET below=".$num." WHERE ".$where);
    }
}

有什么建议吗？我不想在数据库中放入太多冗余数据，但每次都进行 8 层似乎过于占用处理器资源。

-- 编辑 --

我不太清楚这些层是如何工作的，所以我把桌子弄大了。我在编辑中要解决的关键问题是，任何人都可以在其正下方的层级中拥有多个人员。希望这是有道理的。

-- 解决方案 -- （将 Kakao 的解决方案实现为“成员”类的方法）

protected function getNumBelowAtLevel($i=1,$force=false){
    $i=abs((int)$i);
    if($i<=1){return 0;}//Level 1 is just the member themselves
    if($force || !isset($this->numBelow[$i])){
        $Us='';
        $Sels='';
        $Lefts='';
        $Groups='';
        $comma='';
        $nl='';
        for($k=1;$k<=$i-1;$k++){
            $j=$k==1?'0':$k-1;
            $Us.=$comma.'u'.$k;
            $Sels.=$comma.$nl.'m'.$k.'.mid as u'.$k;
            $Lefts.=$nl.'left join members as m'.$k.' on m'.$k.'.subof = m'.$j.'.mid';
            $Groups.=$comma.'u'.$k;

            $nl="\n\t\t\t\t\t";
            $comma=', ';
        }
        $sql="select count(*) - 1 as users_below
from (
    select distinct {$Us}
        from (
            select 
                {$Sels}
            from members as m0
                {$Lefts}
            where m0.mid = {$this->id}
                group by {$Groups} with rollup
            ) d
    ) a";
        if(DEBUG){var_dump($sql);}
        $r=mysql_query($sql);
        list($this->numBelow[$i])=mysql_fetch_row($r);
    }
    return $this->numBelow[$i];
}

Answer 1

select (case 
   when m1.userid is null then 0
   when m2.userid is null then 1
   when m3.userid is null then 2
   when m4.userid is null then 3
   when m5.userid is null then 4
   when m6.userid is null then 5
   when m7.userid is null then 6
   when m8.userid is null then 7
   else 8 end
   ) as users_below

from members as m0
left join members as m1 on m1.subof = m0.userid
left join members as m2 on m2.subof = m1.userid
left join members as m3 on m3.subof = m2.userid
left join members as m4 on m4.subof = m3.userid
left join members as m5 on m5.subof = m4.userid
left join members as m6 on m6.subof = m5.userid
left join members as m7 on m7.subof = m6.userid
left join members as m8 on m8.subof = m7.userid

where m0.userid = 1

更新

多个成员以下版本：

select count(*) - 1 as users_below
from (
   select distinct u1, u2, u3, u4, u5, u6, u7
   from (
      select 
         m1.userid as u1, 
         m2.userid as u2, 
         m3.userid as u3,
         m4.userid as u4,
         m5.userid as u5,
         m6.userid as u6,
         m7.userid as u7

      from members as m0
      left join members as m1 on m1.subof = m0.userid
      left join members as m2 on m2.subof = m1.userid
      left join members as m3 on m3.subof = m2.userid
      left join members as m4 on m4.subof = m3.userid
      left join members as m5 on m5.subof = m4.userid
      left join members as m6 on m6.subof = m5.userid
      left join members as m7 on m7.subof = m6.userid

      where m0.userid = 1
      group by u1, u2, u3, u4, u5, u6, u7 with rollup
   ) d
) a

Answer 2

就我个人而言，我认为你预先计算数据的解决方案很好。

我要改变的一件事是使“纠正”功能更加智能（可选）而不必重建整个数据集。如果一个人被移动到另一个分支，唯一需要重新计算的是该人的旧超级和新超级。

例如，如果 Joe 从 Bob 转移到 Alice，那么 Bob 和他的所有超级用户都会失去 Joe 的“num_below”，然后 Alice 和她的所有超级用户都会获得 Joe 的“num_below”。请注意，用于调整超级的“num_below”实际上是num_below + 1，因为乔本人不被算作他自己的一部分。

编辑：

或者，查看：

• http://articles.sitepoint.com/article/ hierarchical-data-database/2

这是一种不同的数据结构，更容易与其他数据结构一起执行此特定计算（子级数量），但确实有自己的一组数字（左/右）需要维护。

Answer 3

我添加更多人员只是为了清楚地表明我了解您的需求：

userid | name  | subof
1      | John  | 0
2      | Joe   | 1
3      | Jill  | 0
4      | Janet | 2
5      | Dawn  | 4
6      | James | 4
7      | Mary  | 3
8      | Doug  | 6

假设您的老板需要乔手下的人员。
你想要得到：Janet、Dawn、James、Doug——对吧？

与其添加新列，不如更改 subof 的定义（在我的示例中我将其设为 varchar）怎么样？

所以你的表应该是这样的：

userid  name    subof   
1           John    0
2           Joe     0.1
3           Jill    0
4           Janet   0.1.2
5           Dawn    0.1.2.4
6           James   0.1.2.4
7           Mary    0.3
8           Doug    0.1.2.4.6

金字塔的顶部是 0，所以 John 和 Jill 仍然位于顶部。然后你就可以通过 0 之后的序列知道每个人是谁。

• 将 john 和 jill 更改为 0 而不是 0.0 以使更新更容易

这样做，你可以在以下查询中获得所需的结果：

    select * from temporary WHERE subof like '0.1.2%' ORDER BY userid ASC;
//this is joe's subof +'.'+ his userid

所以你的下一个问题是如何插入一名新兵。好的。比尔在道格手下登场。那么比尔的插入内容是什么？

// 首先获取 subof 和 userid

SELECT subof, userid 
FROM tablename 
WHERE name = 'doug'; #answer 0.1.2.4.6
$subof, $userid = mysql_fetch; //pseudo code

//然后插入新行 subof.userid

INSERT into tablename (userid, name, subof) VALUES ('9', 'Bill', '$subof.userid');

所以现在你有了另一行：

9   Bill    0.1.2.4.6.8

但是等等......还有更多！

---

将示例替换为詹姆斯和道格，以替换为乔治和胡安的新表，以专注于修改后的问题：

===== 乔治和胡安的新示例

userid | name  | subof
1      | John  | 0
2      | Joe   | 0.1
3      | Jill  | 0
4      | Janet | 0.1.2
5      | Juan  | 0.1
6      | George| 0.1.2

约翰和吉尔在顶部，乔和
胡安（Juan）低于约翰（John），珍妮特（Janet）和
乔治低于乔。分层是
用于放弃佣金。

问题

我的
客户希望能够看到如何
许多用户低于任何给定用户，
（至少限制在8层
出）

答案

    SELECT count(*) 
    FROM tablename 
    WHERE subof LIKE 'subof_of_any_given_user+that_users_userid%';
//get those under Joe by using '0.1.2' (Joe's subof + his userid)

问题

当它变得毛茸茸的时候
我的客户走过来说“哦，乔治
打算加入胡安领导下，请移动
他”

答案

选择用户 ID、姓名、子类别
FROM 表名
WHERE name in('Juan','George');

//$juan_userid = 5
//$juan_subof = 0.1
//$updatevalue = $juan_subof.'.'.$juan_userid; //0.1.5

//$george_userid = 6
//$george_subof = 0.1.2
/$subofmatch = $george_subof.'.'.$george_userid; //0.1.2.6

因此您的自动查询将如下所示：

UPDATE tablename 
SET subof = (REPLACE(subof, '$george_subof', '$updatevalue')) 
WHERE (subof like '$subofmatch%' OR userid = '$george_userid')



 // here it is with number values to make it easier to understand //  
    UPDATE tablename 
    SET subof = (REPLACE(subof, '0.1.2', '0.1.5')) 
    WHERE (subof like '0.1.2.6%' OR userid = '6');

给您这个新结果：

userid  name    subof   
1           John    0
2           Joe     0.1
3           Jill    0
4           Janet   0.1.2
5           Juan    0.1
6           George  0.1.5

享受吧！

黎明

Answer 4

以下解决方案使用非递归存储过程：

示例用法：

call employees_hier(1);

+-----------+
| num_below |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

希望您发现它有帮助 - 下面是完整脚本:)

完整脚本：

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',7);

drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table emps;
        insert into emps select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select count(*) as num_below from hier where depth > 0;

/*
-- use this if you want to return the employees instead

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;
*/

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;

-- call this sproc from your php

call employees_hier(1);
call employees_hier(2);
call employees_hier(3);
call employees_hier(5);
call employees_hier(6);
call employees_hier(7);