mysql - 计算按等级分组的左右子节点总数

Question

我正在从事一个基于推荐 (MLM) 奖励人们的项目

我已经能够计算左右两边子节点的总数，但现在我需要能够在用户有一定数量的用户低于他们的特定等级时更新用户等级. (我将在下面更好地解释:

用户表

 id | name  | parentID| side | rank   |
 4  | Dan   |         |      | starter|
 5  | Chris |   4     | left | starter|
 6  | James |   4     | right| starter|
 7  | Tybe  |   5     | left | starter|
 8  | Rose  |   5     | right| starter|
 9  | Paul  |   6     | left | starter|
10  | Zach  |   6     | right| starter|

树形表

userID | left | right| leftCount | rightCount|
 4     |  5   |  6   |    3      |    3      |
 5     |  7   |  8   |    1      |    1      |
 6     |  9   |  10  |    1      |    1      |
 7     |      |      |    0      |    0      |
 8     |      |      |    0      |    0      |
 9     |      |      |    0      |    0      |
 10    |      |      |    0      |    0      |

下面是根据这个表生成的树

我如何在用户注册时更新 leftCount 和 rightCount

    $referralid; //who is referring the current user
    $side; //the side the user is to fall under, either left or right

    $temp_referralid = $referralid; 
    $temp_sideCount = $side.'Count'; //leftCount or rightCount

    $temp_side = $side;
    $total_count=1;
    $i=1;
    while($total_count>0){
        $stmt = $db->prepare("SELECT * from tree WHERE id=:id");
        $stmt->bindValue(':id', $temp_referralid);
        $stmt->execute();
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        $current_temp_sideCount = ($r[$temp_sideCount]+1);

       //This will add (+1) to the leftCount or rightCount 
        //of the referral depending on the side the user 
       //choose to fall under.
        $sql ="UPDATE `tree` SET `$temp_sideCount`=:count WHERE `id` = :id";
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':count', $current_temp_sideCount);
        $stmt->bindValue(':id', $temp_referralid);
        $stmt->execute();

    //if the user has someone that referred them as 
    //only the last person at the top has no referral

    if($temp_referralid!=""){
        //change the $referralid to the person that referred the person 
        //referring this user. this is where the loop will go through 
        //to the last person maintaining the side upward
        $next_referralid = $this->getReferringId($db, $temp_referralid);
        $temp_side = $this->getReferringIdSide($db, $temp_referralid);
        $temp_sideCount = $temp_side.'Count';
        $temp_referralid = $next_referralid;

        $i++;

        }else{
           $total_count=0;
            }

    }

}

使用的函数

 getReferringId($db, $id) //gets the referringId of the user passed as 
                         //param from the user table
 getReferringIdSide($db, $id) //gets the side which the user 
                              //is on (left or right) from the user table

所有这一切都工作得很好，但我需要实现一些东西，但我还没有找到解决它的最佳方法。

如果每个用户达到一个阶段，我需要不断更改他们的排名。见下文和示例:

 stage 1: starter //just registered
 stage 2: grow // the person has leftCount=3 and rightCount=3
 stage 3: growbig //the person has 7 - grow user on the left 
                  //and 7- grow user on the right 
 state 4: growbigger //the person has 7 - growbig on left and 7 growbig
                     //on the right 

到第 2 阶段，我没有问题，但向上是我无法掌握正确逻辑的地方

更新例如:growbig 的数量可以来自腿上的任何位置，它不应该只是直接节点，只是每边都低于该用户的等级计数

更新:以更清晰的术语和规范重新询问

它是一个二叉树(2:2)，这意味着一个人只能有两个直接在他下面的人(一个在左边，另一个在右边。

我的 table 上面的图片看起来像这样

树形表

userID | left (userid) | right (userid)|  rank
 8     |     4         |  12           |
 4     |     2         |  6            |
 12    |     10        |  14           |
 2     |     1         |   3           |
 6     |     5         |   7           |
 10    |     9         |  11           |
 14    |     14        |  15           |

注意:用户的任何一方或双方都没有任何人在他之下。这意味着如果一个用户在他下面没有人，那么树( Twig )就到此为止，如果他直接在左边有一个人而右边没有人，则意味着左边的 Twig 可以继续生长。

根据用户的增长情况对每个用户进行评分的逻辑以及他们如何设法平衡各方的增长是问题所在。

逻辑

级别 1:主管:用户必须在其右分支上有 3 个用户，在左分支上有 3 个用户。

Rank 2: controller: 用户必须在左边有 7 个用户是“supervisors”，在右边也有 7 个已经成为 supervisors 的用户。

等级 3:高级 Controller:用户必须有 7 个用户在左侧分支为“controller”，并且在右侧也有 7 个“controller”。

等级4:大使:用户左边必须有7个高级控制者，右边有7个高级控制者

等级 5:高级大使:用户必须有 7 名用户，左边是大使，右边是大使。

等级6:大大使:用户必须有7位用户在他的两边都是高级大使。

解释:让我挑选排名并解释一下:

级别:大使 - 如果 ID 为 3 的用户右侧有 45 人，右侧分支机构中有 7 人是高级 Controller ，左侧也有 67 人，其中 7 人已经是高级 Controller ，那么用户ID 3 应升级为“大使”

@布拉格

Answer 1

这更有可能是我解决这个问题的方式(使用 http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ ):

新的表架构是:

'id'| 'name' | 'left'| 'right'| 'rank'   
4   | 'Dan'  | 1     | 14     | 'starter'
5   | 'Chris'| 2     | 7      | 'starter'
6   | 'James'| 8     | 13     | 'starter'
7   | 'Tybe' | 3     | 4      | 'starter'
8   | 'Rose' | 5     | 6      | 'starter'
9   | 'Paul' | 9     | 10     | 'starter'
10  | 'Zach' | 11    | 12     | 'starter'

完整版:

请注意，我使用以下值以避免使用更大的数据集

-- on each side 
set @grow = 1 // -- 1 child R & L
set @growbig = 2 // -- 2 grow child R & L

SQL Fiddle

程序

CREATE PROCEDURE p(IN p_parent varchar(15), IN p_children varchar(15))
BEGIN
  IF NOT EXISTS (
    select parent.*, count(distinct dc.id)  direct_child
    from u parent
    left join u as dc on(parent.left=dc.left-1 or parent.right=dc.right+1)
    WHERE parent.name = p_parent
    group by parent.id
    having count(distinct dc.id) =2
    )
  THEN
      SET @myLeft =(SELECT `left` FROM u WHERE name = p_parent);

      UPDATE u SET `right` = `right` + 2 WHERE `right` > @myLeft;
      UPDATE u SET `left` = `left` + 2 WHERE `left` > @myLeft;

      INSERT INTO u(`name`, `left`, `right`) 
      VALUES(p_children, @myLeft + 1, @myLeft + 2);

  END IF;
END
//


call p('Tybe','Marjorie') //
call p('Tybe','Vernon') //
call p('Rose','Marc') //
call p('Rose','Peter') //
call p('Marc','Lily') //
call p('Marc','Ignotus') //
call p('Ignotus','Aragorn') //
call p('Ignotus','Arwen') //
call p('Peter','Chase') //
call p('Peter','Foreman') //
call p('Chase','Pétunia') //
call p('Chase','Dudley') //
call p('Foreman','Bob') //
call p('Foreman','Taub') //
call p('Paul','Lisa') //
call p('Paul','Bilbo') //
call p('Lisa','House') //
call p('Lisa','Gandalf') //
call p('Gandalf','Frodo') //
call p('Gandalf','Legolas') //
call p('House','Thirteen') //
call p('House','Wilson') //
call p('Thirteen','Ginny') //
call p('Thirteen','Harry') //
call p('Wilson','Kutner') //
call p('Wilson','Master') //
call p('Master','Adams') //
call p('Master','Park') //
call p('Zach','Ary') //

成长

set @grow = 1 //
update u
set rank = 'grow'
where u.id in (
    select id from ( 
        select id 
        from (
            select p.id id, p.name name, lc.id lcid, null rcid
            from  u p
            inner join u l on (p.left = l.left-1 and p.right <> l.right+1)
            inner join u lc on (lc.left >= l.left and lc.right <= l.right)
            inner join u r on (p.right = r.right+1 and p.left <> r.left-1)

            union all

            select p.id id, p.name name, null lcid, rc.id rcid
            from  u p
            inner join u l on (p.left = l.left-1 and p.right <> l.right+1)
            inner join u r on (p.right = r.right+1 and p.left <> r.left-1)
            inner join u rc on (rc.left >= r.left and rc.right <= r.right)
        ) p
        group by p.id
        having 
            count(distinct lcid) >= @grow
            and count(distinct rcid) >= @grow
    ) z
)
//

变大

set @growbig = 2 //
update u
set rank = 'growbig'
where u.id in (
    select id from ( 
        select id 
        from (
            select p.id id, p.name name, lc.id lcid, null rcid
            from  u p
            inner join u l on (p.left = l.left-1 and p.right <> l.right+1)
            inner join u lc on (lc.rank ='grow' and lc.left >= l.left and lc.right <= l.right)
            inner join u r on (p.right = r.right+1 and p.left <> r.left-1)

            union all

            select p.id id, p.name name, null lcid, rc.id rcid
            from  u p
            inner join u l on (p.left = l.left-1 and p.right <> l.right+1)
            inner join u r on (p.right = r.right+1 and p.left <> r.left-1)
            inner join u rc on (rc.rank ='grow' and rc.left >= r.left and rc.right <= r.right)
        ) p
        group by p.id
        having 
            count(distinct lcid) >= @growbig
            and count(distinct rcid) >= @growbig
    ) z
)

//

查询 1:

-- output parent that have both right and left child
select parent.*, count(distinct dc.id)  direct_child
from u parent
left join u as dc on(parent.left=dc.left-1 or parent.right=dc.right+1)
group by parent.id
having count(distinct dc.id) =2

Results :

| id |     name | left | right |    rank | direct_child |
|----|----------|------|-------|---------|--------------|
|  4 |      Dan |    1 |    72 | growbig |            2 |
|  5 |    Chris |    2 |    35 |    grow |            2 |
|  6 |    James |   36 |    71 |    grow |            2 |
|  7 |     Tybe |    3 |     8 |    grow |            2 |
|  8 |     Rose |    9 |    34 | growbig |            2 |
|  9 |     Paul |   37 |    66 |    grow |            2 |
| 13 |     Marc |   24 |    33 |    grow |            2 |
| 14 |    Peter |   10 |    23 |    grow |            2 |
| 16 |  Ignotus |   25 |    30 |    grow |            2 |
| 19 |    Chase |   17 |    22 |    grow |            2 |
| 20 |  Foreman |   11 |    16 |    grow |            2 |
| 25 |     Lisa |   40 |    65 |    grow |            2 |
| 27 |    House |   47 |    64 |    grow |            2 |
| 28 |  Gandalf |   41 |    46 |    grow |            2 |
| 31 | Thirteen |   58 |    63 |    grow |            2 |
| 32 |   Wilson |   48 |    57 |    grow |            2 |
| 36 |   Master |   49 |    54 |    grow |            2 |

查询 2:

-- show the tree
SELECT CONCAT( REPEAT('|...', COUNT(parent.name) - 1), node.id, ' ', node.name,' /',node.rank) AS name 
FROM u AS node,
        u AS parent  
WHERE node.left BETWEEN parent.left AND parent.right
GROUP BY node.name
ORDER BY node.left

Results :

|                                          name |
|-----------------------------------------------|
|4 Dan /growbig                                 |
||...5 Chris /grow                              |
||...|...7 Tybe /grow                           |
||...|...|...12 Vernon /starter                 |
||...|...|...11 Marjorie /starter               |
||...|...8 Rose /growbig                        |
||...|...|...14 Peter /grow                     |
||...|...|...|...20 Foreman /grow               |
||...|...|...|...|...24 Taub /starter           |
||...|...|...|...|...23 Bob /starter            |
||...|...|...|...19 Chase /grow                 |
||...|...|...|...|...22 Dudley /starter         |
||...|...|...|...|...21 Pétunia /starter        |
||...|...|...13 Marc /grow                      |
||...|...|...|...16 Ignotus /grow               |
||...|...|...|...|...18 Arwen /starter          |
||...|...|...|...|...17 Aragorn /starter        |
||...|...|...|...15 Lily /starter               |
||...6 James /grow                              |
||...|...9 Paul /grow                           |
||...|...|...26 Bilbo /starter                  |
||...|...|...25 Lisa /grow                      |
||...|...|...|...28 Gandalf /grow               |
||...|...|...|...|...30 Legolas /starter        |
||...|...|...|...|...29 Frodo /starter          |
||...|...|...|...27 House /grow                 |
||...|...|...|...|...32 Wilson /grow            |
||...|...|...|...|...|...36 Master /grow        |
||...|...|...|...|...|...|...38 Park /starter   |
||...|...|...|...|...|...|...37 Adams /starter  |
||...|...|...|...|...|...35 Kutner /starter     |
||...|...|...|...|...31 Thirteen /grow          |
||...|...|...|...|...|...34 Harry /starter      |
||...|...|...|...|...|...33 Ginny /starter      |
||...|...10 Zach /starter                       |
||...|...|...39 Ary /starter                    |

查询 3:

-- show parent + child data
select *,(l.right - l.left -1)/2 l , (r.right - r.left -1)/2 r from u p
inner join u l on (p.left = l.left-1 and p.right <> l.right+1)
inner join u r on (p.right = r.right+1 and p.left <> r.left-1)

Results :

| id |     name | left | right |    rank | id |    name | left | right |    rank | id |     name | left | right |    rank |  l |  r |
|----|----------|------|-------|---------|----|---------|------|-------|---------|----|----------|------|-------|---------|----|----|
|  4 |      Dan |    1 |    72 | growbig |  5 |   Chris |    2 |    35 |    grow |  6 |    James |   36 |    71 |    grow | 16 | 17 |
|  5 |    Chris |    2 |    35 |    grow |  7 |    Tybe |    3 |     8 |    grow |  8 |     Rose |    9 |    34 | growbig |  2 | 12 |
|  6 |    James |   36 |    71 |    grow |  9 |    Paul |   37 |    66 |    grow | 10 |     Zach |   67 |    70 | starter | 14 |  1 |
|  7 |     Tybe |    3 |     8 |    grow | 12 |  Vernon |    4 |     5 | starter | 11 | Marjorie |    6 |     7 | starter |  0 |  0 |
|  8 |     Rose |    9 |    34 | growbig | 14 |   Peter |   10 |    23 |    grow | 13 |     Marc |   24 |    33 |    grow |  6 |  4 |
| 13 |     Marc |   24 |    33 |    grow | 16 | Ignotus |   25 |    30 |    grow | 15 |     Lily |   31 |    32 | starter |  2 |  0 |
| 16 |  Ignotus |   25 |    30 |    grow | 18 |   Arwen |   26 |    27 | starter | 17 |  Aragorn |   28 |    29 | starter |  0 |  0 |
| 14 |    Peter |   10 |    23 |    grow | 20 | Foreman |   11 |    16 |    grow | 19 |    Chase |   17 |    22 |    grow |  2 |  2 |
| 19 |    Chase |   17 |    22 |    grow | 22 |  Dudley |   18 |    19 | starter | 21 |  Pétunia |   20 |    21 | starter |  0 |  0 |
| 20 |  Foreman |   11 |    16 |    grow | 24 |    Taub |   12 |    13 | starter | 23 |      Bob |   14 |    15 | starter |  0 |  0 |
|  9 |     Paul |   37 |    66 |    grow | 26 |   Bilbo |   38 |    39 | starter | 25 |     Lisa |   40 |    65 |    grow |  0 | 12 |
| 25 |     Lisa |   40 |    65 |    grow | 28 | Gandalf |   41 |    46 |    grow | 27 |    House |   47 |    64 |    grow |  2 |  8 |
| 28 |  Gandalf |   41 |    46 |    grow | 30 | Legolas |   42 |    43 | starter | 29 |    Frodo |   44 |    45 | starter |  0 |  0 |
| 27 |    House |   47 |    64 |    grow | 32 |  Wilson |   48 |    57 |    grow | 31 | Thirteen |   58 |    63 |    grow |  4 |  2 |
| 31 | Thirteen |   58 |    63 |    grow | 34 |   Harry |   59 |    60 | starter | 33 |    Ginny |   61 |    62 | starter |  0 |  0 |
| 32 |   Wilson |   48 |    57 |    grow | 36 |  Master |   49 |    54 |    grow | 35 |   Kutner |   55 |    56 | starter |  2 |  0 |
| 36 |   Master |   49 |    54 |    grow | 38 |    Park |   50 |    51 | starter | 37 |    Adams |   52 |    53 | starter |  0 |  0 |