Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

mysql DBI Nested Queries

by finhagen (Sexton)
on Dec 23, 2012 at 03:13 UTC ( #1010068=perlquestion: print w/ replies, xml ) Need Help??
finhagen has asked for the wisdom of the Perl Monks concerning the following question:

I have a mysql employee database which I access via the mysql DBI module and push into the Spreadsheet:WriteExcel module to a spreadsheet for reporting purposes. That (remarkably) all works fine.

Each employee record has a employee ID (eid) supervisor (supervisor) column (among other columns in the database). I can perform a simple SELECT statement (SELECT eid, supervisor from employeedb WHERE eid = 'xxxxxx') and extract that employee's supervisor to a variable $supervisor.

What I need is a WHILE loop wherein I enter an employee ID and walk that employee's reporting chain backwards or up the chain. In my mind that means capturing the supervisor data in a variable and inserting that into another SELECT statement (SELECT eid, supervisor from employeeID WHERE supervisor = '$supervisor';) and iterating up the reporting chain until I arrive at the CEO.

My problem (among many ;-) is I don't know how to pass the $supervisor variable from SELECT statement to SELECT statement given it changes for each iteration and the variable scope has to pass from one WHILE {} to the next WHILE {}.

Here's some code that doesn't work (I am pretty proficient writing code that doesn't work ;-):
our $lname; our $fname; our $title; our $eid; our $supervisor; our $supervisor2; our $supervisor3; ; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM e +mployeedb WHERE eid ='employee1';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor) = @$aref; #write data to spreadsheet row by row $worksheet0->write(19, 26, $fname, $format_PRDmidd +le); $worksheet0->write(20, 26, $lname, $format_PRDmidd +le); $worksheet0->write(21, 26, $title, $format_PRDmidd +le); $worksheet0->write(22, 26, $supervisor, $format_PR +Dbottom); our $supervisor2 = $supervisor; } my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM e +mployeedb WHERE supervisor ='$supervisor2';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor2) = @$aref; #write data to spreadsheet row by row $worksheet0->write(23, 11, $fname, $format_AMCtop2 +); $worksheet0->write(24, 11, $lname, $format_AMCmidd +le); $worksheet0->write(25, 11, $title, $format_AMCmidd +le); $worksheet0->write(26, 11, $supervisor2, $format_A +MCbottom); our $supervisor3 = $supervisor2; }

I hope that explanation is clear enough to illicit the wise advice of the Perl Monks. Thank you!

Hagen Finley Boulder, CO

Comment on mysql DBI Nested Queries
Download Code
Re: mysql DBI Nested Queries
by atcroft (Monsignor) on Dec 23, 2012 at 05:49 UTC

    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:

    Hope that helps.

Re: mysql DBI Nested Queries
by CountZero (Bishop) on Dec 23, 2012 at 14:00 UTC
    Unfortunately MySQL does not have recursive SQL capabilities, so the recursion will have to be done in Perl, which makes this kind of work rather slow since you will have to climb the adjacency tree node-by-node.

    There are other solutions though, but they all mean that you will have to change your datatables. The most flexible and still fast model is probably the "closure table model". More info on the different options for hierarchical data models can be found at: Models for hierarchical data.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
Re: mysql DBI Nested Queries
by roboticus (Canon) on Dec 23, 2012 at 14:09 UTC

    finhagen:

    If you're doing a report of employees by supervisor, or similar, another way to handle the project is to make the database do some of the work for you. For example, you might be able to make a query give you the information sorted conveniently, and then add a control break. Perhaps something like:

    our $lname; my $sth = $dbh->prepare(<<EOSQL); SELECT sup.fname, sup.lname, sup.title, sup.supervisor, emp.fname, emp.lname, emp.title, emp.supervisor FROM employeedb sup JOIN employeedb emp ON ..... ORDER BY sup.fname, sup.lname, emp.fname, emp.lname EOSQL $sth->execute or die $sth->errstr; my $previous_supervisor = ''; while (my $aref = $sth->fetchrow_arrayref) { my ($sup_fname, $sup_lname, $sup_title, $emp_fname, $emp_lname, $emp_title) = @$aref; # control-break: if a new supervisor is found, skip a few lines and + add a header my $current_supervisor = "$sup_fname $sup_lname"; if ($current_supervisor ne $previous_supervisor) { $R += 3; $C = 0; $worksheet0->write($R, $C++, "SUPERVISOR: ", $format_HEADER); $worksheet0->write($R, $C++, $current_supervisor, $format_HEADER +); $R++; $C=0; $worksheet0->write($R, $C++, "First Name", $format_COL_HEADER); $worksheet0->write($R, $C++, "Last Name", $format_COL_HEADER); $worksheet0->write($R, $C++, "Title", $format_COL_HEADER); $worksheet0->write($R, $C++, "Supervisor", $format_COL_HEADER); } # Add employee to sheet $R++; $C=0; $worksheet0->write($R, $C++, $emp_fname, $format_NORMAL); $worksheet0->write($R, $C++, $emp_lname, $format_NORMAL); $worksheet0->write($R, $C++, $emp_title, $format_NORMAL); $worksheet0->write($R, $C++, $emp_supervisor, $format_NORMAL); }

    (Yes, I know this doesn't match your task exactly, it's just an example you might be able to adapt.)

    Your example shows nearly duplicated code, which indicates that you might be able to restructure your code to use a subroutine:

    sub process_employee { my ($worksheet0, $employee, $format, $col, $row) = @_; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor " ."FROM employeedb WHERE eid = ?"); $sth->execute($employee) or die $sth->errstr; my ($fname, $lname, $title, $supervisor); while (my $aref = $sth ->fetchrow_arrayref) { ($fname, $lname, $title, $supervisor) = @$aref; $worksheet0->write($row++, $col, $fname, $format); $worksheet0->write($row++, $col, $lname, $format); $worksheet0->write($row++, $col, $title, $format); $worksheet0->write($row++, $col, $supervisor, $format); } return $supervisor } my $supervisor = process_employee($worksheet0, $employee1, $format_PRD +middle, 19, 26); if (defined $supervisor) { process_employee($worksheet0, $supervisor, $format_AMCtop, 23, 11) +; }

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

      I stumbled onto a reporting chain approach that works, but it's ugly. Looking at your sub process comment, I am wondering if you and the the Monks can help me turn the following code into a sub process?

      I replaced the CEO's supervisor field (which was blank) with "CEO" which should allow for a while ( $supervisor ne 'CEO') {}

      I tried a couple of approaches but they all ended up in an infinite loop.

      Here's the code that works:

      our $lname; our $fname; our $title; our $eid; our $supervisor; our $supervisor2; our $supervisor3; our $supervisor4; our $supervisor5; our $supervisor6; our $supervisor7; our $supervisor8; #select fname, lname, csg from acnse where supervisor ='pierre.nanterm +e'; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM a +cnse WHERE eid ='employeID';"); $sth->execute or die $sth->errstr; my $row=23; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor, $format +_HRbottom); $row++; $supervisor2 = $supervisor; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor2';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor2) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor2, $forma +t_HRbottom); $row++; $supervisor3 = $supervisor2; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor3';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor3) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor3, $forma +t_HRbottom); $row++; $supervisor4 = $supervisor3; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor4';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor4) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor4, $forma +t_HRbottom); $row++; $supervisor5 = $supervisor4; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor5';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor5) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor5, $forma +t_HRbottom); $row++; $supervisor6 = $supervisor5; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor6';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor6) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor6, $forma +t_HRbottom); $row++; $supervisor7 = $supervisor6; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor7';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor7) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor7, $forma +t_HRbottom); $row++; $supervisor8 = $supervisor7; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor8';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor8) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor8, $forma +t_HRbottom); $row++; } } } } } } } }
      Hagen Finley Boulder, CO

        finhagen:

        Sure thing. Just take it step by step. First, notice that your code is structured something like this:

        initialization execute an SQL statement while (my $aref=$sth->fetchrow_arrayref) { ... write to spreadsheet ... prepare & execute an SQL statement while (my $aref=$sth->fetchrow_arrayref) { ... write to spreadsheet ... prepare & execute an SQL statement while (my $aref=$sth->fetchrow_arrayref) { ... write to spreadsheet ... prepare & execute an SQL statement while (...) { <<< same thing for several more levels } } } }

        The first thing you need to do is identify a repeated chunk of code that's relatively easy to separate from the surrounding code. There are several things you might choose, but the one that jumped out at me was the code that converts the array reference into a set of variables and then writes the variables to a worksheet.

        It's easy to separate from the surrounding code because there's only three variables on the input side ($aref, $worksheet0 and $row) and one variable on the output side ($supervisor). Since the $row is going to change everywhere, and since it would be pain to have to pass it around and return it everywhere, I decided to leave it a global variable. So what I wound up with is this:

        sub add_aref_to_sheet { my ($aref, $worksheet0) = @_; my ($fname, $lname, $title, $supervisor) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRtop2); $worksheet0->write($row++, 2, $lname, $format_HRmiddle); $worksheet0->write($row++, 2, $title, $format_HRmiddle); $worksheet0->write($row++, 2, $supervisor, $format_HRbottom); $row++; return $supervisor; }

        We return the supervisor variable so you can use it in the next level. When you pull that code out into a subroutine, your code simplifies a good bit:

        our $lname; our $fname; our $title; our $eid; our $supervisor; our $supervisor2; our $supervisor3; our $supervisor4; our $supervisor5; our $supervisor6; our $supervisor7; our $supervisor8; #select fname, lname, csg from acnse where supervisor ='pierre.nanterm +e'; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM a +cnse WHERE eid ='employeID';"); $sth->execute or die $sth->errstr; my $row=23; while ( my $aref = $sth ->fetchrow_arrayref) { $supervisor2 = add_aref_to_sheet($aref, $worksheet0); my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FRO +M acnse WHERE eid ='$supervisor2';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { $supervisor3 = add_aref_to_sheet($aref, $worksheet0); my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor +FROM acnse WHERE eid ='$supervisor3';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { $supervisor4 = .... ... continue for several more levels ... } } } sub aref { ... same as shown earlier ... }

        There are a good few tuneups you can do to this program. You might want to read up on placeholders in DBI. That can simplify your code a bit, too. Then, depending on how many supervisors a person may have, whether you want to go as many levels as required without hardcoding, etc., there are several ways you could go from here. I'll stop here, as I don't want to take *all* the fun out of it! ;^) But I *will* provide a hint: If each person has only one supervisor, you can turn the successive level of while loops into a simple loop. Give it a try and let me know if you run into any stumbling blocks.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

Re: mysql DBI Nested Queries
by Anonymous Monk on Dec 24, 2012 at 15:53 UTC

    A simplified iterative approach, provided in pseudocode. Assumes supervisor is empty or null when hits CEO level. (Adjust while condition if not a good assumption.)

    my $supervisor = $initial_employee_id; while ($supervisor) ( $row = select columns where eid = $supervisor; print out spreadsheet row for employee; $supervisor = $row{supervisor}; }

    Also, since the database only returns a single row, you should be able to use the selectrow_arrayref function to make your sql code a bit simpler. And as for your "how to use variable inside sql string" question, see 7548

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2014-08-30 10:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (292 votes), past polls