}
/**
- * Get sql and parameters that will return user ids for a group
+ * Get sql and parameters that will return user ids for a group or groups
*
- * @param int $groupid
+ * @param int|array $groupids Where this is an array of multiple groups, it will match on members of any of the groups
* @param context $context Course context or a context within a course. Mandatory when $groupid = USERSWITHOUTGROUP
* @return array($sql, $params)
* @throws coding_exception if empty or invalid context submitted when $groupid = USERSWITHOUTGROUP
*/
-function groups_get_members_ids_sql($groupid, context $context = null) {
- $groupjoin = groups_get_members_join($groupid, 'u.id', $context);
+function groups_get_members_ids_sql($groupids, context $context = null) {
+ if (!is_array($groupids)) {
+ $groupids = [$groupids];
+ }
+
+ $groupjoin = groups_get_members_join($groupids, 'u.id', $context);
$sql = "SELECT DISTINCT u.id
FROM {user} u
/**
* Get sql join to return users in a group
*
- * @param int $groupid The groupid, 0 means all groups and USERSWITHOUTGROUP no group
+ * @param int|array $groupids The groupids, 0 or [] means all groups and USERSWITHOUTGROUP no group
* @param string $useridcolumn The column of the user id from the calling SQL, e.g. u.id
- * @param context $context Course context or a context within a course. Mandatory when $groupid = USERSWITHOUTGROUP
+ * @param context $context Course context or a context within a course. Mandatory when $groupids includes USERSWITHOUTGROUP
* @return \core\dml\sql_join Contains joins, wheres, params
* @throws coding_exception if empty or invalid context submitted when $groupid = USERSWITHOUTGROUP
*/
-function groups_get_members_join($groupid, $useridcolumn, context $context = null) {
+function groups_get_members_join($groupids, $useridcolumn, context $context = null) {
+ global $DB;
+
// Use unique prefix just in case somebody makes some SQL magic with the result.
static $i = 0;
$i++;
$prefix = 'gm' . $i . '_';
+ if (!is_array($groupids)) {
+ $groupids = $groupids ? [$groupids] : [];
+ }
+
$coursecontext = (!empty($context)) ? $context->get_course_context() : null;
- if ($groupid == USERSWITHOUTGROUP && empty($coursecontext)) {
+ if (in_array(USERSWITHOUTGROUP, $groupids) && empty($coursecontext)) {
// Throw an exception if $context is empty or invalid because it's needed to get the users without any group.
throw new coding_exception('Missing or wrong $context parameter in an attempt to get members without any group');
}
- if ($groupid == USERSWITHOUTGROUP) {
+ // Handle cases where we need to include users not in any groups.
+ if (($nogroupskey = array_search(USERSWITHOUTGROUP, $groupids)) !== false) {
// Get members without any group.
$join = "LEFT JOIN (
SELECT g.courseid, m.groupid, m.userid
) {$prefix}gm ON ({$prefix}gm.userid = {$useridcolumn} AND {$prefix}gm.courseid = :{$prefix}gcourseid)";
$where = "{$prefix}gm.userid IS NULL";
$param = ["{$prefix}gcourseid" => $coursecontext->instanceid];
+ unset($groupids[$nogroupskey]);
+
+ // Handle any groups that also need to be included (eg searching for users in no groups OR within specified groups).
+ if (!empty($groupids)) {
+ list($groupssql, $groupsparams) = $DB->get_in_or_equal($groupids, SQL_PARAMS_NAMED, $prefix);
+
+ $join .= "LEFT JOIN {groups_members} {$prefix}gm2
+ ON ({$prefix}gm2.userid = {$useridcolumn} AND {$prefix}gm2.groupid {$groupssql})";
+ // TODO: This only handles 'Any' (logical OR) of the provided groups. MDL-68348 will add 'All' and 'None' support.
+ $where = "({$where} OR {$prefix}gm2.userid IS NOT NULL)";
+ $param = array_merge($param, $groupsparams);
+ }
+
} else {
- // Get members of defined groupid.
+ // Get members of defined group IDs only.
+ list($groupssql, $param) = $DB->get_in_or_equal($groupids, SQL_PARAMS_NAMED, $prefix);
+
+ // TODO: This only handles 'Any' (logical OR) of the provided groups. MDL-68348 will add 'All' and 'None' support.
$join = "JOIN {groups_members} {$prefix}gm
- ON ({$prefix}gm.userid = $useridcolumn AND {$prefix}gm.groupid = :{$prefix}gmid)";
+ ON ({$prefix}gm.userid = {$useridcolumn} AND {$prefix}gm.groupid {$groupssql})";
$where = '';
- $param = ["{$prefix}gmid" => $groupid];
}
return new \core\dml\sql_join($join, $where, $param);
$this->assertTrue(array_key_exists($student1->id, $users));
}
+ public function test_groups_get_members_ids_sql_multiple_groups() {
+ global $DB;
+
+ $this->resetAfterTest(true);
+
+ $generator = $this->getDataGenerator();
+
+ $course = $generator->create_course();
+ $student1 = $generator->create_user();
+ $student2 = $generator->create_user();
+ $plugin = enrol_get_plugin('manual');
+ $role = $DB->get_record('role', array('shortname' => 'student'));
+ $group1 = $generator->create_group(array('courseid' => $course->id));
+ $group2 = $generator->create_group(array('courseid' => $course->id));
+ $groupids = [
+ $group1->id,
+ $group2->id,
+ ];
+ $instance = $DB->get_record('enrol', array(
+ 'courseid' => $course->id,
+ 'enrol' => 'manual',
+ ));
+
+ $this->assertNotEquals($instance, false);
+
+ // Enrol users in the course.
+ $plugin->enrol_user($instance, $student1->id, $role->id);
+ $plugin->enrol_user($instance, $student2->id, $role->id);
+
+ list($sql, $params) = groups_get_members_ids_sql($groupids);
+
+ // Test an empty group.
+ $users = $DB->get_records_sql($sql, $params);
+ $this->assertFalse(array_key_exists($student1->id, $users));
+
+ // Test with a member of one of the two group.
+ groups_add_member($group1->id, $student1->id);
+ $users = $DB->get_records_sql($sql, $params);
+ $this->assertTrue(array_key_exists($student1->id, $users));
+
+ // Test with members of two groups.
+ groups_add_member($group2->id, $student2->id);
+ $users = $DB->get_records_sql($sql, $params);
+ $this->assertTrue(array_key_exists($student1->id, $users));
+ $this->assertTrue(array_key_exists($student2->id, $users));
+ }
+
public function test_groups_get_members_ids_sql_valid_context() {
global $DB;
