Sort by Referrer (quite complicated)

Question

I will try my best to be both succinct and fully explanatory in this predicament.

On a site I manage, we allow a manager to view their "Recruiting Downline" which entails a list of all agents they personally recruited, as well as the recruits that particular agent (and so on and so on) brought to the Team.

For example:

• I recruit two Agents, A and B.
• A recruits two agents, C and D.
• B recruits two agents, E and F.
• D recruits two agents G and H.
• C, E, and F do nothing.

In the database, every individual agent record has a field for 'referring agent', which lists their recruited agent.

• As the top level, when I click "My Recruits", I am shown a list of all sub agents (because they ALL fall under my umbrella).
• A is able to view C, D, G, and H.
• B is only able to view E and F as they are his only downline recruits and they have brought nobody on board.

While this functionality works great it is flawed for two reasons:

Because of the way our PHP scripts are built, we are unable to sort the commission level data as a whole. Example: Even as the top man and I can see everybody, sorting by 'commission level' sorts my immediate agents by the criteria, then their downline as an item, then continues the sort based on my criteria. This is difficult to understand so to demonstrate, assume the table below displays the 'commission level' for ALL agents:

• A, 7
• B, 6
• C, 5
• D, 6
• E, 5
• F, 2
• G, 5
• H, 1

Note: an agent can NEVER recruit another agent at a higher level than they sit but they can recruit at ANY level below them (e.g. a 7 can recruit at 1,2,3,4,5,6 while a 3 can only recruit a 1,2).

FROM MY (high level) perspective,

While it would make sense for the data to be 'sorted by commission level' as: A, D, B, G, C, E, F, H - this is not the case.

Instead (view from top agent's perspective mind you) is: A, D, G, H, C, B, E, F

basically, every while loop depends on the DIRECT upline agent number to determine who falls next in line.

I understand this is 'very' difficult to understand but let me know if I can provide any additional understanding into our current 'sort' issue.

Answer 1

Sounds like you're trying to implement tree like structures in your DB. Have you considered using using Celko trees:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Personally, I'd look to implement most of the ordering, selecting aspects of this kind of project in the DB. Note that Celko trees aren't really suitable for very large datasets.

Answer 2

So the problem is that you don't store the "commission level" (which I take to be the number of nodes whose distance is < ∞) in the database?

You have two options:

• Change your schema so that this is easily retrievable. See this article.
• If it's not an option calculating it using only SQL in MySQL may not be possible because you don't have iterative queries (WITH RECURSIVE clause). You have to make multiple queries in PHP.

Answer 3

i think i understood you. you want to sort by commission_level within a given agent hierarchy. the following may help (http://pastie.org/1111097)

drop table if exists agent;

create table agent
(
agent_id int unsigned not null auto_increment primary key,
name varchar(32) not null,
commission_level tinyint unsigned default 0,
parent_agent_id int unsigned default null
)
engine = innodb;

insert into agent (name, commission_level, parent_agent_id) values

('I', 99, null),
  ('A', 7, 1),
  ('B', 6, 1),
    ('C', 5, 2),
    ('D', 6, 2),
    ('E', 5, 3),
    ('F', 2, 3),
      ('G', 5, 5),
      ('H', 1, 5);


delimiter ;

drop procedure if exists agent_hier;

delimiter #

create procedure agent_hier
(
in p_agent_id int unsigned
)
proc_main:begin

declare done tinyint unsigned default 0;
declare dpth smallint unsigned default 0;


create temporary table hier(
 parent_agent_id int unsigned, 
 agent_id int unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier values (p_agent_id, p_agent_id, dpth);

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

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

while done <> 1 do

    if exists( select 1 from agent a inner join hier on a.parent_agent_id = hier.agent_id and hier.depth = dpth) then

        insert into hier 
            select a.parent_agent_id, a.agent_id, dpth + 1 from agent a
            inner join tmp on a.parent_agent_id = tmp.agent_id and tmp.depth = dpth;

        set dpth = dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = dpth;

    else
        set done = 1;
    end if;

end while;


select 
 a.agent_id,
 a.name as agent_name,
 if(a.agent_id = b.agent_id, null, b.agent_id) as parent_agent_id,
 if(a.agent_id = b.agent_id, null, b.name) as parent_agent_name,
 hier.depth,
 a.commission_level
from 
 hier
inner join agent a on hier.agent_id = a.agent_id
inner join agent b on hier.parent_agent_id = b.agent_id
order by
 -- dont want to sort by depth but by commision instead - i think ??
 -- hier.depth, hier.agent_id; 
 a.commission_level desc;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end proc_main #


delimiter ;


/*

select * from agent;

call agent_hier(1);
call agent_hier(2);
call agent_hier(3);
call agent_hier(5);
*/