Find a list of catagories where user has a role

Find a list of catagories where user has a role

por 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

por Paul Holden -
Imaxe de Core developers Imaxe de Moodle HQ Imaxe de Moodle Workplace team Imaxe de Particularly helpful Moodlers Imaxe de Peer reviewers Imaxe de Plugin developers Imaxe de 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

por Michael Aherne -
Imaxe de Core developers Imaxe de Particularly helpful Moodlers Imaxe de Peer reviewers Imaxe de 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

por Amanda Doughty -
Imaxe de Core developers Imaxe de 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

por Amanda Doughty -
Imaxe de Core developers Imaxe de 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: -