I think we need to make a new function in accesslib that will allow us to LEFT JOIN data from other tables to a set of records of the users who have a capability in a module context.
I'm working on the quiz reports and we need a way to fetch quiz attempt data for all 'students' who have or have not attempted a quiz. The definition of 'student' ie. someone who is expected to take the quiz is a user with the mod/quiz:attempt capability in the module context.
I think to use get_users_by_capability to get the user ids and then to use WHERE u.id IN($useridlist) will not work well for courses with many students enrolled as then the SQL query will include a couple of thousand userids or more.
So I'm wondering if accesslib experts think it is a good idea to have a way to pass SQL to a function similar to get_users_by_capability to be able to JOIN or LEFT JOIN data from the db to the results returned by the db queries necessary to fetch the list of users from the db with a certain capability?
I just did a search through Moodle HEAD and it seems that generally modules are using a imploded list of userids returned from get_users_by_capability to get data for the subset of users with a 'student' capability in the activity context.
accesslib question : is there no way to do a db JOIN to fetch all data for 'students' in an activity
by Jamie Pratt -
Number of replies: 4
In reply to Jamie Pratt
Re: accesslib question : is there no way to do a db JOIN to fetch all data for 'students' in an activity
by Jamie Pratt -
For some background see : http://tracker.moodle.org/browse/MDL-7772
In reply to Jamie Pratt
Re: accesslib question : is there no way to do a db JOIN to fetch all data for 'students' in an activity
by Tim Hunt -
Don't we just need one extra optional paramter to get_users_by_capability, of the form
$extratables = array(
'quiz_grades' => array('qg', 'u.id = qg.userid')
// etc.
);
And this adds
LEFT JOIN mdl_quiz_grades qg ON u.id = qg.userid
To the SELECT statement. (And you can add multiple tables this way if you need to.)
There is already a $fields argument.
Or is it actually easier to have an $extratablessql field, so you can pass 'LEFT JOIN mdl_quiz_grades qg ON u.id = qg.userid' - but you can do other sorts of join too, if you want.
$extratables = array(
'quiz_grades' => array('qg', 'u.id = qg.userid')
// etc.
);
And this adds
LEFT JOIN mdl_quiz_grades qg ON u.id = qg.userid
To the SELECT statement. (And you can add multiple tables this way if you need to.)
There is already a $fields argument.
Or is it actually easier to have an $extratablessql field, so you can pass 'LEFT JOIN mdl_quiz_grades qg ON u.id = qg.userid' - but you can do other sorts of join too, if you want.
In reply to Tim Hunt
Re: accesslib question : is there no way to do a db JOIN to fetch all data for 'students' in an activity
by Jamie Pratt -
Hmm. It seems that in the case that there are any relevant negative capability assignments for a role then get_users_by_capability has to load all the data into memory anyway, calculation of users with a permission in this case cannot be done in SQL.
It seems that since many modules' code already uses an IN() clause with a list of user ids a decision has already been made that we can live with the limit that this will put on the number of enrollments we can have in a course?
I wonder if another solution might be to have a different function get_users_without_capability then modules could use a simple sql query to fetch all users with a role assignment in or inherited in a context and exclude all users without the relevant capability?
It seems that since many modules' code already uses an IN() clause with a list of user ids a decision has already been made that we can live with the limit that this will put on the number of enrollments we can have in a course?
I wonder if another solution might be to have a different function get_users_without_capability then modules could use a simple sql query to fetch all users with a role assignment in or inherited in a context and exclude all users without the relevant capability?
In reply to Jamie Pratt
Re: accesslib question : is there no way to do a db JOIN to fetch all data for 'students' in an activity
by Jamie Pratt -
Would be cool if get_context_users_by_cap was up to the job of fetching all users with permissions as the code is relatively simple and I would think could be easily extended to fetch other data as well from the db. See my post here about get_context_users_by_cap.