in reply to Re: Sort in multilevel hash
in thread Sort in multilevel hash

The respons from 
print Dumper(\%shipto);

[download]
was this: 
$VAR1 = { 'country' => { 'label' => 'Namn', 'i' => 3 }, 
'name' => { 'label' => 'Klinik', 'i' => 11 }, 
'contact' => { 'label' => 'Remitterad av', 'i' => 9 },
 'phone' => { 'label' => 'Remiss utgångsdatum', 'i' => 10 }, 
'zipcode' => { 'label' => 'Postnr', 'i' => 6 }, 
'state' => { 'label' => 'Frikort, utgångsdatum', 'i' => 8 },
 'address2' => { 'label' => '', 'i' => 5 }, 
'email' => { 'label' => 'Klinik adress', 'i' => 12 }, 
'city' => { 'label' => 'Stad', 'i' => 7 }, 
'fax' => { 'label' => 'Personnr', 'i' => 2 }, 
'address1' => { 'label' => 'Adress', 'i' => 4 } };

[download]
and the response from 'ref' was: 

$VAR1 = undef;

[download]
So the output of this is the first level of the hash. I want to sort on the contents in 'fax'!

Replies are listed 'Best First'.
Re^3: Sort in multilevel hash
by TJPride (Pilgrim) on Jan 09, 2012 at 09:30 UTC
    As Anonymous stated, you can just use an ORDER BY command at the end of your SQL queries to sort your output by any field you prefer. And yes, you need placeholders (or at least some function to safe all your form values before inserting them into queries), and I'm guessing from looking at your query that that could be made a lot more compact as well. But here's a code sample that demonstrates how to sort on fax:

    use strict;
use warnings;
use Data::Dumper;

my $form; $form->{'all_shipto'} = [
  {
    'country' => { 'label' => 'Namn', 'i' => 3 }, 
    'name' => { 'label' => 'Klinik', 'i' => 11 }, 
    'contact' => { 'label' => 'Remitterad av', 'i' => 9 },
    'phone' => { 'label' => 'Remiss utgångsdatum', 'i' => 10 }, 
    'zipcode' => { 'label' => 'Postnr', 'i' => 6 }, 
    'state' => { 'label' => 'Frikort, utgångsdatum', 'i' => 8 },
    'address2' => { 'label' => '', 'i' => 5 }, 
    'email' => { 'label' => 'Klinik adress', 'i' => 12 }, 
    'city' => { 'label' => 'Stad', 'i' => 7 }, 
    'fax' => { 'label' => 'A', 'i' => 2 }, 
    'address1' => { 'label' => 'Adress', 'i' => 4 } 
  },
  {
    'country' => { 'label' => 'Namn', 'i' => 3 }, 
    'name' => { 'label' => 'Klinik', 'i' => 11 }, 
    'contact' => { 'label' => 'Remitterad av', 'i' => 9 },
    'phone' => { 'label' => 'Remiss utgångsdatum', 'i' => 10 }, 
    'zipcode' => { 'label' => 'Postnr', 'i' => 6 }, 
    'state' => { 'label' => 'Frikort, utgångsdatum', 'i' => 8 },
    'address2' => { 'label' => '', 'i' => 5 }, 
    'email' => { 'label' => 'Klinik adress', 'i' => 12 }, 
    'city' => { 'label' => 'Stad', 'i' => 7 }, 
    'fax' => { 'label' => 'B', 'i' => 2 }, 
    'address1' => { 'label' => 'Adress', 'i' => 4 } 
  }
];

for (sort {
    $a->{'fax'} cmp $b->{'fax'}
} @{$form->{'all_shipto'}}) {
    print Dumper($_);
}

    [download]
[reply]
[d/l]
      THANK YOU!! I finally maaged by sorting in SQL. It was not possible to make nested Select. I could make this better query with left join, but then I couldn't make the filtering work so either I got all records or none... I could not manage to sort the hash; nothings changed whatever I tried. BUT Finally I managed to find out how to write the query. This was the final (not very nice) working code: 
      
      my $query = qq|SELECT
                   s.shiptofax, s.shiptocountry, s.shiptophone, s.ship
+toaddress1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname, s.shiptoemail, o.waybill, o.s
+hipvia|;

          $query .= qq| FROM shipto s, oe o 
          WHERE ((trans_id =  $form->{"$form->{vc}_id"}) AND ( trans_i
+d = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))|;

          $query .= qq| UNION
          SELECT
           s.shiptofax, s.shiptocountry,  s.shiptophone, s.shiptoaddre
+ss1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, o.waybill, o.shipvia 
          FROM shipto s
          JOIN oe o ON (o.id = s.trans_id)
          WHERE ((o.$form->{vc}_id = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  ))|;
          $query .= qq| UNION
          SELECT
           s.shiptofax, s.shiptocountry, s.shiptophone, s.shiptoaddres
+s1, s.shiptoaddress2,
          s.shiptocity, s.shiptostate, s.shiptozipcode,
          s.shiptocontact, s.shiptoname,
          s.shiptoemail, a.waybill, a.shipvia
          FROM shipto s
          JOIN $table a ON (a.id = s.trans_id)
          WHERE ((a.$form->{vc}_id = $form->{"$form->{vc}_id"})
                  AND ((to_date( s.shiptophone, 'YYYY-MM-DD') >= to_da
+te( to_char(current_timestamp,'YYYY-MM-DD'), 'YYYY-MM-DD'))
                  )) |;


  if ($form->{id}) {
    $query .= qq|
                 EXCEPT
         SELECT
          s.shiptofax, s.shiptocountry, s.shiptophone, s.shiptoaddress
+1, s.shiptoaddress2,
         s.shiptocity, s.shiptostate, s.shiptozipcode,
         s.shiptocontact, s.shiptoname,
         s.shiptoemail, o.waybill, o.shipvia
         FROM shipto s, oe o
         WHERE ((s.trans_id = '$form->{id}') AND ( o.id = '$form->{id}
+'))
         |;
  }
     $query .= qq| ORDER BY shiptofax, shiptostate |;

      [download]
      I should find a way to get rid of all the double records (containing the same information) but thats another question... Regards/ G!
[reply]
[d/l]

In Section Seekers of Perl Wisdom