One way of doing this is to use recursion-when you get an employee's supervisor, call the look-up function again on them, and repeat. Then, as you are returning, put the person currently being queried at the end of the returned results.
My code below built a SQLite DB for testing, and queried a particular user about mid-way of the employee list (256, queried employee 128), but should give you a possible way to accomplish this. (You also want to make sure there is a terminating condition to check, such as the CEO being listed as their own supervisor, or no value for supervisor for the CEO, for instance.)
use strict;
use DBI;
use DBD::SQLite;
use Data::Dumper;
use Getopt::Long;
$| = 1;
my $datafile = $0 . q{.sqlite};
if ( scalar grep( /^-/, @ARGV ) ) {
GetOptions( "datafile=s" => \$datafile, );
}
if ( !-e $datafile ) {
open( DF, q{>>} . $datafile )
or die(qq{Couldn't open $datafile for append: $!\n});
close(DF);
}
my $dbh = DBI->connect( qq{dbi:SQLite:dbname=$datafile},
q{}, q{}, { AutoCommit => 0 } );
{
my $tables_query = q{
SELECT COUNT(name) FROM
(
SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master
) WHERE type='table';
};
my $tqs = $dbh->prepare($tables_query);
my $tqr = $tqs->execute;
my @row_tqr = $tqs->fetchrow_array;
$tqs->finish;
if ( !$row_tqr[0] ) {
my $create_table_query = q{
CREATE TABLE employee (
id INTEGER PRIMARY KEY,
name STRING, title STRING, supervisor INTEGER,
UNIQUE(id)
);
};
my $cr = $dbh->do($create_table_query);
foreach my $create_index_query (
q{CREATE INDEX id_idx ON employee ( id );},
q{CREATE INDEX name_idx ON employee ( name ); },
q{CREATE INDEX supervisor_idx ON employee ( supervisor );}
+,
)
{
$cr = $dbh->do($create_index_query) or die( $dbh->errstr )
+;
}
my $preload_query =
q{INSERT INTO employee ( id, name, title, supervisor ) VALUES ( NULL,
+?, 'foo', ? ); };
my $pq = $dbh->prepare($preload_query);
# print q{employee_1}, q{ -> }, q{1}, qq{\n};
$pq->execute( q{employee_} . 1, 1 ) or die $dbh->errstr;
foreach my $i ( 1 .. 8 ) {
foreach my $j ( ( 2**( $i - 1 ) + 1 ) .. ( 2**$i ) ) {
my $e_name = sprintf q{employee_%d}, $j;
my $e_sup = int( 2**( $i - 1 ) );
# print $e_name, q{ -> }, $e_sup, qq{\n};
$pq->execute( $e_name, $e_sup ) or die $dbh->errstr;
}
}
$pq->finish;
$dbh->commit;
}
}
my $employee = 128;
# &walk_all;
print qq{Hierarchy for employee ${employee}:\n};
my @chain = get_chain($employee);
# print Data::Dumper->Dump( [ \@chain, ], [ qw( *chain ) ] ), qq{\n};
foreach (@chain) {
print $_->{name}, qq{\n};
}
$dbh->disconnect;
sub walk_all {
my $emp =
q{SELECT id, name, title, supervisor FROM employee ORDER BY id;
+};
my $emps = $dbh->prepare($emp);
my $empr = $emps->execute or die( $dbh->errstr );
while ( my @row_emp = $emps->fetchrow_array ) {
print join( q{, }, @row_emp ), qq{\n};
}
$emps->finish;
}
sub get_chain {
my ($e_id) = @_;
my $en_select =
q{SELECT name, supervisor FROM employee WHERE ( id = ? ) LIMIT 1
+;};
my $ens = $dbh->prepare($en_select);
my $enr = $ens->execute($e_id) or die $dbh->errstr;
my %link = ( id => $e_id, );
my @row_enr = $ens->fetchrow_array;
$link{name} = $row_enr[0];
if ( defined $row_enr[1] and $row_enr[1] != $e_id ) {
$link{supervisor} = $row_enr[1];
return ( get_chain( $link{supervisor} ), \%link );
}
else {
return ( \%link );
}
}
# $dbh = DBI->connect( qq{dbi:SQLite:dbname=$datafile}, q{}, q{} );
# my $maintenance = q{VACUUM employee; };
# $dbh->do($maintenance);
# $dbh->disconnect;
__END__
Hope that helps.