Forum OpenACS Development: Re: Hierarchical query in Oracle - prvileges tree

Collapse
Posted by Michael Hinds on
Do you know how to get what you want just using acs_privilege_hierarchy? I think you need to give it something to START WITH for it to give you a result set close to what you're looking for.

Can you try this and tell me if it's close:

select 
  tree.tree_level,
  tree.privilege,
  p.privilege as child_privilege
from
  acs_privileges p,
  (select
     level as tree_level,
	 privilege,
	 child_privilege   
   from 
     acs_privilege_hierarchy 
   connect by privilege = prior child_privilege
     start with privilege = 'admin'
  ) tree
where 
  tree.child_privilege(+) = p.privilege
order by tree_level,tree.privilege
Collapse
Posted by Tilmann Singer on
Thanks for the suggestion - this way I don't get the error message anymore, although it doesn't return yet what is needed.

There are possibly more then one root node, so starting with 'admin' is not sufficient. Adding a 'start with privilege in (select ...)' clause that contains all such root nodes is possible.

The problem remains though that the inner query (the one with the CONTAINS) does not return an extra row for the leaf node, so the joined rows from acs_privileges do not appear in the correct order.

This is what your query returns

TREE_LEVEL PRIVILEGE            CHILD_PRIVILEGE
---------- -------------------- --------------------
         1 one_top_level        sub_level
         2 sub_level            sub_sub_level
                                another_top_level
                                one_top_level
This is from a test schema with testing data in it, that contains this:
one_top_level
  |
  +- sub_level
      |
      + sub_sub_level

another_top_level
I modified your query to fit the testing data:
select
  tree.tree_level,
  tree.privilege,
  p.privilege as child_privilege
from
  test_acs_privileges p,
  (select
     level as tree_level,
         privilege,
         child_privilege
   from
     test_acs_privilege_hierarchy
   connect by privilege = prior child_privilege
     start with privilege in ('one_top_level', 'another_top_level')
  ) tree
where
  tree.child_privilege(+) = p.privilege
order by tree_level,tree.privilege