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

[download]

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'
    }
];

[download]

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'
    }
];

[download]

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

[download]

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

    [download]
    but you cannot make perl keep them in any sort of "order" than the one it decides on.

[reply]
[d/l]
[select]

      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

[reply]

      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
[reply]
        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'
        };

        [download]
        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:  <%-{-{-{-<

[reply]
[d/l]
[select]
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?

[reply]
[d/l]

      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
[reply]

        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

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom