user-site affiliations



  • Avatar
    Jerome Ohnui (Edited )

    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.

  • Avatar
    Jon Gaydos (Edited )

    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.

  • Avatar
    Muneiza Ahsan

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

  • Avatar
    Jimmy Hoang (Edited )

    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



  • Avatar
    Franck Reyherme

    Hey guys,

    The `term_info` table could also prove useful.  

  • Avatar
    Muneiza Ahsan

    Thank you all!

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

Please sign in to leave a comment.