Using a Tree is likely overkill, but you can build one like this:<div><br><div>my $tree = Tree::Simple->new({id=>0,name=>'USA'}, TreeSimple->ROOT);</div><div><br></div><div># assume @data is your table sorted by ParentID</div>
<div>foreach my $row (@data) {</div><div> ($id, $name, $pid) = @$row;</div><div> $tree->traverse( sub {</div><div> my ($_tree) = @_;</div><div> my $nodeData = $_tree->getNodeValue();</div><div> if ($nodeData->{id} == $pid) {</div>
<div> $_tree->addChild(Tree::Simple->new({id=>$id, name=>$name});</div><div> }</div><div> });</div><div>}<br><div><br></div><div>You could define a hash with level information:</div><div>my %levels = (-1 => "Country", 0=> "State", 1=> "City", 2=> "Zip");</div>
<div>so in a traverse routine you can tell which level you are on:</div><div>print $level{$_tree->getDepth()};</div><div><br></div><div>-imran<br><br><div class="gmail_quote">On Thu, Mar 1, 2012 at 1:14 PM, Jay Strauss <span dir="ltr"><<a href="mailto:me@heyjay.com">me@heyjay.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi, yes (unfortunately) it all has to go in the same table. <div><br></div><div>I'm using a vendor supplied data model, with a generic structure like;<br>
<br>ID NAME ParentID</div><div><br></div><div>Although, even if I could break it up into separate tables I'd still need the keys<div><div class="h5"><br>
<br><div class="gmail_quote">On Thu, Mar 1, 2012 at 1:11 PM, Chris Hamilton <span dir="ltr"><<a href="mailto:cjhamil@gmail.com" target="_blank">cjhamil@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Just out of curiosity, is there a strong need to put all of this data<br>
into the same table? At least with your example data it seems like<br>
the hierarchy is better managed through separate tables for city,<br>
state, and zip (with a 1:many relationship from state to city, and<br>
then from city to zip). In that form it would be easy enough to<br>
create the ID's and tie them up as you iterate through your hash keys.<br>
Just a thought.<br>
<span><font color="#888888"><br>
-Chris<br>
</font></span><div><div><br>
On Thu, Mar 1, 2012 at 12:58 PM, Jay Strauss <<a href="mailto:me@heyjay.com" target="_blank">me@heyjay.com</a>> wrote:<br>
> Imran,<br>
><br>
> Thanks. I'm ok with building a tree, currently I'm using a hash. I just<br>
> don't know how to unwind the tree and assign the proper ID and parent ID.<br>
><br>
> Jay<br>
><br>
><br>
> On Thu, Mar 1, 2012 at 12:51 PM, imran javaid <<a href="mailto:imranjj@gmail.com" target="_blank">imranjj@gmail.com</a>> wrote:<br>
>><br>
>> One option would be use a tree data structure. Take a look at<br>
>> Tree::Simple. You will have USA in the first level, states in the second,<br>
>> cities in the third, and zip codes in the 4th (and then whatever else below<br>
>> that).<br>
>><br>
>> -imran<br>
>><br>
>> On Thu, Mar 1, 2012 at 12:40 PM, Jay Strauss <<a href="mailto:me@heyjay.com" target="_blank">me@heyjay.com</a>> wrote:<br>
>>><br>
>>> Hi all,<br>
>>><br>
>>> I have some data like:<br>
>>> CITY|STATE|ZIP<br>
>>> SCHAUMBURG|IL|60194<br>
>>> MATTESON|IL|60443<br>
>>> WARRENTON|OR|97146<br>
>>> MOUNTAIN HOME|AR|72653<br>
>>> FORT WORTH|TX|76107<br>
>>> CLEVELAND|MS|38732<br>
>>> WATERTOWN|SD|57201<br>
>>> GRAND CHUTE|WI|54913<br>
>>><br>
>>> I want to load it into a relational database in such a way that I have<br>
>>> the proper keys to build a hierarchy.<br>
>>><br>
>>> so for example:<br>
>>> ID Name ParentID<br>
>>> 0 USA<br>
>>> 1 IL 0<br>
>>> 2 SCHAUMBURG 1<br>
>>> 3 60194 2<br>
>>> 4 MATTESON 1<br>
>>> 5 60443 4<br>
>>> 6 OR 0<br>
>>> 7 WARRENTON 6<br>
>>> 8 97146 7<br>
>>> ...<br>
>>><br>
>>> I'm not sure of a good way to do this.<br>
>>><br>
>>> I read the data an built a hash like:<br>
>>> USA => {IL => {SCHAUMBURG => {60194 => 0},<br>
>>> MATTESON => {60443 => 0}},<br>
>>> OR => {WARRENTON => {97146 => 0}}<br>
>>> ...<br>
>>> };<br>
>>><br>
>>> I can't think of a good way to look through it and assign the keys.<br>
>>><br>
>>> Maybe someone has done this in the past and has an elegant solution?<br>
>>><br>
>>> Thanks<br>
>>> Jay<br>
>>><br>
>>> _______________________________________________<br>
>>> Chicago-talk mailing list<br>
>>> <a href="mailto:Chicago-talk@pm.org" target="_blank">Chicago-talk@pm.org</a><br>
>>> <a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br>
>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> Chicago-talk mailing list<br>
>> <a href="mailto:Chicago-talk@pm.org" target="_blank">Chicago-talk@pm.org</a><br>
>> <a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br>
><br>
><br>
><br>
> _______________________________________________<br>
> Chicago-talk mailing list<br>
> <a href="mailto:Chicago-talk@pm.org" target="_blank">Chicago-talk@pm.org</a><br>
> <a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br>
_______________________________________________<br>
Chicago-talk mailing list<br>
<a href="mailto:Chicago-talk@pm.org" target="_blank">Chicago-talk@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br>
</div></div></blockquote></div><br></div></div></div>
<br>_______________________________________________<br>
Chicago-talk mailing list<br>
<a href="mailto:Chicago-talk@pm.org">Chicago-talk@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/chicago-talk" target="_blank">http://mail.pm.org/mailman/listinfo/chicago-talk</a><br></blockquote></div><br></div></div></div>