Archive

Posts Tagged ‘sql’

Ruby, SQL Query result to iterable data structure

January 22nd, 2010 dwright No comments

This is an example of how to transform a multi-table join result returned by SQL and iterate over it to retrieve the data you are seeking.

Problem Space:

You have a multi-table join result returned by a SQL query which contains the data you need but in a 'flat' format which makes it difficult to extract, maintaing the data relationship.

The Data:

This data structure represents the data returned by a multi-table join.

sql_results = [
 {'t_name' => 'Acct & Benft','h_name' => 'Account',  'type' => 'pu_group'},
 {'t_name' => 'Acct & Benft','h_name' => 'Account',  'type' => 'py_group'},
 {'t_name' => 'Acct & Benft','h_name' => 'Account',  'type' => 'c_group'},
 {'t_name' => 'Acct & Benft','h_name' => 'Account',  'type' => 'pu_line'},
 {'t_name' => 'Acct & Benft','h_name' => 'Account',  'type' => 'py_line'},
 {'t_name' => 'Acct & Benft','h_name' => 'Account',  'type' => 'cl_line'},
 {'t_name' => 'Acct & Benft','h_name' => 'Benft',    'type' => 'b_line'},
 {'t_name' => 'Acct & Benft','h_name' => 'Benft',    'type' => 'b_c_line'},
 {'t_name' => 'Acct & Benft','h_name' => 'Benft',    'type' => 'b_c_add'},
 {'t_name' => 'Acct & Benft','h_name' => 'Benft',    'type' => 'b_group'},
 {'t_name' => 'Catg',        'h_name' => 'Catg Pge', 'type' => 'catg_line'},
 {'t_name' => 'Catg',        'h_name' => 'Catg Pge', 'type' => 'catg_group'},
 {'t_name' => 'List Page',   'h_name' => 'All St',   'type' => 's_line' },
 {'t_name' => 'List Page',   'h_name' => 'All St',   'type' => 's_group'},
 {'t_name' => 'List Page',   'h_name' => 'Cpns & S', 'type' => 'cpn_group'},
 {'t_name' => 'List Page',   'h_name' => 'Cpns & S', 'type' => 'cpn_line_a'},
 {'t_name' => 'List Page',   'h_name' => 'Cpns & S', 'type' => 'cpn_line'},
 {'t_name' => 'TLP',         'h_name' => 'Mrch Rand','type' => 'contact'},
 {'t_name' => 'TLP',         'h_name' => 'Mrch Rand','type' => 'tlp_group'},
 {'t_name' => 'TLP',         'h_name' => 'Mrch Rand','type' => 'tlp_line'}
]

From the data, I need to create a table where:

't_name' is a header with one or more sub-nodes, 'h_name'.
and 'h_name' is a sub-header with one or more sub-nodes, 'type'

For Illustration:

                          'List Page'(t_name)
                               |
               ----------------------------------
               |                                |
           'All St'(h_name)              'Cpns & S'(h_name)
               |                                |
         -------------             --------------------------
         |           |             |            |           |
      's_line', 's_group'     'cpn_group', 'cpn_line_a' 'cpn_line' (type)

Or, simpler

                          'Catg'(t_name)
                             |
                         'Catg Pge'(h_name)
                             |
                        -------------
                        |            |
                   'catg_line', 'catg_group'(type)

It seems clear, to me, that a good data structure for this representation would be
a hash whose key is t_name and whose values are a hash whose key is h_name and
whose value is an array

Example:

{
 "List Page" => {
      "All St"   => ["s_line", "s_group"],
      "Cpns & S" => ["cpn_group", "cpn_line_a", "cpn_line"]
  },
 "Catg"      => {
      "Catg Pge" => ["catg_line", "catg_group"]
  },
 etc,...

}

Here is some code which produces the desired data structure. (I'm sure there are many other approaches)

top_node     = Hash.new
top_sub_node = Hash.new
sql_results.each do |r|

  if top_sub_node[r['h_name']].nil?
    top_sub_node[r['h_name']] = [r['type']]
  else
    top_sub_node[r['h_name']].push r['type']
  end

  if top_node[r['t_name']].nil?
    top_node[ r['t_name'] ] = {r['h_name'] => top_sub_node[r['h_name']]}
  else
    top_node[ r['t_name'] ].merge!({r['h_name'] => top_sub_node[r['h_name']]})
  end
end

require 'pp'
pp top_node
Categories: ruby Tags: , ,

Recreate sql’s ORDER BY (multi column) with perl’s sort

February 22nd, 2009 dwright 1 comment

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

Categories: perl Tags: , , ,