Index: openacs-4/packages/chat-portlet/www/chat-portlet-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/chat-portlet/www/chat-portlet-postgresql.xql,v
diff -u -r1.6 -r1.7
--- openacs-4/packages/chat-portlet/www/chat-portlet-postgresql.xql	1 Oct 2018 17:56:56 -0000	1.6
+++ openacs-4/packages/chat-portlet/www/chat-portlet-postgresql.xql	3 Sep 2024 15:37:36 -0000	1.7
@@ -1,49 +1,39 @@
 <?xml version="1.0"?>
-     
+
 <queryset>
-<rdbms><type>postgresql</type><version>7.1</version></rdbms>
+<rdbms><type>postgresql</type><version>9.6</version></rdbms>
 
 <fullquery name="rooms_list">
   <querytext>
-    select rm.room_id, 
-           rm.pretty_name as pretty_name, 
-           rm.description as description, 
-           rm.moderated_p, 
-           rm.active_p, 
-           rm.archive_p,
-           acs_permission__permission_p(room_id, :user_id, 'chat_room_admin') as admin_p,
-           acs_permission__permission_p(room_id, :user_id, 'chat_read') as user_p,           
-           (select site_node__url(site_nodes.node_id)
-                   from site_nodes
-                   where site_nodes.object_id = obj.context_id) as base_url
-    from chat_rooms rm, 
-         acs_objects obj
-    where rm.room_id = obj.object_id
-          and obj.context_id IN ($sep_package_ids)
-          and rm.active_p = 't'
-    order by rm.pretty_name
+    with user_chat_rooms as (
+        select rm.room_id,
+               rm.pretty_name as pretty_name,
+               acs_object__name(apm_package__parent_id(obj.context_id)) as parent_name,
+               rm.description as description,
+               rm.active_p,
+               rm.archive_p,
+               acs_permission.permission_p(room_id, :user_id, 'chat_room_admin') as admin_p,
+               acs_permission.permission_p(room_id, :user_id, 'chat_ban') as banned_p,
+               obj.context_id
+          from chat_rooms rm,
+               acs_objects obj
+         where rm.room_id = obj.object_id
+           and obj.context_id IN ([ns_dbquotelist $config(package_id)])
+           and rm.active_p = 't'
+    )
+    select *
+      from user_chat_rooms u,
+           (select distinct orig_object_id
+              from acs_permission.permission_p_recursive_array(
+                      array(select room_id from user_chat_rooms),
+                      :user_id,
+                      'chat_read'
+                   )
+           ) as chat_rooms_read
+     where chat_rooms_read.orig_object_id = u.room_id
+       and u.banned_p = 'f'
+    order by parent_name, lower(pretty_name)
   </querytext>
 </fullquery>
 
-<fullquery name="rooms_list_all">
-  <querytext>
-    select rm.room_id, 
-           rm.pretty_name as pretty_name, 
-           rm.description as description, 
-           rm.moderated_p, 
-           rm.active_p, 
-           rm.archive_p,
-           acs_permission__permission_p(room_id, :user_id, 'chat_room_admin') as admin_p,
-           acs_permission__permission_p(room_id, :user_id, 'chat_read') as user_p,           
-           (select site_node__url(site_nodes.node_id)
-                   from site_nodes
-                   where site_nodes.object_id = obj.context_id) as base_url
-    from chat_rooms rm, 
-         acs_objects obj
-    where rm.room_id = obj.object_id and rm.active_p = 't'
-    order by rm.pretty_name
-  </querytext>
-</fullquery>
-
 </queryset>
-