Forum OpenACS Development: Re: Need some help with tree_sortkey

Collapse
Posted by Tom Jackson on

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"
Collapse
Posted by Tom Jackson on

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.

Collapse
Posted by Mark Aufflick on
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?
Collapse
Posted by Tom Jackson on

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.