In search of elegant code: Combining two statements into one (DBI)

[David Filmer]

(I'm gonna use a DBI query as an example, but this question is really
a Perl syntax inquiry (how to add elegance to a bit of code), not a
database question).

Suppose I have a database table "department" with a simple structure
such as:

id integer primary key #might not be sequential because of
deletes
name varchar

I want to build a hash (%dept) from the database such that (for
example) $dept{2} eq "Accounting".

I access the database:

$sth = $dbh->prepare("SELECT id, name FROM department");
$sth->execute;

Now, I COULD do this:

$dept_ref = %{$sth->fetchall_hashref('id')};

Which gives me a situation where $dept_ref->{2}->{'name'} eq
"Accounting" - not really very close to the data structure I want (a
simple hash). So I COULD, instead, populate a hash like this:

%dept = %{$sth->fetchall_hashref('id')};

That gets me closer - now $dept{2}{'name'} eq "Accounting." DBI does
this, of course, because I might be selecting multiple columns. But,
in this case, I'm only selecting a key/value pair from the database
and would like to go directly to a simple hash. But I need an EXTRA
STEP to get to my goal - something like this:

$dept{$_} = $dept{$_}{'name'} for keys %dept; #now $dept{2} eq
'Accounting'

That got me what I wanted, but it's not elegant - I had to populate
the hash and then manipulate it. Is there an elegant way I can express
the subroutine call (in this case to fetchall_hashref) so that it does
this in just one step?

 

[Darin McBride]

(I'm gonna use a DBI query as an example, but this question is really
a Perl syntax inquiry (how to add elegance to a bit of code), not a
database question).

Yes ... but it does have a bearing on the solution.

Suppose I have a database table "department" with a simple structure
such as:

id integer primary key #might not be sequential because of
deletes
name varchar

I want to build a hash (%dept) from the database such that (for
example) $dept{2} eq "Accounting".

Hopefully, this table stays relatively small. ;-)

I access the database:

$sth = $dbh->prepare("SELECT id, name FROM department");
$sth->execute;

Now, I COULD do this:

$dept_ref = %{$sth->fetchall_hashref('id')};

I doubt it. This would return a hash in scalar context - just remove
the %{}, and you'd be right.

Which gives me a situation where $dept_ref->{2}->{'name'} eq
"Accounting" - not really very close to the data structure I want (a
simple hash). So I COULD, instead, populate a hash like this:

Well, actually pretty close. You could write that as
$dept_ref->{2}{'name'} - same thing, really. So, the only difference
is that you've got a ref to the hash of hashes, rather than a hash of
hashes.

%dept = %{$sth->fetchall_hashref('id')};

That gets me closer - now $dept{2}{'name'} eq "Accounting." DBI does
this, of course, because I might be selecting multiple columns. But,
in this case, I'm only selecting a key/value pair from the database
and would like to go directly to a simple hash. But I need an EXTRA
STEP to get to my goal - something like this:

Extra steps are not necessarily non-elegant. Intermediary variables
often are elegant in that they make the code easier to read. What you
want is to merely make compact code - not always the same thing as
elegant.

$dept{$_} = $dept{$_}{'name'} for keys %dept; #now $dept{2} eq
'Accounting'

That got me what I wanted, but it's not elegant - I had to populate
the hash and then manipulate it. Is there an elegant way I can express
the subroutine call (in this case to fetchall_hashref) so that it does
this in just one step?

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

I don't have time right now to check the exact syntax of the arrayref
function, but that should be pretty close.

 

[Tore Aursand]

Now, I COULD do this:

$dept_ref = %{$sth->fetchall_hashref('id')};

Which gives me a situation where $dept_ref->{2}->{'name'} eq
"Accounting" - not really very close to the data structure I want (a
simple hash). So I COULD, instead, populate a hash like this:

%dept = %{$sth->fetchall_hashref('id')};

That gets me closer - now $dept{2}{'name'} eq "Accounting." DBI does
this, of course, because I might be selecting multiple columns. But, in
this case, I'm only selecting a key/value pair from the database and
would like to go directly to a simple hash. But I need an EXTRA STEP to
get to my goal - something like this:

$dept{$_} = $dept{$_}{'name'} for keys %dept; #now $dept{2} eq
'Accounting'

That got me what I wanted, but it's not elegant - I had to populate the
hash and then manipulate it. Is there an elegant way I can express the
subroutine call (in this case to fetchall_hashref) so that it does this
in just one step?

You want to play with the 'map' function. You also want to use
'fetchall_arrayref' instead;

my $stDepts = $dbh->prepare( 'SELECT id, name FROM dept );
$stDepts->execute();
my %dept = map { $_->[0] => $_->[1] } @{$stDepts->fetchall_arrayref()};
$stDepts->finish();

 

[Heinrich Mislik]

(I'm gonna use a DBI query as an example, but this question is really
a Perl syntax inquiry (how to add elegance to a bit of code), not a
database question).

Suppose I have a database table "department" with a simple structure
such as:

id integer primary key #might not be sequential because of
deletes
name varchar

I want to build a hash (%dept) from the database such that (for
example) $dept{2} eq "Accounting".

This is form perldoc DBI and should do what you want:

# get array of id and name pairs:
my $ary_ref = $dbh->selectcol_arrayref("select id, name from table", { Columns=>[1,2] });
my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => name

cheers