AnsweredAssumed Answered

Selecting users,groups and site from database

Question asked by svetlingulev on Jun 8, 2014
Latest reply on Jun 8, 2014 by 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;

Outcomes