I've programmed a membership site for a client within which members join below other users. e.g.
userid | name | subof
1 | John | 0
2 | Joe | 1
3 | Jill | 0
4 | Janet | 2
5 | Juan | 1
6 | George| 2
John and Jill are at the top, Joe and Juan are below John, and Janet and George are below Joe. The tier-ing is used for passing up commission. My client wants to be able to see How many users are below any given user, (at least it's restricted to 8 tiers out)
For now I have added the additional field `num_below` to the user table, and that field is incremented or decremented whenever someone joins or leaves below the user.
The first problem with this is that it feels like it violates good Database Normalization practices~ cuz it's storing data that's already in the DB
The second is that it get's hairy when my client comes and says "Oh, George meant to join under Juan, please move him"
I considered just dynamically calculating the number below every time it's asked for, but the db queries would seem to grow exponentially.
I've written a rectifySubs() function that can go through and fix all of the `num_below` fields, but as there are more members it will become more and more intensive to run~
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);
}
}
Any recommendations? I don't want to put too much redundant data in the DB, but going 8 tiers out every time seems way too processor intensive.
-- EDIT --
I wasn't clear enough about how the tiers worked so I made the table bigger. The key issue I'm addressing with the edit is that any person can have multiple people in a tier directly below them. Hope that makes sense.
-- SOLUTION -- (Implementation of Kakao's solution as a method of the 'Member' Class)
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];
}
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
Update
Multiple members below version:
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