Strange Mysql Inconsistancy with role_assignments (Error with $DB->get_records_sql()?)

Strange Mysql Inconsistancy with role_assignments (Error with $DB->get_records_sql()?)

by jasper boyd -
Number of replies: 2

Hey all, I'm developing a block that needs to look up all the role assignments for a course. The query I'm using for this is: 


SELECT ra.roleid, ra.userid

FROM role_assignments AS ra

LEFT OUTER JOIN context AS ctx

ON ctx.id = ra.contextid

LEFT OUTER JOIN course AS c

ON c.id = ctx.instanceid WHERE c.id = " . $course->id; 


So when I do this with a test course id from the mysql command line I get 33 listings which is correct based on the user_enrolments table but when I call this command using $DB->get_records_sql() it only returns 2 or 3 entries the teacher, the TA if there is one and a single student usually the last one in the listings i get from running the sql from the command line.


I can't seem to think of anything that could cause this. I would just use the user_enrollments table but I'd really like to know why this is happening. Especially if its a bug with get_records_sql(), but if that was the case I feel like I'd be having similar problems with other queries and I'm not.


 

Average of ratings: -
In reply to jasper boyd

Re: Strange Mysql Inconsistancy with role_assignments (Error with $DB->get_records_sql()?)

by Paul Holden -
Immàgine de Core developers Immàgine de Moodle HQ Immàgine de Moodle Workplace team Immàgine de Particularly helpful Moodlers Immàgine de Peer reviewers Immàgine de Plugin developers Immàgine de Testers

If you turned on developer debugging you'd probably get a warning about the first column of your query not being unique, in which case you need to add a unique column before your others or use the DML recordset methods.

In this case however, you'd probably be better just using the Moodle API: get_user_roles might be a good place to start.

Average of ratings:Useful (1)
In reply to jasper boyd

Re: Strange Mysql Inconsistancy with role_assignments (Error with $DB->get_records_sql()?)

by David Mudrák -
Immàgine de Core developers Immàgine de Documentation writers Immàgine de Moodle HQ Immàgine de Particularly helpful Moodlers Immàgine de Peer reviewers Immàgine de Plugin developers Immàgine de Plugins guardians Immàgine de Testers Immàgine de Translators
Yes what Paul says. I would personally leave SQL as is and used $DB->get_recordset_sql(). Also note, in Moodle we do not use AS in table aliases statements (only in column aliases) and do not mention OUTER explicitly.
Average of ratings: -