misc software development snippets

What:
Recreate sql's ORDER BY (multi column) with perl's sort

Issue:
you have 2 sql statements that utilize a multi column order by statement (they return the same type and number of columns of information, for some reason they cannot be combined into one sql statement) but you need to display them as one (properly ordered) table client side

Data:
2 array refs ordered by columns 'date', 'name', 'id' , in that order.

'name', 'date' ("%mm/%dd/%YYYY" ), 'id'

How:
How do you combine them to one properly ordered list?

Example:

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper qw/Dumper/;

# these lists come from a sql statement are are in the order desired but need
# to be combined togther

# one array ref with many records (as array refs)
my $data1 = [
        [qw{Eels   01/11/2009  10955252 field4 field5}],
        [qw{Mapple 01/12/2009  10957336 field4 field5}],
        [qw{Ack    01/12/2009  20005374 field4 field5}],
        [qw{Ack    02/18/2009  20005371 field4 field5}],
];
# access (for the uninitiated)
#warn @{$a->[3]};            # Mapple01/12/200910957336field4field5
#warn join "\t", @{$a->[3]}; # Mapple  01/12/2009  10957336    field4  field5
#warn $a->[3][0];            # Mapple 

# same data format as above, but store in array instead of array ref
# i.e. one array with many records (as array refs)
my @data2 = (
        [qw{Ack    02/19/2008  20005373 field4 field5}],
        [qw{Eels   01/12/2009  10955252 field4 field5}],
        [qw{Mapple 01/12/2009  10985507 field4 field5}],
        [qw{Ack    01/12/2009  20005374 field4 field5}],
        [qw{Zoys   02/09/2009  20004772 field4 field5}],
        [qw{Ack    02/18/2009  20005372 field4 field5}],
);
# access (for the uninitiated)
#warn @{$b[2]};             # Mapple01/12/200910985507field4field5
#warn join "\t", @{$b[2]};  # Mapple  01/12/2009  10985507    field4  field5
#warn $b[2]->[0];           # Mapple
# also
#warn @{[$b[2]->[0]]};      # Mapple 

# combine both lists to one
my @c = (@{$data1},@{[@data2]});
#warn Dumper \@c;

# to confirm
#foreach my $i ( @c ) {
#   warn "$i->[0], $i->[1], $i->[2]\n";
#}
# step 1, now  they are combined: (but not in the correct order)
#Eels, 01/11/2009, 10955252
#Mapple, 01/12/2009, 10957336
#Ack, 01/12/2009, 20005374
#Ack, 02/18/2009, 20005371
#Ack, 02/19/2008, 20005373
#Eels, 01/12/2009, 10955252
#Mapple, 01/12/2009, 10985507
#Ack, 01/12/2009, 20005374
#Zoys, 02/09/2009, 20004772
#Ack, 02/18/2009, 20005372

# try to sort by date
#foreach my $i ( sort { $a->[1] cmp $b->[1] } @c ) {
#   warn "$i->[0], $i->[1], $i->[2]\n";
#}
# hmmm, not what we expect, the names' should be grouped
#Eels, 01/11/2009, 10955252
#Mapple, 01/12/2009, 10957336
#Ack, 01/12/2009, 20005374
#Eels, 01/12/2009, 10955252
#Mapple, 01/12/2009, 10985507
#Ack, 01/12/2009, 20005374
#Zoys, 02/09/2009, 20004772
#Ack, 02/18/2009, 20005371
#Ack, 02/18/2009, 20005372
#Ack, 02/19/2008, 20005373

# ok, let's add some other columns to the sort
# this should sort by the date, name then id field respectivly
#foreach my $i (
#               sort {
#                       $a->[1] cmp $b->[1]
#                                ||
#                       $a->[0] cmp $b->[0]
#                                ||
#                       $a->[2] <=> $b->[2]
#                    } @c
#              ) {
#   warn "$i->[0], $i->[1], $i->[2]\n";
#}
# ok, looks better but still not correct, hmm, why is 2008 first?
#Eels, 01/11/2009, 10955252
#Ack, 01/12/2009, 20005374
#Ack, 01/12/2009, 20005374
#Eels, 01/12/2009, 10955252
#Mapple, 01/12/2009, 10957336
#Mapple, 01/12/2009, 10985507
#Zoys, 02/09/2009, 20004772
#Ack, 02/18/2009, 20005371
#Ack, 02/18/2009, 20005372
#Ack, 02/19/2008, 20005373

# the solution I use is hackish and probably not recommended but seems to work
# in my cases - it appears sort is sorting by the first 2 digits only,
# I am looking for year, month, date, so let's tell sort that,..
# let's split on the '/' and join to a string by year, month, day
#join('', (split '/', $_ 3)[2,0,1]) 

foreach my $i (
               sort {
                      join('', (split '/', $a->[1], 3)[2,0,1])
                           cmp
                      join('', (split '/', $b->[1], 3)[2,0,1])
                                ||
                       $a->[0] cmp $b->[0]
                                ||
                       $a->[2] <=> $b->[2]
                    } @c
              ) {
   warn "$i->[0], $i->[1], $i->[2]\n";
}
# this is what we were looking for
#Ack, 02/19/2008, 20005373
#Eels, 01/11/2009, 10955252
#Ack, 01/12/2009, 20005374
#Ack, 01/12/2009, 20005374
#Eels, 01/12/2009, 10955252
#Mapple, 01/12/2009, 10957336
#Mapple, 01/12/2009, 10985507
#Zoys, 02/09/2009, 20004772
#Ack, 02/18/2009, 20005371
#Ack, 02/18/2009, 20005372

# references:
# http://www.sysarch.com/Perl/sort_paper.html A Fresh Look at Efficient Perl Sorting
# perldoc perldsc -> perl data structures cookbook
# perldoc -f sort -> sort documentation