Forum OpenACS Development: Objects data model
Well, I'm having a little trouble right now. I'm working in the user-tracking package, where I want to show the community_id of the most visited objects. So, what I have to do is find some relationship between the object_id and the community_id.
Looking over the tables, I foud the context_id in the acs_objects. I thought that the context_id should be the same as the package_id of the community, but it's not, and I can't find any other relations about them. Can anybody tell me what's the relation about the object's context_id and the community's package_id? How can I build a query that returns me the community_id for the given object_id?
You could then write a quick function to check the package_id in acs-objects and see to which community the package_id belongs to.
There is no direct connection between context_id and package_id or community_id.
The problem here is about the performance. I'm doing recursive searchs in a huge table like acs_objects, and this is not a nice thing to do. What I could find out is that I can improve the performance of my query using the tree_sortkey. However, I found another problem here: it just doesn't work for some objects. For forums it works, but for other objects, like file-storage folders it does not work. It seems like the tree_sortkey of the file-storage object is related to its parent folder, but it never gets to the community. So, I'm out of ideas to improve the performance. Any idea?
Take the package_id of the object, find the site_node for it, then use that tcl procedure to search the site map. The site map is an array stored in ram so it can peform the search much more quickly than querying each step from the database.
Also you should understand how to do a tree query in your database. See Hierarchal Data https://openacs.org/doc/openacs-5-0-4/tutorial-advanced.html