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

[Menolly]

On Tue, 10 May 2005, Tkil wrote:

>>>>>> "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() };

Indeed; perhaps next time I'm messing with the file.  (Too close to
release for mere aesthetic changes; the syntax one is already pushed to
QA.)

> 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:

I don't have it conveniently[*], which is why I didn't use
selectcol_arrayref -- I'm quite fond of that in other contexts.

> 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} ) };

Ah!  This changes my previous "conveniently", but I'm not sure it's
actually any simpler than my existing code.

> 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
>  WHERE NOT EXISTS ( SELECT *
>                       FROM taken_thingies t
>                       WHERE t.thingy_id = a.thingy_id
>                         AND t.person_id = ? )
> SQL

MySQL 4.0.22; no subqueries.

-- 
menolly at mib.org                    http://www.livejournal.com/~nolly/

On that day, many will say to me, "Lord, Lord, did we not prophesy in
your name, and cast out demons in your name, and do many mighty works
in your name?" And then will I declare to them, "I never knew you;
depart from me you evildoers." -- Matt 7:20-23, RSV