misc software development snippets

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