Selecting users,groups and site from database

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

svetlingulev

Hi,
I would like to select users,in which groups they are, and their sites.. I wan it with a single postgre sql query.

I've found  these queries, but i want them combined with join if possible.

select * from alf_permission

select * from ALF_AUTHORITY 

select * from ALF_CHILD_ASSOC where CHILD_NODE_NAME like ‘group%’

select * from ALF_CHILD_ASSOC where QNAME_LOCALNAME like ‘GROUP%’



select
    node_id,
    string_agg(string_value, ',')
from (
   select
      node_id,
      qname_id,
      (select local_name from alf_qname where id = qname_id) as qname_type,
      string_value
   from alf_node_properties
   where node_id in (
      select id from alf_node 
      where type_qname_id = (
         select id from alf_qname where local_name = 'person'
      )
      and qname_id in (
         select id
         from  alf_qname 
         where local_name in (
            'username',
            'firstName',
            'lastName',
            'email'      
         )
      )
   )
) alf_users
group by node_id;

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

svetlingulev

test