Re: Hierarchal data

On Fri, Jan 23, 2004 at 12:32:49AM -0600, Douglas Trainor wrote:
> You have a fundamental problem if you want to go from high-level
> to low-level if you only store the parent_id (from low-level to
> high-level).
> [in a booming voice]:  Feed your head.  Good luck.

Well, I think you can do it, but it's really ugly.

Keeping with the theme, say you have a path like:
   /California/History/Sixties

Then something like:

SELECT t0.topic_id FROM
    topic t0,
    topic t1,
    topic t2
    WHERE
        t0.name LIKE 'Sixties' AND t0.parent = t1.topic_id AND
        t1.name LIKE 'History' AND t1.parent = t2.topic_id AND
        t2.name LIKE 'California' and t2.parent = 0;  -- top level


> In any case, throw an example on paper and see why your scheme
> will not work.  You need a better reference than SITEPOINT for
> what you want to do... What they say does not apply to you.

I'm not sure I follow.  You are talking about this link, right?

> > http://www.sitepoint.com/article/1105/1

It's PHP, but in the section "The Path to a Node" they show the
recursive method of finding the path from a node to the root.  That's
1/2 of what I need, although I'm wondering if I can get Postgresql to do
the recursion for me.  So, I'm not clear why you say it will not work.

In Oracle someone suggested:

select stuff from node
  start with id = $node_id
  connect by prior parentId = id;

I had a better reference -- an article by Joe Celko linked on the bottom
of that sitepoint article.  But that article now requires registration.

Both of those articles are recommending the preorder tree method, but
I'm trying to figure out if I can use the method above, but some way
that's more efficient.

The tree won't change very often so I'm thinking of just doing the node
to path conversions once and cache those mappings.


--
Bill Moseley
moseley@hank.org