user-site affiliations

Follow

Muneiza Ahsan

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

0

• 

  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

  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

  Permalink
• 

  Muneiza Ahsan May 07, 2018 22:42

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

  0

  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

  Permalink
• 

  Franck Reyherme May 09, 2018 16:55

  Hey guys,

  The `term_info` table could also prove useful.  

  0

  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

  Permalink

Please sign in to leave a comment.