Find a list of catagories where user has a role

Find a list of catagories where user has a role

by Kieran Briggs -
Number of replies: 4

I'm trying to create a report using mysql which when a user logs in it takes their id number and gives them a list of all categories that they Department Head for.  This department head is a role which can only be assigned to a category.  at the moment I've been able to show a list of the user and tie it to the role eg.

SELECT usr.firstname, usr.lastname , role.description, ra.id

FROM mdl_role_assignments as ra

JOIN mdl_user as usr on usr.id = ra.userid

JOIN mdl_context as ctx on ctx.id = ra.contextid

JOIN mdl_role as role on role.id = ra.roleid

WHERE ra.id = 10 and ra.contextid = 77

where 10 is the department head role and context 77 is I think category context. and it gives me something like

Kieran Briggs - Head of Department - 10

but I can't then get the list of departments which contain that person.  

Can anyone help me tie the role to the category please?


Thanks


Kieran Briggs

Average of ratings: -
In reply to Kieran Briggs

Re: Find a list of catagories where user has a role

by Paul Holden -
Picture of Core developers Picture of Moodle HQ Picture of Moodle Workplace team Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

WHERE ra.id = 10 and ra.contextid = 77

This will only match a single context instance (a course category in your case); you probably want to check that ctx.contextlevel = 40 in your where clause to filter all course categories.

Average of ratings: -
In reply to Kieran Briggs

Re: Find a list of catagories where user has a role

by Michael Aherne -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Also, you probably want ra.roleid = 10 in your WHERE clause rather than ra.id - that'll be the ID of a specific role assignment record, which doesn't seem to be what you're looking for.

Average of ratings: -
In reply to Kieran Briggs

Re: Find a list of catagories where user has a role

by Amanda Doughty -
Picture of Core developers Picture of Plugin developers

SELECT usr.firstname, usr.lastname , role.description, ra.id, cc.name

FROM mdl_role_assignments as ra

JOIN mdl_user as usr on usr.id = ra.userid

JOIN mdl_context as ctx on ctx.id = ra.contextid

JOIN mdl_course_categories as cc on ctx.instanceid = cc.id

JOIN mdl_role as role on role.id = ra.roleid

WHERE ra.roleid = 10 and ctx.contextlevel = 40 and usr.id = ?

Average of ratings: -
In reply to Amanda Doughty

Re: Find a list of catagories where user has a role

by Amanda Doughty -
Picture of Core developers Picture of Plugin developers

ra.roleid = 10

Have a look in mdl_roles to get the correct id number for your 'Head of Department' role.

Average of ratings: -