[San-Diego-pm] Perl Meeting on Monday, May 9th

Tkil tkil at scrye.com
Tue May 10 13:22:49 PDT 2005

>>>>> "Menolly" == menolly  <menolly at mib.org> writes:

Menolly>   my %taken;
Menolly>   @taken{map $_->[0], @{$rd2->{'sth'}->fetchall_arrayref()}} = ();

You were complaining that you couldn't readily do that in one line.
Complain no more:

  my %taken = map { ( $_->[0] => 1 ) } @{ $rd2->{sth}->fetchall_arrayref() };

One more optimization -- if you're returning just a single value per
row (or if you only care about the first value in each row returned by
$rd2->{sth}), use "selectcol_arrayref".  Only complication is that it
seems to require the database handle, not just the statement handle.
If you have the corresponding $dbh lying around, fine:

  my %taken = map { ( $_ => 1 ) }
                @{ $rd2->{dbh}->selectcol_arrayref( $rd2->{sth} ) };

If not...  more attribute abuse!  Every statement handle has a
"Database" attribute that points to its parent $dbh:

  my %taken = map { ( $_ => 1 ) }
                @{ $rd2->{sth}->{Database}->selectcol_arrayref( $rd2->{sth} ) };

Depending on how much access you have to the server and the SQL
running behind "$rd2->{sth}", you're possibly better off asking the DB
server to do set membership tests for you.

  my @untaken = @{ $dbh->selectcol_arrayref( <<SQL, {}, $person_id ) };
SELECT a.thingy
  FROM all_thingy a
                       FROM taken_thingies t
                       WHERE t.thingy_id = a.thingy_id
                         AND t.person_id = ? )

(Season that anti-join to taste.)  You could even get more perverse
and do another nested query with a random value in one column, sort by
that, grab the first, you're done.


More information about the San-Diego-pm mailing list