Index: openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql	7 Aug 2017 23:47:58 -0000	1.3
+++ openacs-4/packages/acs-subsite/www/admin/applications/index-postgresql.xql	21 Oct 2018 17:36:23 -0000	1.4
@@ -5,50 +5,47 @@
 
 <fullquery name="select_applications">      
       <querytext>
-
-    select n.node_id
-    from   site_nodes n,
-           site_nodes np,
-           apm_packages p
-	   left outer join lang_messages m
-	     on m.locale = :locale and
-  	        '#' || m.package_key || '.' || m.message_key || '#' = p.instance_name
-           left outer join lang_messages md
-	     on m.locale = 'en_US' and
-  	        '#' || md.package_key || '.' || md.message_key || '#' = p.instance_name,
-	   apm_package_types pt
-    where  np.node_id = :subsite_node_id
-    and    n.tree_sortkey between np.tree_sortkey and tree_right(np.tree_sortkey)
-    and    p.package_id = n.object_id
-    and    pt.package_key = p.package_key
-    [template::list::filter_where_clauses -and -name applications]
-    order  by n.tree_sortkey
-
+    select node_id from (
+       WITH RECURSIVE site_node_tree AS (
+         select node_id, parent_id, name, object_id from site_nodes where node_id = :subsite_node_id
+       UNION ALL
+         select c.node_id, c.parent_id, c.name, c.object_id from site_node_tree tree, site_nodes as c
+         where  c.parent_id = tree.node_id
+       )
+       select n.node_id, n.parent_id, n.name, site_node__url(n.node_id) as url
+       from   site_node_tree n, apm_packages p
+	      left outer join lang_messages m
+	        on m.locale = 'en_US' and
+  	           '#' || m.package_key || '.' || m.message_key || '#' = p.instance_name
+              left outer join lang_messages md
+	        on m.locale = 'en_US' and
+  	           '#' || md.package_key || '.' || md.message_key || '#' = p.instance_name,
+	      apm_package_types pt
+       where  p.package_id = n.object_id
+       and    pt.package_key = p.package_key
+       [template::list::filter_where_clauses -and -name applications]
+       order  by url) node_tree
       </querytext>
 </fullquery>
 
 <fullquery name="select_applications_page">      
       <querytext>
-
-    select n.node_id, 
-           n.name, 
-           p.package_id,
-           p.instance_name,
-           tree_level(n.tree_sortkey) - tree_level(np.tree_sortkey) as treelevel,
-           pt.pretty_name as package_pretty_name,
-           exists (select 1 from apm_parameters
-	            where package_key = pt.package_key) as parameters_p
-    from   site_nodes n,
-           site_nodes np,
-           apm_packages p,
-           apm_package_types pt
-    where  np.node_id = :subsite_node_id
-    and    n.tree_sortkey between np.tree_sortkey and tree_right(np.tree_sortkey)
-    and    p.package_id = n.object_id
-    and    pt.package_key = p.package_key
-    and [template::list::page_where_clause -name applications -key n.node_id]
-    order  by n.tree_sortkey
-
+    select
+       node_id, name, package_id, instance_name, package_pretty_name, parameters_p,
+       (char_length(url)-char_length(replace(url, '/', ''))-1) as treelevel
+    from (
+       select n.node_id, 
+              n.name, 
+              p.package_id,
+              p.instance_name,
+              site_node__url(n.node_id) as url,
+              pt.pretty_name as package_pretty_name,
+              exists (select 1 from apm_parameters where package_key = pt.package_key) as parameters_p
+       from   site_nodes n, apm_packages p, apm_package_types pt
+       where  p.package_id = n.object_id
+       and    pt.package_key = p.package_key
+       and    [template::list::page_where_clause -name applications -key n.node_id]
+    ) sm0 order by url
       </querytext>
 </fullquery>