Forum OpenACS Development: Need some help with tree

Posted by Jade Rubick on 02/07/04 12:06 AM

I'm trying to figure out how to use tree_sortkey on a table that isn't using the content repository, but just acs_objects.

tree_sortkey isn't too hard when using the content_repository, because the parent_id is already in the cr_items table.

But when using acs_objects, I don't see any such thing.

That confuses me, because tree_sortkey is in acs_objects. Am I supposed to use the context_id from parent_id?

So if I create a table foo that is foreign key acs_objects, how do I use tree_sortkey?

create table foo (
my_key integer references acs_objects
parent_id integer references foo
);

???

And how would I make a tree_sortkey query using that?

If someone could help me out, I'd very much appreciate it. If I have time, I'll even add something on tree_sortkey to the main docs.

2: Re: Need some help with tree_sortkey (response to 1)

Posted by Tom Jackson on 02/07/04 12:23 AM

Jade,

I have a query that does that. You do use the context_id, but of course, you have to set the context_id to establish the hierarchy. Let me look up the query, but you can see it a t work at iunicycle.com, the categories have prefix dots indicating the depth.

3: Re: Need some help with tree_sortkey (response to 1)

Posted by Jade Rubick on 02/07/04 01:22 AM

Hmmm, so that is a legitimate use of context_id? Interesting.

I would appreciate a sample, if you didn't mind digging it up.

Otherwise, I'll start coding using context_id.

Thanks, Tom!

4: Re: Need some help with tree_sortkey (response to 1)

Posted by Tom Jackson on 02/07/04 04:27 AM

Jade, here is the query:

db_multirow categories blog_categories "
select
 c.*,
 o.context_id,
 lpad('.',(tree_level(o.tree_sortkey) - 4), '.') as indent
from
 blog_categories c,
 acs_objects o
where
 c.category_id = o.object_id
and
(
 c.public_p = 't'
or
 c.party_id = :user_id
)
order by
 o.tree_sortkey"

5: Re: Need some help with tree_sortkey (response to 4)

Posted by Tom Jackson on 02/07/04 04:34 AM

Wow, sorry for the delay, we were without power for about four hours! Lightning struck. My laptop and routers, etc. were fine, but I guess the cable company ran out of power.

Anyway, I guess one purpose of tree-sortkey is to provide order and hierarchy in a single table design. It is expensive to maintain, but then, hopefully the more numerous selects will be very fast.

6: Re: Need some help with tree_sortkey (response to 5)

Posted by Mark Aufflick on 02/08/04 04:30 AM

But surely you can't guarantee that context_id provides that info in general - an object's context doesn't have to be it's parent - or did we agree to standardise on that?

7: Re: Need some help with tree_sortkey (response to 6)

Posted by Tom Jackson on 02/08/04 05:01 AM

The only question I was trying to answer was how to use the tree_sortkey in the acs_objects table. I'm not guaranteeing anything.

8: Re: Need some help with tree_sortkey (response to 1)

Posted by Jun Yamog on 02/08/04 02:12 PM

Hi Jade,

Usage of tree_sortkey either acs_objects or cr_items are the same, or any other table that uses tree_sortkey. As given by Tom as example you will see its the same usage. You can use other queries in CR or other parts as samples.

9: Re: Need some help with tree_sortkey (response to 1)

Posted by Don Baccus on 02/09/04 05:12 PM

I guess one purpose of tree-sortkey is to provide order and hierarchy in a single table design.

Well, when we inherited ACS 4.2 it was chock full of queries like "find me all the children of ...", implemented by CONNECT BY queries in Oracle. There's no such facility in Postgres. The tree_sortkey approach allows us to ask such parent-child questions in a single query, that can't be done in PG using a single "parent_id"-style column. It is more efficient than joining against a hierarchy table. It is also more efficient than the nested set approach ... anyway, we needed to be able to write such parent-child queries so needed SOME solution. tree_sortkeys are very fast on the SELECT side, slower to maintain.

Regarding context_id, yes, it has been used as a surrogate for not having a real parent_id, and some time back we agreed that it was "OK". I would like to change that someday, adding a real parent_id and removing the CR's then-redundant parent_id but that's a discussion for another day ...

10: Re: Need some help with tree_sortkey (response to 1)

Posted by Jade Rubick on 02/09/04 08:17 PM

Thank you all for your comments and help. As a way of saying thank you, I'm writing up how to use tree-sortkey in the advanced tutorial section.

I'd put it somewhere else, but I haven't figured out how to make a new page in the documentation yet.

(I'm really doing this because I want to make sure I understand the best way to do this, and so that I have something to refer to in the future...)

11: Re: Need some help with tree_sortkey (response to 1)

Posted by Jade Rubick on 02/12/04 08:19 PM

I added a section to the documentation that details how to use tree_sortkey with acs_objects to do hierarchical queries. I took snippets provided by Tom and Don and explain how to use context_id and make common queries, such as finding children, etc...

I'm now facing a situation where I have a table that is not a subclass of acs_objects. I face the choice of either adding in tree_sortkey support (which sounds like a lot of work, and uncertain territory), or adding it as an acs_object (which isn't really necessary for this table).

Would anybody who has done this before describe how they did it? Or should I just go ahead and make it into an acs_object? I only have a few hours to spend on this aspect of it, but unfortunately, it looks like it will take a few hours to do either way... :(

Forum OpenACS Development: Need some help with tree_sortkey