Index: openacs-4/packages/acs-tcl/tcl/site-nodes-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/site-nodes-procs-postgresql.xql,v
diff -u -r1.23 -r1.23.2.1
--- openacs-4/packages/acs-tcl/tcl/site-nodes-procs-postgresql.xql	31 Oct 2018 14:09:24 -0000	1.23
+++ openacs-4/packages/acs-tcl/tcl/site-nodes-procs-postgresql.xql	23 Oct 2020 16:20:41 -0000	1.23.2.1
@@ -10,22 +10,25 @@
     </fullquery>
 
     <fullquery name="site_node::update_cache.select_child_site_nodes">
-        <querytext>
-	  with recursive site_node_tree AS (
-	     select node_id, parent_id, name, directory_p, pattern_p, object_id
-	     from site_nodes where node_id = :node_id
-	  union all
-	     select c.node_id, c.parent_id, c.name, c.directory_p, c.pattern_p, c.object_id
-	     from site_node_tree tree, site_nodes c
-	     where  c.parent_id = tree.node_id
-	  )
-	  select
-	     t.node_id, t.parent_id, t.name, t.directory_p, t.pattern_p, t.object_id,
-	     p.package_key, p.package_id, p.instance_name, pt.package_type
-	  from   site_node_tree t, apm_packages p, apm_package_types pt
-	  where  pt.package_key = p.package_key
-	  and    t.object_id = p.package_id
-        </querytext>
+      <querytext>
+        with recursive site_node_tree as (
+           select node_id, parent_id, name, directory_p, pattern_p,
+                  object_id, 1 as tdepth
+           from site_nodes where node_id = :node_id
+         union all
+           select c.node_id, c.parent_id, c.name, c.directory_p,
+                  c.pattern_p, c.object_id, tree.tdepth + 1 as tdepth
+           from site_node_tree tree, site_nodes c
+           where c.parent_id = tree.node_id
+        )
+        select t.node_id, t.parent_id, t.name, t.directory_p,
+               t.pattern_p, t.object_id, t.tdepth,
+               p.package_key, p.package_id, p.instance_name, pt.package_type
+        from site_node_tree t
+        join apm_packages p on (t.object_id = p.package_id)
+        join apm_package_types pt on (pt.package_key = p.package_key)
+        order by tdepth, node_id
+      </querytext>
     </fullquery>
 
     <fullquery name="site_node::update_cache.select_site_node">