Forum OpenACS Development: Help needed on porting outer joins in static-pages
Posted by Dave Bauer on 07/02/01 07:47 PM
I am working on porting static pages and I came up against the following query in commentability.xql:
FIX ME DECODE (USE SQL92 CASE) select spf.folder_id, decode (spf.folder_id,:root_folder_id,'[acs_root_dir]/www/',content_item.get_ title(spf.folder_id)||'/') as folder_name, static_page.five_n_spaces(lev) as spaces, static_page_id, substr(filename,instr(filename,'/',-1)+1) as filename, decode(p_file.grantee_id,NULL,'not commentable','commentable') as file_permission, decode(show_comments_p,'t','comments displayed','comments summarized') as display_policy, decode(p_folder.grantee_id,NULL,'children not commentable','children commentable') as folder_permission from static_pages sp, (select folder_id,level as lev from sp_folders start with folder_id = :root_folder_id connect by parent_id = prior folder_id) spf, acs_permissions p_file, acs_permissions p_folder where spf.folder_id=sp.folder_id(+) and p_file.grantee_id(+) = acs.magic_object_id('the_public') and p_file.privilege(+) = 'general_comments_create' and p_file.object_id(+) = static_page_id and p_folder.grantee_id(+) = acs.magic_object_id('the_public') and p_folder.privilege(+) = 'general_comments_create' and p_folder.object_id(+) = spf.folder_idI added a tree_sortkey column and triggers to sp_folders and ported the connect by and also ported the decodes. The outer joins are making my head spin. Any help is appreciated!
2: Response to Help needed on porting outer joins in static-pages (response to 1)
Posted by Dan Wickstrom on 07/02/01 08:29 PM
It should look something like the following:
select spf.folder_id, ... from ((static_pages sp RIGHT OUTER JOIN (select folder_id,level as lev from sp_folders start with folder_id = :root_folder_id connect by parent_id = prior folder_id) spf ON spf.folder_id = sp.folder_id) LEFT OUTER JOIN acs_permissions p_file ON (p_file.grantee_id = acs__magic_object_id('the_public') and p_file.privilege = 'general_comments_create' and p_file.object_id = sp.static_page_id)) LEFT OUTER JOIN acs_permissions p_folder ON (p_folder.grantee_id = acs__magic_object_id('the_public') and p_folder.privilege = 'general_comments_create' and p_folder.object_id = spf.folder_id)