SQL users with a certain role but some restrictions

SQL users with a certain role but some restrictions

by Federica Marra -
Number of replies: 5

Dear all,

I am working on a SQL code to have a report on the users with a certain system role and members of a cohort.

This is the SQL I am using for the report on the role

SELECT ra.roleid,r.name, u.username, u.firstname, u.lastname, u.email
FROM prefix_role_assignments AS ra
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid

WHERE ra.roleid = 10
I am not a SQL expert so it was already a big win for me to edit this report from the "special roles"!

What shall I add to have other data? I do not understand how can I merge two different tables. I think the "FROM" function is my problem.

If I write 
SELECT ra.roleid,r.name, u.username, u.firstname, u.lastname, u.email, h.idnumber, h.name
FROM prefix_role_assignments AS ra
AND prefix_cohort AS h

JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_user AS u ON hm.userid = u.id

WHERE ra.roleid = 10
I get an error message :/

Thanks in advance!
Average of ratings: Useful (1)
In reply to Federica Marra

Re: SQL users with a certain role but some restrictions

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Federica,
AFAIK a user can be enrolled in several cohorts and this is not related to roles so you can't easily merge https://docs.moodle.org/29/en/ad-hoc_contributed_reports#Cohorts_by_user into the first query.

So a first merging attempt could be:

SELECT
    -- Course.
    c.id, c.fullname, c.shortname, c.idnumber
    -- Role.
    ,ra.roleid, r.name
    -- User.
    ,u.username, u.firstname, u.lastname, u.email
    -- Cohorts.
    ,(
        SELECT GROUP_CONCAT(h.idnumber, h.name ORDER BY h.name SEPARATOR ', ')
        FROM
            prefix_cohort AS h
        JOIN
            prefix_cohort_members AS hm ON h.id = hm.cohortid
        JOIN
            prefix_user AS tmpu ON hm.userid = tmpu.id
        WHERE tmpu.id = u.id
        GROUP BY tmpu.id
    )  AS cohorts
FROM
    prefix_role_assignments AS ra
JOIN
    prefix_role AS r ON r.id = ra.roleid
JOIN
    prefix_user AS u ON u.id = ra.userid
JOIN
    prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
JOIN
    prefix_course AS c ON ctx.instanceid = c.id

   WHERE ra.roleid = 10

Note: you should preserve the Special roles extra joins, to collect also the course name otherwise you miss where the user has that role.

HTH,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: SQL users with a certain role but some restrictions

by Federica Marra -

Hi Matteo,

thanks for your answer. You are always super helpful, especially with the SQL! I'll explain you my case:

- I created special roles to allow certain users to view certain blocks (these roles are valid in system and in blocks)

- the users are added in the roles as soon as they register in the platform

- users are not added automatically in cohorts.

I would need a report to see How many users can view "block A" but are not members of the cohort "block A".
In my idea this would have been possible with the role report and maybe adding the column "cohort" next to  

SELECT ra.roleid,r.name, u.username, u.firstname, u.lastname, u.email

Should the SQL looks more like: 
SELECT
    -- User.
    u.username, u.firstname, u.lastname, u.email
	   -- Role.
    ,ra.roleid, r.name
    -- Cohorts.
    ,(
        SELECT GROUP_CONCAT(h.idnumber, h.name ORDER BY h.name SEPARATOR ', ')
        FROM
            prefix_cohort AS h
        JOIN
            prefix_cohort_members AS hm ON h.id = hm.cohortid
       
    )  AS cohorts
FROM
    prefix_role_assignments AS ra
JOIN
    prefix_role AS r ON r.id = ra.roleid
JOIN
    prefix_user AS u ON u.id = ra.userid
JOIN
    prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 1)
JOIN
    prefix_course AS c ON ctx.instanceid = c.id

   WHERE ra.roleid = 10

Thanks!

Average of ratings: -
In reply to Federica Marra

Re: SQL users with a certain role but some restrictions

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi Federica,
you can omit some fields in the SELECT statement and that's the reason why I've divided (and commented) the scope of the fields but you need to stick with the user table in the GROUP_CONCAT since the enrollment is per cohort and per user: even if you have only one cohort per user, the database design expects each user to be in several cohorts.

Give the SQL code below a try, it should do the trick and if you don't like cohorts you can use cohort for the field name:

SELECT
    -- Course.
    -- c.id, c.fullname, c.shortname, c.idnumber,
    -- User.
    u.username, u.firstname, u.lastname, u.email
    -- Role.
    ,ra.roleid, r.name
    -- Cohorts.
    ,(
        SELECT GROUP_CONCAT(h.idnumber, h.name ORDER BY h.name SEPARATOR ', ')
        FROM
            prefix_cohort AS h
        JOIN
            prefix_cohort_members AS hm ON h.id = hm.cohortid
        JOIN
            prefix_user AS tmpu ON hm.userid = tmpu.id
        WHERE tmpu.id = u.id
        GROUP BY tmpu.id
    )  AS cohorts
FROM
    prefix_role_assignments AS ra
JOIN
    prefix_role AS r ON r.id = ra.roleid
JOIN
    prefix_user AS u ON u.id = ra.userid
JOIN
    prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
JOIN
    prefix_course AS c ON ctx.instanceid = c.id

WHERE ra.roleid = 10

HTH,
Matteo

Average of ratings: Useful (1)
In reply to Matteo Scaramuccia

Re: SQL users with a certain role but some restrictions

by Federica Marra -

Super thanks for the code, Matteo.

Your version was not working: the report was saying "No records found""

But I adapted it a bit and now it is working:

SELECT
    -- User.
    u.username, u.firstname, u.lastname, u.email
    -- Role.
    ,ra.roleid, r.name
    -- Cohorts.
    ,(
        SELECT GROUP_CONCAT(h.idnumber, h.name ORDER BY h.name SEPARATOR ', ')
        FROM
            prefix_cohort AS h
        JOIN
            prefix_cohort_members AS hm ON h.id = hm.cohortid
        JOIN
            prefix_user AS tmpu ON hm.userid = tmpu.id
        WHERE tmpu.id = u.id
        GROUP BY tmpu.id
    )  AS cohorts
FROM
    prefix_role_assignments AS ra
JOIN
    prefix_role AS r ON r.id = ra.roleid
JOIN
    prefix_user AS u ON u.id = ra.userid

WHERE ra.roleid = 10


I think the "context" was tricky because these are all system cohorts and system roles. smile

Average of ratings: Useful (1)
In reply to Federica Marra

Re: SQL users with a certain role but some restrictions

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Happy to read about it!

Reporting has always a great coupling with the instance and the goals i.e. it's not easy to suggest the ready-to-run query w/o stepping into the data of that instance.

Matteo

Average of ratings: -