DBI fetchall_arrayref

kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks,

I'm doing a LEFT JOIN of two tables. The tables contain something like:

#table1
fruit_id   fruit
1          apple
2          orange
3          pear

#table2
some_id fruit_id  some_col
1       1         a1
2       1         a2
3       1         a3
4       2         z1
5       2         z2

my $dbi = get_connection();

# sql
my $sql = q~
SELECT fruit, some_col
FROM table1
LEFT JOIN table2
   ON table1.fruit_id=table2.fruit_id
WHERE table1.fruit_id=1
~;

# Relevent Perl code
my $sth = $dbh->prepare($sql);
$sth->execute();
my $matrix_ref = $sth->fetchall_arrayref();
[download]

$matrix_ref holds the output as follows:

[[apple, a1], [apple, a2], [apple, a3]];
[download]

Somewhat duplicative. Is there a way to get the following output?

[[apple, a1, a2, a3]];
[download]

As usual, thanks for reading and offering help :)

Comment on DBI fetchall_arrayref Select or Download Code

Replies are listed 'Best First'.
Re: DBI fetchall_arrayref by amw1 (Friar) on Jun 15, 2004 at 15:39 UTC
afaik the only real way to do this is to scan through matrix_ref and generate the array you want. SQL doesn't really allow you to do things like that. `# build a hash to mash all of the fruits together. # foreach my $row (@$matrix_ref) { # add the some_col to the fruit push(@{$tmp_hash->{$row[0]}}, $row[1]); } my $output; my $i; foreach my $fruit (keys(%$tmp_hash)) { # make the fruit the first element of the sub array push(@{$output->[$i]}, $fruit); # push the some_col's onto the array push(@{$output->[$i]}, @{$tmp_hash->{'fruit'}}; # start creating the next sub array $i++; } #output should now be what you want.` [download] not tested, if it blows up your house it ain't my fault. :)	[reply] [d/l]
Re^2: DBI fetchall_arrayref by kiat (Vicar) on Jun 15, 2004 at 15:45 UTC
Thanks, amw1! I wasn't sure whether I could do it with SQL alone or perhaps with some version of DBI methods (like fetchall_hashref) that I'm not aware of. So now I know I can't do it with either SQL or DBI alone :)	[reply]
Re: DBI fetchall_arrayref by Tomte (Priest) on Jun 15, 2004 at 15:45 UTC
I'd hold the consolidated date using a hash the following way `my $array = [[apple, a1], [apple, a2], [apple, a3]]; my %hash = (); foreach (@{$array}) { $hash{$_->[0]} = [] unless defined($hash{$_->[0]}); push (@{$hash{$_->[0]}}, $_->[1]); }` [download] resulting in `%hash == ( apple => [ a1, a2, a3 ], )` Update:Another way to get your whished for result `[[apple, a1, a2, a3 ]]`: `use List::Util qw(reduce); my $array = [[apple, a1], [apple, a2], [apple, a3]]; my $result = []; unshift(@$array, []); push @$result, reduce { push(@$a, $b->[0]) unless exists $a->[0]; push(@$a, $b->[1]); $a; } @$array;` [download] regards, tomte An intellectual is someone whose mind watches itself. -- Albert Camus	[reply] [d/l] [select]
Re: DBI fetchall_arrayref by jeffa (Bishop) on Jun 15, 2004 at 17:40 UTC
If you only want to find the rows that match apple, then just fetch the rows for apples: `#untested my $matrix_ref = $dbh->selectall_arrayref(' SELECT some_col FROM table1 INNER JOIN table2 ON table1.fruit_id = table2.fruit_id WHERE table1.fruit = ? ', undef, 'apple');` [download] This will yield: `[ [a1], [a2], [a3] ]` [download] You can instead use `selectcol_arrayref` to return `[a1, a2, a3]`. No apple, but we already know we want apples. [Updated, had to confirm exact return structure of a one column set from `selectall_arrayref()`] jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re: DBI fetchall_arrayref by chance (Beadle) on Jun 15, 2004 at 15:38 UTC
maybe something like: select distinct some_col from table1 .. <what you had before> and use selectcol_arrayref. you can push or unshift 'apple' onto front or back of results then, if you really want em there. apologize in advance if above is a MySQL-ism.	[reply]
Re: DBI fetchall_arrayref by Roy Johnson (Monsignor) on Jun 15, 2004 at 15:52 UTC
It looks like what you want is a UNION of the fruit column from table1 and some_col from table2. `SELECT fruit FROM table1 UNION SELECT some_col FROM table2` [download] That will give you `[[apple], [a1], [a2], [a3]]`, I guess, which is pretty close to what you're looking for. To get exactly what you want, you'd need to transpose rows and columns, for which I know of no straightforward, general solution. We're not really tightening our belts, it just feels that way because we're getting fatter.	[reply] [d/l] [select]