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

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]

Replies are listed 'Best First'.
Re^4: Sortin multilevel hash
by gueriniere (Initiate) on Jan 09, 2012 at 21:04 UTC
    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