[Chicago-talk] creating relational keys

[imran javaid]

Using a Tree is likely overkill, but you can build one like this:

my $tree = Tree::Simple->new({id=>0,name=>'USA'}, TreeSimple->ROOT);

# assume @data is your table sorted by ParentID
foreach my $row (@data) {
  ($id, $name, $pid) = @$row;
  $tree->traverse( sub {
    my ($_tree) = @_;
    my $nodeData = $_tree->getNodeValue();
    if ($nodeData->{id} == $pid) {
      $_tree->addChild(Tree::Simple->new({id=>$id, name=>$name});
    }
  });
}

You could define a hash with level information:
my %levels = (-1 => "Country", 0=> "State", 1=> "City", 2=> "Zip");
so in a traverse routine you can tell which level you are on:
print $level{$_tree->getDepth()};

-imran

On Thu, Mar 1, 2012 at 1:14 PM, Jay Strauss <me at heyjay.com> wrote:

> Hi, yes (unfortunately) it all has to go in the same table.
>
> I'm using a vendor supplied data model, with a generic structure like;
>
> ID NAME ParentID
>
> Although, even if I could break it up into separate tables I'd still need
> the keys
>
>
> On Thu, Mar 1, 2012 at 1:11 PM, Chris Hamilton <cjhamil at gmail.com> wrote:
>
>> Just out of curiosity, is there a strong need to put all of this data
>> into the same table?  At least with your example data it seems like
>> the hierarchy is better managed through separate tables for city,
>> state, and zip (with a 1:many relationship from state to city, and
>> then from city to zip).  In that form it would be easy enough to
>> create the ID's and tie them up as you iterate through your hash keys.
>>  Just a thought.
>>
>> -Chris
>>
>> On Thu, Mar 1, 2012 at 12:58 PM, Jay Strauss <me at heyjay.com> wrote:
>> > Imran,
>> >
>> > Thanks.  I'm ok with building a tree, currently I'm using a hash. I just
>> > don't know how to unwind the tree and assign the proper ID and parent
>> ID.
>> >
>> > Jay
>> >
>> >
>> > On Thu, Mar 1, 2012 at 12:51 PM, imran javaid <imranjj at gmail.com>
>> wrote:
>> >>
>> >> One option would be use a tree data structure. Take a look at
>> >> Tree::Simple. You will have USA in the first level, states in the
>> second,
>> >> cities in the third, and zip codes in the 4th (and then whatever else
>> below
>> >> that).
>> >>
>> >> -imran
>> >>
>> >> On Thu, Mar 1, 2012 at 12:40 PM, Jay Strauss <me at heyjay.com> wrote:
>> >>>
>> >>> Hi all,
>> >>>
>> >>> I have some data like:
>> >>> CITY|STATE|ZIP
>> >>> SCHAUMBURG|IL|60194
>> >>> MATTESON|IL|60443
>> >>> WARRENTON|OR|97146
>> >>> MOUNTAIN HOME|AR|72653
>> >>> FORT WORTH|TX|76107
>> >>> CLEVELAND|MS|38732
>> >>> WATERTOWN|SD|57201
>> >>> GRAND CHUTE|WI|54913
>> >>>
>> >>> I want to load it into a relational database in such a way that I have
>> >>> the proper keys to build a hierarchy.
>> >>>
>> >>> so for example:
>> >>> ID Name ParentID
>> >>> 0 USA
>> >>> 1 IL 0
>> >>> 2 SCHAUMBURG 1
>> >>> 3 60194 2
>> >>> 4 MATTESON 1
>> >>> 5 60443 4
>> >>> 6 OR 0
>> >>> 7 WARRENTON 6
>> >>> 8 97146 7
>> >>> ...
>> >>>
>> >>> I'm not sure of a good way to do this.
>> >>>
>> >>> I read the data an built a hash like:
>> >>> USA => {IL => {SCHAUMBURG => {60194 => 0},
>> >>>       MATTESON   => {60443 => 0}},
>> >>> OR => {WARRENTON  => {97146 => 0}}
>> >>> ...
>> >>> };
>> >>>
>> >>> I can't think of a good way to look through it and assign the keys.
>> >>>
>> >>> Maybe someone has done this in the past and has an elegant solution?
>> >>>
>> >>> Thanks
>> >>> Jay
>> >>>
>> >>> _______________________________________________
>> >>> Chicago-talk mailing list
>> >>> Chicago-talk at pm.org
>> >>> http://mail.pm.org/mailman/listinfo/chicago-talk
>> >>
>> >>
>> >>
>> >> _______________________________________________
>> >> Chicago-talk mailing list
>> >> Chicago-talk at pm.org
>> >> http://mail.pm.org/mailman/listinfo/chicago-talk
>> >
>> >
>> >
>> > _______________________________________________
>> > Chicago-talk mailing list
>> > Chicago-talk at pm.org
>> > http://mail.pm.org/mailman/listinfo/chicago-talk
>> _______________________________________________
>> Chicago-talk mailing list
>> Chicago-talk at pm.org
>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>
>
>
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/chicago-talk/attachments/20120301/896f19ff/attachment.html>