Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Using Tie::IxHash to keep database query keys/values in order

by bradcathey (Prior)
on Feb 15, 2017 at 22:26 UTC ( #1182101=perlquestion: print w/replies, xml ) Need Help??

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

While I have read other posts on PerlMonks, and knocked around the Interwebs a bit, I'm not finding exactly what I'm looking or.

I want to keep the keys/values in the same order for every array element in an AoH ref.

my $stmt = "SELECT firstname, lastname, city, state FROM addresses ORD +ER BY lastname"; my $data = $self->dbh->selectall_arrayref($stmt, {Slice => {}});

Data::Dumper returns:

[ { 'lastname' => 'Smith', 'city' => 'Chicago', 'state' => 'IL', 'firstname' => 'Jim' }, { 'city' => 'Cleveland', 'state' => 'OH', 'firstname' => 'Susan', 'lastname' => 'Jones' }, { 'state' => 'FL', 'lastname' => 'Waters', 'firstname' => 'Sam', 'city' => 'Miami' } ];

I'd like to have:

[ { 'firstname' => 'Jim', 'lastname' => 'Smith', 'city' => 'Chicago', 'state' => 'IL' }, { 'firstname' => 'Susan', 'lastname' => 'Jones', 'city' => 'Cleveland', 'state' => 'OH' }, { 'firstname' => 'Sam', 'lastname' => 'Waters', 'city' => 'Miami', 'state' => 'FL' } ];

I was thinking this might work, but result is the same.

use Tie::IxHash; my $data = {}; tie %$data, 'Tie::IxHash'; my $stmt = "SELECT firstname, lastname, city, state FROM addresses ORD +ER BY lastname"; $data = $self->dbh->selectall_arrayref($stmt, {Slice => {}});

What am i not understanding? Thanks!

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

Replies are listed 'Best First'.
Re: Using Tie::IxHash to keep database query keys/values in order
by huck (Parson) on Feb 15, 2017 at 22:52 UTC

    "I want to keep the keys/values in the same order for every array element in an AoH ref."

    You just cant do that. You could instruct data dumper to PRINT them in sorted order (local $Data::Dumper::Sortkeys=1;), or you could program your own print loop to print them in a certain order,

    use strict; use warnings; my $data= [ { 'lastname' => 'Smith', 'city' => 'Chicago', 'state' => 'IL', 'firstname' => 'Jim' }, { 'city' => 'Cleveland', 'state' => 'OH', 'firstname' => 'Susan', 'lastname' => 'Jones' }, { 'state' => 'FL', 'lastname' => 'Waters', 'firstname' => 'Sam', 'city' => 'Miami' } ]; my @order=qw/firstname lastname city state/; my $rowct=0; for my $row (@$data) { printf "%5i \n",$rowct++; for my $ordername (@order) { printf " %20s %-20s \n",$ordername,$row->{$ordername}; } }
    but you cannot make perl keep them in any sort of "order" than the one it decides on.

      As an interesting alternative to the qw/.../ you may find some interesting info about getting the field names from the sql request at Re: Field names from DBI? . that would allow you to specify the order via the SELECT statement, but it a two step setup described in setting up and executing the SQL. YMMV

      So Data::Dumper is throwing me off and Tie::IxHash just might be keeping them in order as advertised. I'll write a loop to check that. Thanks for the lesson in D::D

      —Brad
      "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
        So Data::Dumper is throwing me off and Tie::IxHash just might be keeping them in order as advertised.

        It's unlikely that Data::Dumper has anything to do with it. As others have written, my guess would be that selectall_arrayref() is returning a reference to an entirely separate hash it generates and populates internally. After all, how would this function "know" that you want it to use the hash referred to by $data to populate with the data it is to return? I can't use Tie::IxHash to produce an example ATM, but consider:

        c:\@Work\Perl\monks>perl -wMstrict -le "package Foo::Bar { sub TIEHASH {} } ;; my $data = {}; tie %$data, 'Foo::Bar'; print $data; ;; $data = return_anon_hash_ref(); print $data; ;; use Data::Dumper; print Dumper $data; ;; sub return_anon_hash_ref { return { 'sorry' => 'charley' }; } " HASH(0x3bd06c) HASH(0x3bd15c) $VAR1 = { 'sorry' => 'charley' };
        Note that the addresses of $data in the two print statements are different, as are the contents of the referents. Nothing you do with Data::Dumper is going to change this.

        Bottom line: Write your own data display loop or routine and use whatever explicit key order you want.


        Give a man a fish:  <%-{-{-{-<

Re: Using Tie::IxHash to keep database query keys/values in order
by Anonymous Monk on Feb 15, 2017 at 23:17 UTC
    What am i not understanding?

    Probably $data = $self->dbh->selectall_arrayref is replacing the hash reference with a reference to a different hash that isn't tied.

    But hashes don't have ordering, why do you need ordering? Just for printing?

      Yes, I'm building a CSV file, but thanks to the comments above, I'm thinking of 1) writing my own loop, or 2) using Excel::Template. Funny how we get myopic and miss the forest.

      —Brad
      "The important work of moving the world forward does not wait to be done by perfect men." George Eliot

        Hi bradcathey,

        I'm building a CSV file

        If you use selectall_arrayref, that'll pull all the results from the database into memory. I imagine that fetching the results row by row with DBI's fetchrow_arrayref, the return value of which can be fed directly into Text::CSV's print method, would be more efficient.

        Regards,
        -- Hauke D

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1182101]
Approved by stevieb
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (3)
As of 2020-10-01 16:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (16 votes). Check out past polls.

    Notices?