Forum: help

Monitor Forum | Start New Thread Start New Thread
RE: Query of project admins [ Reply ]
By: Chanh TRAN on 2019-10-15 11:49
Hi Franck,

Long time no see ;)
It works !!!

Thx again

Take care,

RE: Query of project admins [ Reply ]
By: Franck Villaume on 2019-10-15 10:18

You may try this:

select user_id, pfo_user_role.role_id, home_group_id from pfo_user_role, pfo_role_setting, pfo_role where pfo_user_role.role_id = pfo_role_setting.role_id and pfo_role.role_id = pfo_user_role.role_id and section_name = 'project_admin' and perm_val = 1;

You will get: all user_id with role_id who has project_admin rights and the attached group_id of the role.

RE: Query of project admins [ Reply ]
By: Chanh TRAN on 2019-10-14 13:59
Hi all,

I'm running FF 5.3.4 & would really appreciate if someone could give me a compatible query for my verson

Big Thx in advance

RE: Query of project admins [ Reply ]
By: Markus Köhl on 2010-06-23 08:17
We used this statement some time ago to generate a list of active users containing the realname, loginname, e-mail adress and a comma seperated list of active projects from the project administrators.

SELECT DISTINCT u.realname, u.user_name,,
array_to_string(array(SELECT g.group_name
FROM groups g
INNER JOIN user_group ON g.group_id = user_group.group_id
WHERE user_group.user_id = u.user_id
AND g.status = 'A'), ', ') as projectname
FROM users u
INNER JOIN user_group ug ON u.user_id = ug.user_id
INNER JOIN role r ON ug.role_id = r.role_id
WHERE ug.admin_flags = 'A'
AND u.status = 'A'

kind regards

RE: Query of project admins [ Reply ]
By: Luis Daniel Ibáñez on 2010-06-22 19:07
AFAIK, there is no instructions to DB Schema. I used PGDesigner to reverse engineer the database in a model, but i'm still ordering the 161 tables into something understandable xD.

Anyway, it seems that the table that holds that info is 'user_group', and the property is "admin_flags" if equals 'A', then you have a group administrator, now you should join that with the groups table (A group equals a project)

Best Regards

Query of project admins [ Reply ]
By: Weiming Li on 2010-04-21 06:01

Anyone know how to get a list of all project admins by using SQL command? Is there any instruction/guidelines for DB schema? Thanks