> From: Matthias Klopper [mailto:mkloepper@stripped]
> Hi,
> joining in on this problem I have a little question. Is there a
> possibility to yquery for all people below e.g. manager 1? Inlcuding
> every level of people under that manager? Or do I have to put the
> recursive part into my code?
I would certainly put the recursive part into my code when displaying
hierarchy trees.
Consider Nick Lindridge's table outlined earlier:
(employee_id int unsigned auto_increment not null,
boss_id int unsigned not null,
etype enum ('head honcho', 'SVP', 'VP', 'TM', 'TO', 'temp', 'service') not
null,
name varchar(50) not null,
phone varchar(50) not null,
primary key (boss_id, employee_id),
index by_etype (etype)
)
Then, in pseudo code, to display the entire tree:
BEGIN PSEUDO CODE
set recordset_honchos = select * from employee where boss_id = employee_id
order by employee_id;
set recordset_others = select * from employee where boss_id <> employee_id
order by boss_id;
for honcho_position = 0 to number_of_elements_in(recordset_honchos) - 1
honcho_record = recordset_honchos(honcho_position)
honcho_employee_id = honcho_record.field("employee_id")
call print_employee(honcho_record, 0)
call expand_tree(recordset_others, honcho_employee_id, 0)
next
sub expand-tree(my_recordset, target_boss_ID, magnitude)
for recordset_position = 0 to number_of_elements_in(my_recordset)
REM Find out if this record has the appropriate boss_id
my_record = my_recordset(recordset_position)
if (my_record.field("boss_id") == target_boss_id) then
REM (we have a match)
call print_employee(my_record, 1+magnitude)
this_employee_id = my_record.field("employee_id")
call expand_tree(my_recordset, this_employee_id,
1+magnitude)
else
REM check to see if we need to run further in the
for-loop
REM this is the case if we have gone "past" the
position
REM in the recordset where we might find our boss_id
if (my_record.field("boss_id") > target_boss_id)
then exit for
end if
next
end sub
sub print_employee(emp_record, magnitude)
print [magniutude] tabs
-- print selected fields from record
end sub
END PSEUDO CODE
If you want to display the tree from a specific employee and down, just
alter the initial honcho select statement thus:
set recordset_honchos = select * from employee where employee_id =
[specific_id]
Note: with proper indexing of the boss-employment relationship you will not
have to run through the whole recordset when looking for matches in the
expand section. This pseodo code is certainly not optimised
/ Otto, with experience from threaded discussion fora.
