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 -
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

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 -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Plugins guardians Picture of Testers Picture of 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: -