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

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

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


 

評比平均分數： -
In reply to jasper boyd

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

由Paul Holden發表於
Core developers的相片 Moodle HQ的相片 Moodle Workplace team的相片 Particularly helpful Moodlers的相片 Peer reviewers的相片 Plugin developers的相片 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.

評比平均分數：Useful (1)
In reply to jasper boyd

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

由David Mudrák發表於
Core developers的相片 Documentation writers的相片 Moodle HQ的相片 Particularly helpful Moodlers的相片 Peer reviewers的相片 Plugin developers的相片 Plugins guardians的相片 Testers的相片 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.
評比平均分數： -