Home

Selecting users,groups and site from database

You are here

2 posts / 0 new
Last post
Selecting users,groups and site from database

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;

4.2
test

test

forums index