Forum .LRN Q&A: How can I find out what community_id an acs_object belongs to?

Collapse
1: How can I find out what community_id an acs_object belongs to?
Posted by Cathy Sarisky on 10/11/04 03:31 AM
Hi,

I've got some miscellaneous object_ids.  Some are packages, some are several levels descended from the package_id.  Before I re-invent the wheel, is there an existing proc to find the dotlrn community_id for a random object_id (that's perhaps a great-great grandchild of the community_id)?

Otherwise, I'm thinking I need to do something recursive to pull context_id from acs_objects until I hit something that's a community?

Collapse
2: Re: How can I find out what community_id an acs_object belongs to? (response to 1)
Posted by Deds Castillo on 10/11/04 04:56 AM
Cathy,

No need to traverse, you can do something like 

select o2.object_id,
       o2.object_type, 
       x.object_id as object_id_two, 
       x.object_type as object_type_two
from acs_objects o2,
     (select o.object_id,
      tree_ancestor_keys(o.tree_sortkey) as ancestor_key,
      o.object_type 
      from acs_objects o 
      where o.object_id = :object_id) x 
where o2.tree_sortkey = x.ancestor_key 
      and o2.object_id in (select community_id 
                           from dotlrn_communities_all)

Of course you can stil tweak that to optimize stuff but the idea is there.