Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: mysql DBI Nested Queries

by atcroft (Monsignor)
on Dec 23, 2012 at 05:49 UTC ( #1010076=note: print w/ replies, xml ) Need Help??


in reply to mysql DBI Nested Queries

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.)

my @chain = get_chain($employee); foreach my $emp (@chain) { print $emp->{name}, qq{\n}; } sub get_chain { my ($e_id) = @_; # Assumes that id is the employee id field, # and supervisor is also an employee 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 ); } }

Here is the full testing code:

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.


Comment on Re: mysql DBI Nested Queries
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1010076]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (10)
As of 2014-07-23 08:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (136 votes), past polls