Re^2: Elegance, dammit!

I'm afraid that doesn't work for me, ysth. 'selectall' rather than 'selectcol' is the deal-killer here (I don't see the need for fetching the entire content of that table, extracting the columns, and then doing all this processing.)

This gets me exactly the info I need - although the structure is a little different from what I want:

my @ref = @{
    $dbh->selectcol_arrayref(
        "select date, label, subject from travel
         where date>= curdate()-5 order by date",
         {Columns=>[1,2,3]}
    )
};
[download]

I do indeed appreciate your attempt to approach it from a broader viewpoint - I try not to lose track of the fact that stepping back can be as important as focusing down when trying to solve a problem. [Grin] Hell, my wife is Japanese-American. She'd beat my butt if I was ever so silly as to lose track of context.

Comment on Re^2: Elegance, dammit! Download Code

Replies are listed 'Best First'.
Re^3: Elegance, dammit! by ysth (Canon) on Jun 17, 2007 at 05:05 UTC
I'm not understanding you at all. There's either something I'm missing or something you are. selectcol_arrayref and selectall_hashref will both read all the available rows. Yes, selectcol_arrayref by default throws away all but the first column, but in your invokation, you are telling it to keep all three columns.	[reply]
Re^4: Elegance, dammit! by oko1 (Deacon) on Jun 19, 2007 at 17:28 UTC
(Sorry for the long delay; I got bushwhacked by Real Life, but managed to survive it.) I'd tried using selectall_hashref originally - that is, in fact, the first approach I tried - but I recall being unsatisfied with the output. I just went back and reproduced what I'd done then, and the problem turns out to be that either the docs don't tell you how to do what I need to do, or it just can't be done with selectall_hashref. In short, here are the problems: 1) Remapping that hashref into the arrayref that I want isn't any easier than what I was looking for - unless I've missed something. 2) The output format is... graceless. Perhaps it is, as you say, that I'm missing something - but I want two columns as an arrayref keyed by date, and what selectall_hashref returns is `'2007-05-13' => { subject => 'foobar', date => '2007-05-13', # Erm... label => 'xyz' }` [download] The statement that I'm using is one I showed previously: `my $ref = $dbh->selectcol_arrayref( "select date, label, subject from travel where date >= curdate()-5 + order by date", { Columns=>[1,2,3] } );` [download] If you can show me how to do this better with selectall_hashref, I'd be grateful.	[reply] [d/l] [select]
Re^5: Elegance, dammit! by ysth (Canon) on Jun 19, 2007 at 18:07 UTC
Now I'm not sure what format you are actually looking for. I originally thought you wanted a hash of arrayrefs, where the arrays contain the label and subject fields and the hash is keyed by date. But I'm not sure what "two columns as an arrayref keyed by date" looks like. Assuming the former is correct, you'd do it like this: `my $ref = $dbh->selectall_hashref(... for my $row (values %$ref) { $row = [ @$row{qw/label subject/} ]; } # or # $_ = [ @$_{qw/label subject/} ] for values %$ref; # or # $ref = { map {; $_, [ @{$ref->{$_}}{qw/label subject/} ] } keys %$re +f };` [download] But it would make more sense to me to just leave it as a hash of hashes; unless there are extreme efficiency issues to concern you, I prefer hashes over arrays when the values are different types of things (in this case, label and subject).	[reply] [d/l]