user-site affiliations

May 04, 2018 16:08

hi, I am trying to retrieve all the users and their site affiliations for a given academic year? I am stuck with retrieving the site names that the users are affiliated with for a given academic year. Any help will be appreciated

Comments

6 comments

Jerome Ohnui May 04, 2018 16:21 (Edited May 04, 2018 16:22)

This is what I built, because it's easier to look at a table that already shows a user's site and role without having to hover over the numbers in the User Management screen:

and here are the fields I used:

The filters are there just to remove certain user names from my report.

0

Comment actions Permalink
Jon Gaydos May 04, 2018 16:26 (Edited May 04, 2018 16:37)

If you want to do this in a SQL query the user role affiliations are stored in public.user_term_role_aff & site affiliations in public.user_term_site_aff. A few additional joins will be required to make any sense out of this though.

0

Comment actions Permalink
Muneiza Ahsan May 07, 2018 22:42

Thanks so much! let me try with some additional joins.

0

Comment actions Permalink
Jimmy Hoang May 09, 2018 16:41 (Edited May 09, 2018 16:49)

If you have users with multiple site affiliation, public.user_term_site_aff might not work.

Here's a SQL query that might be helpful.

SELECT DISTINCT u.user_id, u.first_name, u.last_name, site_id, site_name, academic_year

FROM users AS u
JOIN user_term_role_aff USING (user_id)
JOIN terms USING (term_id)
JOIN sessions USING (session_id)
JOIN sites USING (site_id)

ORDER BY user_id

0

Comment actions Permalink
Franck Reyherme May 09, 2018 16:55

Hey guys,

The `term_info` table could also prove useful.

0

Comment actions Permalink
Muneiza Ahsan July 31, 2018 16:37

Thank you all!

Now I am trying to find out about the permissions that these roles have. Any help would be appreciated.

0

Comment actions Permalink

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?