Merge branch 'MDL-68348-master-6' of git://github.com/mickhawkins/moodle
authorAdrian Greeve <abgreeve@gmail.com>
Mon, 25 May 2020 23:48:53 +0000 (07:48 +0800)
committerAdrian Greeve <abgreeve@gmail.com>
Mon, 25 May 2020 23:48:53 +0000 (07:48 +0800)
lib/grouplib.php
lib/table/classes/local/filter/filter.php
lib/table/classes/local/filter/filterset.php
lib/testing/generator/data_generator.php
lib/tests/grouplib_test.php
user/classes/table/participants_search.php
user/lib.php
user/tests/table/participants_search_test.php
user/tests/userlib_test.php

index 34b4869..6aa8906 100644 (file)
@@ -43,6 +43,20 @@ define('VISIBLEGROUPS', 2);
  */
 define('USERSWITHOUTGROUP', -1);
 
+/**
+ * 'None' join type, used when filtering by groups (logical NOT)
+ */
+define('GROUPS_JOIN_NONE', 0);
+
+/**
+ * 'Any' join type, used when filtering by groups (logical OR)
+ */
+define('GROUPS_JOIN_ANY', 1);
+
+/**
+ * 'All' join type, used when filtering by groups (logical AND)
+ */
+define('GROUPS_JOIN_ALL', 2);
 
 /**
  * Determines if a group with a given groupid exists.
@@ -983,15 +997,16 @@ function groups_group_visible($groupid, $course, $cm = null, $userid = null) {
  *
  * @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
+ * @param int $groupsjointype Join type logic used. Defaults to 'Any' (logical OR).
  * @return array($sql, $params)
  * @throws coding_exception if empty or invalid context submitted when $groupid = USERSWITHOUTGROUP
  */
-function groups_get_members_ids_sql($groupids, context $context = null) {
+function groups_get_members_ids_sql($groupids, context $context = null, $groupsjointype = GROUPS_JOIN_ANY) {
     if (!is_array($groupids)) {
         $groupids = [$groupids];
     }
 
-    $groupjoin = groups_get_members_join($groupids, 'u.id', $context);
+    $groupjoin = groups_get_members_join($groupids, 'u.id', $context, $groupsjointype);
 
     $sql = "SELECT DISTINCT u.id
               FROM {user} u
@@ -1010,10 +1025,11 @@ function groups_get_members_ids_sql($groupids, context $context = null) {
  * @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 $groupids includes USERSWITHOUTGROUP
+ * @param int $jointype Join type logic used. Defaults to 'Any' (logical OR).
  * @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($groupids, $useridcolumn, context $context = null) {
+function groups_get_members_join($groupids, $useridcolumn, context $context = null, int $jointype = GROUPS_JOIN_ANY) {
     global $DB;
 
     // Use unique prefix just in case somebody makes some SQL magic with the result.
@@ -1025,43 +1041,100 @@ function groups_get_members_join($groupids, $useridcolumn, context $context = nu
         $groupids = $groupids ? [$groupids] : [];
     }
 
+    $join = '';
+    $where = '';
+    $param = [];
+
     $coursecontext = (!empty($context)) ? $context->get_course_context() : null;
     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');
     }
 
-    // Handle cases where we need to include users not in any groups.
+    // Handle cases where we need to include/exclude 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
-                    FROM {groups_members} m
-                    JOIN {groups} g ON g.id = m.groupid
-                ) {$prefix}gm ON ({$prefix}gm.userid = {$useridcolumn} AND {$prefix}gm.courseid = :{$prefix}gcourseid)";
-        $where = "{$prefix}gm.userid IS NULL";
+        $join .= "LEFT JOIN (
+                     SELECT g.courseid, m.groupid, m.userid
+                       FROM {groups_members} m
+                       JOIN {groups} g ON g.id = m.groupid
+                  ) {$prefix}gm ON ({$prefix}gm.userid = {$useridcolumn} AND {$prefix}gm.courseid = :{$prefix}gcourseid)";
+
+        // Join type 'None' when filtering by 'no groups' means match users in at least one group.
+        if ($jointype == GROUPS_JOIN_NONE) {
+            $where = "{$prefix}gm.userid IS NOT NULL";
+        } else {
+            // All other cases need to match users not in any group.
+            $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);
+    // Handle any specified groups that need to be included.
+    if (!empty($groupids)) {
+        switch ($jointype) {
+            case GROUPS_JOIN_ALL:
+                // Handle matching all of the provided groups (logical AND).
+                $joinallwheres = [];
+                $aliaskey = 0;
+                foreach ($groupids as $groupid) {
+                    $gmalias = "{$prefix}gm{$aliaskey}";
+                    $aliaskey++;
+                    $join .= "LEFT JOIN {groups_members} {$gmalias}
+                                     ON ({$gmalias}.userid = {$useridcolumn} AND {$gmalias}.groupid = :{$gmalias}param)";
+                    $joinallwheres[] = "{$gmalias}.userid IS NOT NULL";
+                    $param["{$gmalias}param"] = $groupid;
+                }
 
-            $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);
-        }
+                // Members of all of the specified groups only.
+                if (empty($where)) {
+                    $where = '(' . implode(' AND ', $joinallwheres) . ')';
+                } else {
+                    // Members of the specified groups and also no groups.
+                    // NOTE: This will always return no results, because you cannot be in specified groups and also be in no groups.
+                    $where = '(' . $where . ' AND ' . implode(' AND ', $joinallwheres) . ')';
+                }
 
-    } else {
-        // Get members of defined group IDs only.
-        list($groupssql, $param) = $DB->get_in_or_equal($groupids, SQL_PARAMS_NAMED, $prefix);
+                break;
+
+            case GROUPS_JOIN_ANY:
+                // Handle matching any of the provided groups (logical OR).
+                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})";
+                $param = array_merge($param, $groupsparams);
+
+                // Members of any of the specified groups only.
+                if (empty($where)) {
+                    $where = "{$prefix}gm2.userid IS NOT NULL";
+                } else {
+                    // Members of any of the specified groups or no groups.
+                    $where = "({$where} OR {$prefix}gm2.userid IS NOT NULL)";
+                }
 
-        // 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 {$groupssql})";
-        $where = '';
+                break;
+
+            case GROUPS_JOIN_NONE:
+                // Handle matching none of the provided groups (logical NOT).
+                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})";
+                $param = array_merge($param, $groupsparams);
+
+                // Members of none of the specified groups only.
+                if (empty($where)) {
+                    $where = "{$prefix}gm2.userid IS NULL";
+                } else {
+                    // Members of any unspecified groups (not a member of the specified groups, and not a member of no groups).
+                    $where = "({$where} AND {$prefix}gm2.userid IS NULL)";
+                }
+
+                break;
+        }
     }
 
     return new \core\dml\sql_join($join, $where, $param);
index eda6039..e6fc650 100644 (file)
@@ -41,8 +41,11 @@ use Iterator;
  */
 class filter implements Countable, Iterator, JsonSerializable {
 
-    /** @var in The default filter type (ANY) */
-    const JOINTYPE_DEFAULT = 1;
+    /**
+     * @var in The default filter type (ALL)
+     * Note: This is for backwards compatibility with the old UI behaviour and will be set to JOINTYPE_ANY as part of MDL-68612.
+     */
+    const JOINTYPE_DEFAULT = 2;
 
     /** @var int None of the following match */
     const JOINTYPE_NONE = 0;
index 0afddeb..d04eaef 100644 (file)
@@ -40,8 +40,11 @@ use moodle_exception;
  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  */
 abstract class filterset implements JsonSerializable {
-    /** @var in The default filter type (ANY) */
-    const JOINTYPE_DEFAULT = 1;
+    /**
+     * @var in The default filter type (ALL)
+     * Note: This is for backwards compatibility with the old UI behaviour and will be set to JOINTYPE_ANY as part of MDL-68612.
+     */
+    const JOINTYPE_DEFAULT = 2;
 
     /** @var int None of the following match */
     const JOINTYPE_NONE = 0;
index c8e031d..a5697b5 100644 (file)
@@ -1205,4 +1205,26 @@ EOD;
 
         return $user;
     }
+
+    /**
+     * Create a new last access record for a given user in a course.
+     *
+     * @param   \stdClass   $user The user
+     * @param   \stdClass   $course The course the user accessed
+     * @param   int         $timestamp The timestamp for when the user last accessed the course
+     * @return  \stdClass   The user_lastaccess record
+     */
+    public function create_user_course_lastaccess(\stdClass $user, \stdClass $course, int $timestamp): \stdClass {
+        global $DB;
+
+        $record = [
+            'userid' => $user->id,
+            'courseid' => $course->id,
+            'timeaccess' => $timestamp,
+        ];
+
+        $recordid = $DB->insert_record('user_lastaccess', $record);
+
+        return $DB->get_record('user_lastaccess', ['id' => $recordid], '*', MUST_EXIST);
+    }
 }
index 117d323..f298447 100644 (file)
@@ -260,6 +260,114 @@ class core_grouplib_testcase extends advanced_testcase {
         $this->assertTrue(array_key_exists($student2->id, $users));
     }
 
+    public function test_groups_get_members_ids_sql_multiple_groups_join_types() {
+        global $DB;
+
+        $this->resetAfterTest(true);
+
+        $generator = $this->getDataGenerator();
+
+        $course = $generator->create_course();
+        $student1 = $generator->create_user();
+        $student2 = $generator->create_user();
+        $student3 = $generator->create_user();
+        $student4 = $generator->create_user();
+        $student5 = $generator->create_user();
+        $student6 = $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));
+        $group3 = $generator->create_group(array('courseid' => $course->id));
+        // Only groups 1 and 2 specified in SQL (group 3 helps cover the None case).
+        $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);
+        $plugin->enrol_user($instance, $student3->id, $role->id);
+        $plugin->enrol_user($instance, $student4->id, $role->id);
+        $plugin->enrol_user($instance, $student5->id, $role->id);
+        $plugin->enrol_user($instance, $student6->id, $role->id);
+
+        // Generate SQL with the different groups join types for members of group1 and group2.
+        list($sqlany, $paramsany) = groups_get_members_ids_sql($groupids, null, GROUPS_JOIN_ANY);
+        list($sqlall, $paramsall) = groups_get_members_ids_sql($groupids, null, GROUPS_JOIN_ALL);
+        list($sqlnone, $paramsnone) = groups_get_members_ids_sql($groupids, null, GROUPS_JOIN_NONE);
+
+        // Any - Test empty groups, no matches.
+        $users = $DB->get_records_sql($sqlany, $paramsany);
+        $this->assertFalse(array_key_exists($student1->id, $users));
+        $this->assertFalse(array_key_exists($student2->id, $users));
+        $this->assertFalse(array_key_exists($student3->id, $users));
+        $this->assertFalse(array_key_exists($student4->id, $users));
+        $this->assertFalse(array_key_exists($student5->id, $users));
+        $this->assertFalse(array_key_exists($student6->id, $users));
+
+        // All - Test empty groups, no matches.
+        $users = $DB->get_records_sql($sqlall, $paramsall);
+        $this->assertFalse(array_key_exists($student1->id, $users));
+        $this->assertFalse(array_key_exists($student2->id, $users));
+        $this->assertFalse(array_key_exists($student3->id, $users));
+        $this->assertFalse(array_key_exists($student4->id, $users));
+        $this->assertFalse(array_key_exists($student5->id, $users));
+        $this->assertFalse(array_key_exists($student6->id, $users));
+
+        // None - Test empty groups, all match.
+        $users = $DB->get_records_sql($sqlnone, $paramsnone);
+        $this->assertTrue(array_key_exists($student1->id, $users));
+        $this->assertTrue(array_key_exists($student2->id, $users));
+        $this->assertTrue(array_key_exists($student3->id, $users));
+        $this->assertTrue(array_key_exists($student4->id, $users));
+        $this->assertTrue(array_key_exists($student5->id, $users));
+        $this->assertTrue(array_key_exists($student6->id, $users));
+
+        // Assign various group member combinations.
+        groups_add_member($group1->id, $student1->id);
+        groups_add_member($group1->id, $student2->id);
+        groups_add_member($group1->id, $student3->id);
+        groups_add_member($group2->id, $student2->id);
+        groups_add_member($group2->id, $student3->id);
+        groups_add_member($group2->id, $student4->id);
+        groups_add_member($group3->id, $student5->id);
+
+        // Any - Test students in one or both of groups 1 and 2 matched.
+        $users = $DB->get_records_sql($sqlany, $paramsany);
+        $this->assertTrue(array_key_exists($student1->id, $users));
+        $this->assertTrue(array_key_exists($student2->id, $users));
+        $this->assertTrue(array_key_exists($student3->id, $users));
+        $this->assertTrue(array_key_exists($student4->id, $users));
+        $this->assertFalse(array_key_exists($student5->id, $users));
+        $this->assertFalse(array_key_exists($student6->id, $users));
+
+        // All - Test only students in both groups 1 and 2 matched.
+        $users = $DB->get_records_sql($sqlall, $paramsall);
+        $this->assertTrue(array_key_exists($student2->id, $users));
+        $this->assertTrue(array_key_exists($student3->id, $users));
+        $this->assertFalse(array_key_exists($student1->id, $users));
+        $this->assertFalse(array_key_exists($student4->id, $users));
+        $this->assertFalse(array_key_exists($student5->id, $users));
+        $this->assertFalse(array_key_exists($student6->id, $users));
+
+        // None - Test only students not in group 1 or 2 matched.
+        $users = $DB->get_records_sql($sqlnone, $paramsnone);
+        $this->assertTrue(array_key_exists($student5->id, $users));
+        $this->assertTrue(array_key_exists($student6->id, $users));
+        $this->assertFalse(array_key_exists($student1->id, $users));
+        $this->assertFalse(array_key_exists($student2->id, $users));
+        $this->assertFalse(array_key_exists($student3->id, $users));
+        $this->assertFalse(array_key_exists($student4->id, $users));
+    }
+
     public function test_groups_get_members_ids_sql_valid_context() {
         global $DB;
 
index 1ede7f6..744d35f 100644 (file)
@@ -32,6 +32,10 @@ use moodle_recordset;
 use stdClass;
 use user_picture;
 
+defined('MOODLE_INTERNAL') || die;
+
+require_once($CFG->dirroot . '/user/lib.php');
+
 /**
  * Class used to fetch participants based on a filterset.
  *
@@ -95,9 +99,10 @@ class participants_search {
             'from' => $from,
             'where' => $where,
             'params' => $params,
+            'groupby' => $groupby,
         ] = $this->get_participants_sql($additionalwhere, $additionalparams);
 
-        return $DB->get_recordset_sql("{$select} {$from} {$where} {$sort}", $params, $limitfrom, $limitnum);
+        return $DB->get_recordset_sql("{$select} {$from} {$where} {$groupby} {$sort}", $params, $limitfrom, $limitnum);
     }
 
     /**
@@ -116,7 +121,7 @@ class participants_search {
             'params' => $params,
         ] = $this->get_participants_sql($additionalwhere, $additionalparams);
 
-        return $DB->count_records_sql("SELECT COUNT(u.id) {$from} {$where}", $params);
+        return $DB->count_records_sql("SELECT COUNT(DISTINCT(u.id)) {$from} {$where}", $params);
     }
 
     /**
@@ -128,33 +133,67 @@ class participants_search {
      */
     protected function get_participants_sql(string $additionalwhere, array $additionalparams): array {
         $isfrontpage = ($this->course->id == SITEID);
-        $accesssince = $this->filterset->has_filter('accesssince') ? $this->filterset->get_filter('accesssince')->current() : 0;
+        $accesssince = 0;
+        // Whether to match on users who HAVE accessed since the given time (ie false is 'inactive for more than x').
+        $matchaccesssince = false;
+
+        if ($this->filterset->has_filter('accesssince')) {
+            $accesssince = $this->filterset->get_filter('accesssince')->current();
+
+            // Last access filtering only supports matching or not matching, not any/all/none.
+            $jointypenone = $this->filterset->get_filter('accesssince')::JOINTYPE_NONE;
+            if ($this->filterset->get_filter('accesssince')->get_join_type() === $jointypenone) {
+                $matchaccesssince = true;
+            }
+        }
 
         [
+            // SQL that forms part of the filter.
             'sql' => $esql,
+            // SQL for enrolment filtering that must always be applied (eg due to capability restrictions).
+            'forcedsql' => $esqlforced,
             'params' => $params,
         ] = $this->get_enrolled_sql();
 
         $joins = ['FROM {user} u'];
         $wheres = [];
+        // Set where statement(s) that must always be included (outside of filter wheres).
+        $forcedwhere = "u.deleted = 0";
 
         $userfieldssql = user_picture::fields('u', $this->userfields);
 
+        // Include any compulsory enrolment SQL (eg capability related filtering that must be applied).
+        if (!empty($esqlforced)) {
+            $joins[] = "JOIN ({$esqlforced}) fef ON fef.id = u.id";
+        }
+
+        // Include any enrolment related filtering.
+        if (!empty($esql)) {
+            $joins[] = "LEFT JOIN ({$esql}) ef ON ef.id = u.id";
+            $wheres[] = 'ef.id IS NOT NULL';
+        }
+
         if ($isfrontpage) {
-            $select = "SELECT $userfieldssql, u.lastaccess";
-            $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Everybody on the frontpage usually.
+            $select = "SELECT {$userfieldssql}, u.lastaccess";
             if ($accesssince) {
-                $wheres[] = user_get_user_lastaccess_sql($accesssince);
+                $wheres[] = user_get_user_lastaccess_sql($accesssince, 'u', $matchaccesssince);
             }
+            $groupby = ' GROUP BY u.id, u.lastaccess, ctx.id';
         } else {
-            $select = "SELECT $userfieldssql, COALESCE(ul.timeaccess, 0) AS lastaccess";
-            $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Course enrolled users only.
+            $select = "SELECT {$userfieldssql}, COALESCE(ul.timeaccess, 0) AS lastaccess";
             // Not everybody has accessed the course yet.
-            $joins[] = 'LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid)';
-            $params['courseid'] = $this->course->id;
+            $joins[] = 'LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid2)';
+            $params['courseid2'] = $this->course->id;
             if ($accesssince) {
-                $wheres[] = user_get_course_lastaccess_sql($accesssince);
+                $wheres[] = user_get_course_lastaccess_sql($accesssince, 'ul', $matchaccesssince);
             }
+
+            // Make sure we only ever fetch users in the course (regardless of enrolment filters).
+            $joins[] = 'JOIN {user_enrolments} ue ON ue.userid = u.id';
+            $joins[] = 'JOIN {enrol} e ON e.id = ue.enrolid
+                                      AND e.courseid = :courseid1';
+            $params['courseid1'] = $this->course->id;
+            $groupby = ' GROUP BY u.id, ul.timeaccess, ctx.id';
         }
 
         // Performance hacks - we preload user contexts together with accounts.
@@ -183,12 +222,12 @@ class participants_search {
         // Apply any keyword text searches.
         if ($this->filterset->has_filter('keywords')) {
             [
-                'wheres' => $keywordswheres,
+                'where' => $keywordswhere,
                 'params' => $keywordsparams,
             ] = $this->get_keywords_search_sql();
 
-            if (!empty($keywordswheres)) {
-                $wheres = array_merge($wheres, $keywordswheres);
+            if (!empty($keywordswhere)) {
+                $wheres[] = $keywordswhere;
             }
 
             if (!empty($keywordsparams)) {
@@ -205,7 +244,23 @@ class participants_search {
         // Prepare final values.
         $from = implode("\n", $joins);
         if ($wheres) {
-            $where = 'WHERE ' . implode(' AND ', $wheres);
+            switch ($this->filterset->get_join_type()) {
+                case $this->filterset::JOINTYPE_ALL:
+                    $firstjoin = ' AND ';
+                    $wheresjoin = ' AND ';
+                    break;
+                case $this->filterset::JOINTYPE_NONE:
+                    $firstjoin = ' AND NOT ';
+                    $wheresjoin = ' AND NOT ';
+                    break;
+                default:
+                    // Default to 'Any' jointype.
+                    $firstjoin = ' AND ';
+                    $wheresjoin = ' OR ';
+                    break;
+            }
+
+            $where = "WHERE ({$forcedwhere}) {$firstjoin}" . implode($wheresjoin, $wheres);
         } else {
             $where = '';
         }
@@ -215,80 +270,68 @@ class participants_search {
             'from' => $from,
             'where' => $where,
             'params' => $params,
+            'groupby' => $groupby,
         ];
     }
 
     /**
      * Prepare SQL and associated parameters for users enrolled in the course.
      *
-     * @return array SQL query data in the format ['sql' => '', 'params' => []].
+     * @return array SQL query data in the format ['sql' => '', 'forcedsql' => '', 'params' => []].
      */
     protected function get_enrolled_sql(): array {
-        // Default status filter settings.
-        // We only show active by default, especially if the user has no capability to review enrolments.
-        $onlyactive = true;
-        $onlysuspended = false;
-
-        $enrolids = [];
-        $groupids = [];
-
-        if ($this->filterset->has_filter('enrolments')) {
-            $enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();
-        }
-
-        if ($this->filterset->has_filter('groups')) {
-            $groupids = $this->filterset->get_filter('groups')->get_filter_values();
-        }
-
+        $isfrontpage = ($this->context->instanceid == SITEID);
         $prefix = 'eu_';
-        $uid = "{$prefix}u.id";
+        $filteruid = "{$prefix}u.id";
+        $sql = '';
         $joins = [];
         $wheres = [];
+        $params = [];
+        // It is possible some statements must always be included (in addition to any filtering).
+        $forcedprefix = "f{$prefix}";
+        $forceduid = "{$forcedprefix}u.id";
+        $forcedsql = '';
+        $forcedjoins = [];
+        $forcedwhere = "{$forcedprefix}u.deleted = 0";
 
-        // Set enrolment types.
-        if (has_capability('moodle/course:enrolreview', $this->context) &&
-                (has_capability('moodle/course:viewsuspendedusers', $this->context))) {
-            $statusids = [-1];
-
-            if ($this->filterset->has_filter('status')) {
-                $statusids = $this->filterset->get_filter('status')->get_filter_values();
-            }
+        if (!$isfrontpage) {
+            // Prepare any enrolment method filtering.
+            [
+                'joins' => $methodjoins,
+                'where' => $wheres[],
+                'params' => $methodparams,
+            ] = $this->get_enrol_method_sql($filteruid);
 
-            // If both status IDs are selected, treat it as not filtering by status.
-            // Note: This is a temporary measure that supports the existing logic.
-            // It will be updated when support is added for all logical operators (all/none).
-            if (count($statusids) !== 1) {
-                $statusid = -1;
+            // Prepare any status filtering.
+            [
+                'joins' => $statusjoins,
+                'where' => $statuswhere,
+                'params' => $statusparams,
+                'forcestatus' => $forcestatus,
+            ] = $this->get_status_sql($filteruid, $forceduid, $forcedprefix);
+
+            if ($forcestatus) {
+                // Force filtering by active participants if user does not have capability to view suspended.
+                $forcedjoins = array_merge($forcedjoins, $statusjoins);
+                $statusjoins = [];
+                $forcedwhere .= " AND ({$statuswhere})";
             } else {
-                $statusid = $statusids[0];
+                $wheres[] = $statuswhere;
             }
 
-            switch ($statusid) {
-                case ENROL_USER_ACTIVE:
-                    // Nothing to do here.
-                    break;
-                case ENROL_USER_SUSPENDED:
-                    $onlyactive = false;
-                    $onlysuspended = true;
-                    break;
-                default:
-                    // If the user has capability to review user enrolments, but statusid is set to -1, set $onlyactive to false.
-                    $onlyactive = false;
-                    break;
-            }
+            $joins = array_merge($joins, $methodjoins, $statusjoins);
+            $params = array_merge($params, $methodparams, $statusparams);
         }
 
-        // Prepare enrolment type filtering.
-        // This will need to use a custom method or new function when 'All'/'Not' cases are introduced,
-        // to avoid the separate passing in of status values ($onlyactive and $onlysuspended).
-        $enrolledjoin = $this->get_enrolled_join($this->context, $uid, $onlyactive, $onlysuspended, $enrolids);
-        $joins[] = $enrolledjoin->joins;
-        $wheres[] = $enrolledjoin->wheres;
-        $params = $enrolledjoin->params;
-
         // Prepare any groups filtering.
+        $groupids = [];
+
+        if ($this->filterset->has_filter('groups')) {
+            $groupids = $this->filterset->get_filter('groups')->get_filter_values();
+        }
+
         if ($groupids) {
-            $groupjoin = groups_get_members_join($groupids, $uid, $this->context);
+            $groupjoin = groups_get_members_join($groupids, $filteruid, $this->context, $this->get_groups_jointype());
             $joins[] = $groupjoin->joins;
             $params = array_merge($params, $groupjoin->params);
             if (!empty($groupjoin->wheres)) {
@@ -296,137 +339,337 @@ class participants_search {
             }
         }
 
-        $joinsql = implode("\n", $joins);
-        $wheres[] = "{$prefix}u.deleted = 0";
-        $wheresql = implode(" AND ", $wheres);
+        // Combine the relevant filters and prepare the query.
+        $joins = array_filter($joins);
+        if (!empty($joins)) {
+            $joinsql = implode("\n", $joins);
+
+            $sql = "SELECT DISTINCT {$prefix}u.id
+                               FROM {user} {$prefix}u
+                                    {$joinsql}
+                              WHERE {$prefix}u.deleted = 0";
+        }
+
+        $wheres = array_filter($wheres);
+        if (!empty($wheres)) {
+            if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_ALL) {
+                $wheresql = '(' . implode(') AND (', $wheres) . ')';
+            } else {
+                $wheresql = '(' . implode(') OR (', $wheres) . ')';
+            }
+
+            $sql .= " AND ({$wheresql})";
+        }
+
+        // Prepare any SQL that must be applied.
+        if (!empty($forcedjoins)) {
+            $forcedjoinsql = implode("\n", $forcedjoins);
+            $forcedsql = "SELECT DISTINCT {$forcedprefix}u.id
+                                     FROM {user} {$forcedprefix}u
+                                          {$forcedjoinsql}
+                                    WHERE {$forcedwhere}";
+        }
 
-        $sql = "SELECT DISTINCT {$prefix}u.id
-                  FROM {user} {$prefix}u
-                       {$joinsql}
-                 WHERE {$wheresql}";
         return [
             'sql' => $sql,
+            'forcedsql' => $forcedsql,
             'params' => $params,
         ];
     }
 
     /**
-     * Returns array with SQL joins and parameters returning all IDs of users enrolled into course.
+     * Prepare the enrolment methods filter SQL content.
      *
-     * Note: This is a temporary method (based on get_enrolled_join from enrollib), supporting multiple enrolment IDs
-     * matched using logical OR. A more complete implementation of other logical operators and supporting more
-     * flexible enrolment statuses will be implemented in MDL-68348.
-     *
-     * This method is using 'ej[0-9]+_' prefix for table names and parameters.
-     *
-     * @throws coding_exception
-     *
-     * @param \context $context
-     * @param string $useridcolumn User id column used the calling query, e.g. u.id
-     * @param bool $onlyactive consider only active enrolments in enabled plugins and time restrictions
-     * @param bool $onlysuspended inverse of onlyactive, consider only suspended enrolments
-     * @param array $enrolids The enrolment IDs. If not [], only users enrolled using these enrolment methods will be returned.
-     * @return \core\dml\sql_join Contains joins, wheres, params
+     * @param string $useridcolumn User ID column used in the calling query, e.g. u.id
+     * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => []].
      */
-    protected function get_enrolled_join(\context $context, $useridcolumn, $onlyactive = false, $onlysuspended = false,
-            $enrolids = []) {
-
+    protected function get_enrol_method_sql($useridcolumn): array {
         global $DB;
 
-        // Use unique prefix just in case somebody makes some SQL magic with the result.
-        static $i = 0;
-        $i++;
-        $prefix = 'ej' . $i . '_';
+        $prefix = 'ejm_';
+        $joins  = [];
+        $where = '';
+        $params = [];
+        $enrolids = [];
 
-        if (!is_array($enrolids)) {
-            $enrolids = $enrolids ? [$enrolids] : [];
+        if ($this->filterset->has_filter('enrolments')) {
+            $enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();
         }
 
-        // First find the course context.
-        $coursecontext = $context->get_course_context();
+        if (!empty($enrolids)) {
+            $jointype = $this->filterset->get_filter('enrolments')->get_join_type();
+
+            // Handle 'All' join type.
+            if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL ||
+                    $jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_NONE) {
+                $allwheres = [];
+
+                foreach ($enrolids as $i => $enrolid) {
+                    $thisprefix = "{$prefix}{$i}";
+                    list($enrolidsql, $enrolidparam) = $DB->get_in_or_equal($enrolid, SQL_PARAMS_NAMED, $thisprefix);
+
+                    $joins[] = "LEFT JOIN {enrol} {$thisprefix}e
+                                       ON ({$thisprefix}e.id {$enrolidsql}
+                                      AND {$thisprefix}e.courseid = :{$thisprefix}courseid)";
+                    $joins[] = "LEFT JOIN {user_enrolments} {$thisprefix}ue
+                                       ON {$thisprefix}ue.userid = {$useridcolumn}
+                                      AND {$thisprefix}ue.enrolid = {$thisprefix}e.id";
+
+                    if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL) {
+                        $allwheres[] = "{$thisprefix}ue.id IS NOT NULL";
+                    } else {
+                        // Ensure participants do not match any of the filtered methods when joining by 'None'.
+                        $allwheres[] = "{$thisprefix}ue.id IS NULL";
+                    }
 
-        $isfrontpage = ($coursecontext->instanceid == SITEID);
+                    $params["{$thisprefix}courseid"] = $this->course->id;
+                    $params = array_merge($params, $enrolidparam);
+                }
 
-        if ($onlyactive && $onlysuspended) {
-            throw new \coding_exception("Both onlyactive and onlysuspended are set, this is probably not what you want!");
-        }
-        if ($isfrontpage && $onlysuspended) {
-            throw new \coding_exception("onlysuspended is not supported on frontpage; please add your own early-exit!");
+                if (!empty($allwheres)) {
+                    $where = implode(' AND ', $allwheres);
+                }
+            } else {
+                // Handle the 'Any'join type.
+
+                list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
+
+                $joins[] = "LEFT JOIN {enrol} {$prefix}e
+                                   ON ({$prefix}e.id {$enrolidssql}
+                                  AND {$prefix}e.courseid = :{$prefix}courseid)";
+                $joins[] = "LEFT JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}
+                                                              AND {$prefix}ue.enrolid = {$prefix}e.id";
+                $where = "{$prefix}ue.id IS NOT NULL";
+
+                $params["{$prefix}courseid"] = $this->course->id;
+                $params = array_merge($params, $enrolidsparams);
+            }
         }
 
+        return [
+            'joins' => $joins,
+            'where' => $where,
+            'params' => $params,
+        ];
+    }
+
+    /**
+     * Prepare the status filter SQL content.
+     * Note: Users who cannot view suspended users will always have their results filtered to only show active participants.
+     *
+     * @param string $filteruidcolumn User ID column used in the calling query, e.g. eu_u.id
+     * @param string $forceduidcolumn User ID column used in any forced query, e.g. feu_u.id
+     * @param string $forcedprefix The prefix to use if forced filtering is required
+     * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => [], 'forcestatus' => true]
+     */
+    protected function get_status_sql($filteruidcolumn, $forceduidcolumn, $forcedprefix): array {
+        $prefix = $forcedprefix;
+        $useridcolumn = $forceduidcolumn;
         $joins  = [];
-        $wheres = [];
+        $where = '';
         $params = [];
+        $forcestatus = true;
 
-        $wheres[] = "1 = 1"; // Prevent broken where clauses later on.
+        // By default we filter to show users with active status only.
+        $statusids = [ENROL_USER_ACTIVE];
+        $statusjointype = $this->filterset::JOINTYPE_DEFAULT;
 
-        // Note all users are "enrolled" on the frontpage, but for others...
-        if (!$isfrontpage) {
-            $where1 = "{$prefix}ue.status = :{$prefix}active AND {$prefix}e.status = :{$prefix}enabled";
-            $where2 = "{$prefix}ue.timestart < :{$prefix}now1 AND ({$prefix}ue.timeend = 0
-                       OR {$prefix}ue.timeend > :{$prefix}now2)";
+        // Allow optional status filtering if the user has relevant capabilities.
+        if (has_capability('moodle/course:enrolreview', $this->context) &&
+                (has_capability('moodle/course:viewsuspendedusers', $this->context))) {
+            $forcestatus = false;
+            $prefix = 'ejs_';
+            $useridcolumn = $filteruidcolumn;
 
-            $enrolconditions = [
-                "{$prefix}e.id = {$prefix}ue.enrolid",
-                "{$prefix}e.courseid = :{$prefix}courseid",
-            ];
+            // Default to no filtering if capabilities allow for it.
+            $statusids = [];
 
-            // TODO: This only handles 'Any' (logical OR) of the provided enrol IDs. MDL-68348 will add 'All' and 'None' support.
-            if (!empty($enrolids)) {
-                list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
-                $enrolconditions[] = "{$prefix}e.id {$enrolidssql}";
-                $params = array_merge($params, $enrolidsparams);
+            if ($this->filterset->has_filter('status')) {
+                $statusjointype = $this->filterset->get_filter('status')->get_join_type();
+                $statusfiltervalues = $this->filterset->get_filter('status')->get_filter_values();
+
+                // If values are set for the status filter, use them.
+                if (!empty($statusfiltervalues)) {
+                    $statusids = $statusfiltervalues;
+                }
             }
+        }
 
-            $enrolconditionssql = implode(" AND ", $enrolconditions);
-            $ejoin = "JOIN {enrol} {$prefix}e ON ($enrolconditionssql)";
+        if (!empty($statusids)) {
+            $enroljoin = 'JOIN {enrol} %1$se ON %1$se.id = %1$sue.enrolid
+                                                  AND %1$se.courseid = :%1$scourseid';
+
+            $whereactive = '(%1$sue.status = :%2$sactive
+                          AND %1$se.status = :%2$senabled
+                      AND %1$sue.timestart < :%2$snow1
+                       AND (%1$sue.timeend = 0
+                         OR %1$sue.timeend > :%2$snow2))';
+
+            $wheresuspended = '(%1$sue.status = :%2$ssuspended
+                             OR %1$se.status != :%2$senabled
+                         OR %1$sue.timestart >= :%2$snow1
+                           OR (%1$sue.timeend > 0
+                          AND %1$sue.timeend <= :%2$snow2))';
+
+            // Round 'now' time to help DB caching.
+            $now = round(time(), -2);
+
+            switch ($statusjointype) {
+                case $this->filterset::JOINTYPE_ALL:
+                    $joinwheres = [];
+
+                    foreach ($statusids as $i => $statusid) {
+                        $joinprefix = "{$prefix}{$i}";
+                        $joins[] = "JOIN {user_enrolments} {$joinprefix}ue ON {$joinprefix}ue.userid = {$useridcolumn}";
+
+                        if ($statusid === ENROL_USER_ACTIVE) {
+                            // Conditions to be met if user filtering by active.
+                            $joinwheres[] = sprintf($whereactive, $joinprefix, $joinprefix);
+
+                            $activeparams = [
+                                "{$joinprefix}active" => ENROL_USER_ACTIVE,
+                                "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
+                                "{$joinprefix}now1"   => $now,
+                                "{$joinprefix}now2"   => $now,
+                                "{$joinprefix}courseid"   => $this->course->id,
+                            ];
+
+                            $params = array_merge($params, $activeparams);
+                        } else {
+                            // Conditions to be met if filtering by suspended (currently the only other status).
+                            $joinwheres[] = sprintf($wheresuspended, $joinprefix, $joinprefix);
+
+                            $suspendedparams = [
+                                "{$joinprefix}suspended" => ENROL_USER_SUSPENDED,
+                                "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
+                                "{$joinprefix}now1"   => $now,
+                                "{$joinprefix}now2"   => $now,
+                                "{$joinprefix}courseid"   => $this->course->id,
+                            ];
+
+                            $params = array_merge($params, $suspendedparams);
+                        }
+
+                        $joins[] = sprintf($enroljoin, $joinprefix);
+                    }
 
-            $params[$prefix.'courseid'] = $coursecontext->instanceid;
+                    $where = implode(' AND ', $joinwheres);
+                    break;
 
-            if (!$onlysuspended) {
-                $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = $useridcolumn";
-                $joins[] = $ejoin;
-                if ($onlyactive) {
-                    $wheres[] = "$where1 AND $where2";
-                }
-            } else {
-                // Suspended only where there is enrolment but ALL are suspended.
-                // Consider multiple enrols where one is not suspended or plain role_assign.
-                $enrolselect = "SELECT DISTINCT {$prefix}ue.userid
-                                           FROM {user_enrolments} {$prefix}ue $ejoin
-                                          WHERE $where1 AND $where2";
-                $joins[] = "JOIN {user_enrolments} {$prefix}ue1 ON {$prefix}ue1.userid = $useridcolumn";
-                $enrolconditions = [
-                    "{$prefix}e1.id = {$prefix}ue1.enrolid",
-                    "{$prefix}e1.courseid = :{$prefix}_e1_courseid",
-                ];
-
-                if (!empty($enrolids)) {
-                    list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
-                    $enrolconditions[] = "{$prefix}e1.id {$enrolidssql}";
-                    $params = array_merge($params, $enrolidsparams);
-                }
+                case $this->filterset::JOINTYPE_NONE:
+                    // Should always be enrolled, just not in any of the filtered statuses.
+                    $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
+                    $joins[] = sprintf($enroljoin, $prefix);
+                    $joinwheres = [];
+                    $params["{$prefix}courseid"] = $this->course->id;
+
+                    foreach ($statusids as $i => $statusid) {
+                        $paramprefix = "{$prefix}{$i}";
+
+                        if ($statusid === ENROL_USER_ACTIVE) {
+                            // Conditions to be met if user filtering by active.
+                            $joinwheres[] = sprintf("NOT {$whereactive}", $prefix, $paramprefix);
+
+                            $activeparams = [
+                                "{$paramprefix}active" => ENROL_USER_ACTIVE,
+                                "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
+                                "{$paramprefix}now1"   => $now,
+                                "{$paramprefix}now2"   => $now,
+                            ];
+
+                            $params = array_merge($params, $activeparams);
+                        } else {
+                            // Conditions to be met if filtering by suspended (currently the only other status).
+                            $joinwheres[] = sprintf("NOT {$wheresuspended}", $prefix, $paramprefix);
+
+                            $suspendedparams = [
+                                "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
+                                "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
+                                "{$paramprefix}now1"   => $now,
+                                "{$paramprefix}now2"   => $now,
+                            ];
+
+                            $params = array_merge($params, $suspendedparams);
+                        }
+                    }
 
-                $enrolconditionssql = implode(" AND ", $enrolconditions);
-                $joins[] = "JOIN {enrol} {$prefix}e1 ON ($enrolconditionssql)";
-                $params["{$prefix}_e1_courseid"] = $coursecontext->instanceid;
-                $wheres[] = "$useridcolumn NOT IN ($enrolselect)";
-            }
+                    $where = '(' . implode(' AND ', $joinwheres) . ')';
+                    break;
+
+                default:
+                    // Handle the 'Any' join type.
+
+                    $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
+                    $joins[] = sprintf($enroljoin, $prefix);
+                    $joinwheres = [];
+                    $params["{$prefix}courseid"] = $this->course->id;
+
+                    foreach ($statusids as $i => $statusid) {
+                        $paramprefix = "{$prefix}{$i}";
+
+                        if ($statusid === ENROL_USER_ACTIVE) {
+                            // Conditions to be met if user filtering by active.
+                            $joinwheres[] = sprintf($whereactive, $prefix, $paramprefix);
+
+                            $activeparams = [
+                                "{$paramprefix}active" => ENROL_USER_ACTIVE,
+                                "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
+                                "{$paramprefix}now1"   => $now,
+                                "{$paramprefix}now2"   => $now,
+                            ];
+
+                            $params = array_merge($params, $activeparams);
+                        } else {
+                            // Conditions to be met if filtering by suspended (currently the only other status).
+                            $joinwheres[] = sprintf($wheresuspended, $prefix, $paramprefix);
+
+                            $suspendedparams = [
+                                "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
+                                "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
+                                "{$paramprefix}now1"   => $now,
+                                "{$paramprefix}now2"   => $now,
+                            ];
+
+                            $params = array_merge($params, $suspendedparams);
+                        }
+                    }
 
-            if ($onlyactive || $onlysuspended) {
-                $now = round(time(), -2); // Rounding helps caching in DB.
-                $params = array_merge($params, [
-                        $prefix . 'enabled' => ENROL_INSTANCE_ENABLED,
-                        $prefix . 'active' => ENROL_USER_ACTIVE,
-                        $prefix . 'now1' => $now,
-                        $prefix . 'now2' => $now]);
+                    $where = '(' . implode(' OR ', $joinwheres) . ')';
+                    break;
             }
         }
 
-        $joins = implode("\n", $joins);
-        $wheres = implode(" AND ", $wheres);
+        return [
+            'joins' => $joins,
+            'where' => $where,
+            'params' => $params,
+            'forcestatus' => $forcestatus,
+        ];
+    }
 
-        return new \core\dml\sql_join($joins, $wheres, $params);
+    /**
+     * Fetch the groups filter's grouplib jointype, based on its filterset jointype.
+     * This mapping is to ensure compatibility between the two, should their values ever differ.
+     *
+     * @return int
+     */
+    protected function get_groups_jointype(): int {
+        $groupsfilter = $this->filterset->get_filter('groups');
+
+        switch ($groupsfilter->get_join_type()) {
+            case $groupsfilter::JOINTYPE_NONE:
+                $groupsjoin = GROUPS_JOIN_NONE;
+                break;
+            case $groupsfilter::JOINTYPE_ALL:
+                $groupsjoin = GROUPS_JOIN_ALL;
+                break;
+            default:
+                // Default to ANY jointype.
+                $groupsjoin = GROUPS_JOIN_ANY;
+                break;
+        }
+
+        return $groupsjoin;
     }
 
     /**
@@ -442,38 +685,93 @@ class participants_search {
 
         // Limit list to users with some role only.
         if ($this->filterset->has_filter('roles')) {
-            $roleids = $this->filterset->get_filter('roles')->get_filter_values();
+            $rolesfilter = $this->filterset->get_filter('roles');
+
+            $roleids = $rolesfilter->get_filter_values();
+            $jointype = $rolesfilter->get_join_type();
+
+            // Determine how to match values in the query.
+            $matchinsql = 'IN';
+            switch ($jointype) {
+                case $rolesfilter::JOINTYPE_ALL:
+                    $wherejoin = ' AND ';
+                    break;
+                case $rolesfilter::JOINTYPE_NONE:
+                    $wherejoin = ' AND NOT ';
+                    $matchinsql = 'NOT IN';
+                    break;
+                default:
+                    // Default to 'Any' jointype.
+                    $wherejoin = ' OR ';
+                    break;
+            }
 
             // We want to query both the current context and parent contexts.
             $rolecontextids = $this->context->get_parent_context_ids(true);
 
             // Get users without any role, if needed.
             if (($withoutkey = array_search(-1, $roleids)) !== false) {
-                list($relatedctxsql1, $relatedctxparams1) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx1');
+                list($relatedctxsql1, $norolectxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 
-                $where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
-                $params = array_merge($params, $relatedctxparams1);
-                unset($roleids[$withoutkey]);
+                if ($jointype === $rolesfilter::JOINTYPE_NONE) {
+                    $where .= "(u.id IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
+                } else {
+                    $where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
+                }
+
+                $params = array_merge($params, $norolectxparams);
 
+                if ($withoutkey !== false) {
+                    unset($roleids[$withoutkey]);
+                }
+
+                // Join if any roles will be included.
                 if (!empty($roleids)) {
-                    // Currently only handle 'Any' (logical OR) case within filters.
-                    // This will need to be extended to support 'All'/'None'.
-                    $where .= ' OR ';
+                    // The NOT case is replaced with AND to prevent a double negative.
+                    $where .= $jointype === $rolesfilter::JOINTYPE_NONE ? ' AND ' : $wherejoin;
                 }
             }
 
             // Get users with specified roles, if needed.
             if (!empty($roleids)) {
-                list($relatedctxsql2, $relatedctxparams2) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx2');
-                list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED);
-
-                $where .= "(u.id IN (
-                                  SELECT userid
-                                    FROM {role_assignments}
-                                   WHERE roleid {$roleidssql}
-                                     AND contextid {$relatedctxsql2})
-                                )";
-                $params = array_merge($params, $roleidsparams, $relatedctxparams2);
+                // All case - need one WHERE per filtered role.
+                if ($rolesfilter::JOINTYPE_ALL === $jointype) {
+                    $numroles = count($roleids);
+                    $rolecount = 1;
+
+                    foreach ($roleids as $roleid) {
+                        list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
+                        list($roleidssql, $roleidparams) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'roleids');
+
+                        $where .= "(u.id IN (
+                                     SELECT userid
+                                       FROM {role_assignments}
+                                      WHERE roleid {$roleidssql}
+                                        AND contextid {$relatedctxsql})
+                                   )";
+
+                        if ($rolecount < $numroles) {
+                            $where .= $wherejoin;
+                            $rolecount++;
+                        }
+
+                        $params = array_merge($params, $roleidparams, $relctxparams);
+                    }
+
+                } else {
+                    // Any / None cases - need one WHERE to cover all filtered roles.
+                    list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
+                    list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED, 'roleids');
+
+                    $where .= "(u.id {$matchinsql} (
+                                 SELECT userid
+                                   FROM {role_assignments}
+                                  WHERE roleid {$roleidssql}
+                                    AND contextid {$relatedctxsql})
+                               )";
+
+                    $params = array_merge($params, $roleidsparams, $relctxparams);
+                }
             }
         }
 
@@ -484,19 +782,37 @@ class participants_search {
     }
 
     /**
-     * Prepare SQL where clauses and associated parameters for any keyword searches being performed.
+     * Prepare SQL where clause and associated parameters for any keyword searches being performed.
      *
-     * @return array SQL query data in the format ['wheres' => [], 'params' => []].
+     * @return array SQL query data in the format ['where' => '', 'params' => []].
      */
     protected function get_keywords_search_sql(): array {
         global $CFG, $DB, $USER;
 
         $keywords = [];
-        $wheres = [];
+        $where = '';
         $params = [];
+        $keywordsfilter = $this->filterset->get_filter('keywords');
+        $jointype = $keywordsfilter->get_join_type();
+        $notjoin = false;
+
+        // Determine how to match values in the query.
+        switch ($jointype) {
+            case $keywordsfilter::JOINTYPE_ALL:
+                $wherejoin = ' AND ';
+                break;
+            case $keywordsfilter::JOINTYPE_NONE:
+                $wherejoin = ' AND NOT ';
+                $notjoin = true;
+                break;
+            default:
+                // Default to 'Any' jointype.
+                $wherejoin = ' OR ';
+                break;
+        }
 
         if ($this->filterset->has_filter('keywords')) {
-            $keywords = $this->filterset->get_filter('keywords')->get_filter_values();
+            $keywords = $keywordsfilter->get_filter_values();
         }
 
         foreach ($keywords as $index => $keyword) {
@@ -515,6 +831,11 @@ class participants_search {
 
             // Search by email.
             $email = $DB->sql_like('email', ':' . $searchkey2, false, false);
+
+            if ($notjoin) {
+                $email = "(email IS NOT NULL AND {$email})";
+            }
+
             if (!in_array('email', $this->userfields)) {
                 $maildisplay = 'maildisplay' . $index;
                 $userid1 = 'userid' . $index . '1';
@@ -522,15 +843,21 @@ class participants_search {
                 // who aren't allowed to see hidden email addresses.
                 $email = "(". $email ." AND (" .
                         "u.maildisplay <> :$maildisplay " .
-                        "OR u.id = :$userid1". // User can always find himself.
+                        "OR u.id = :$userid1". // Users can always find themselves.
                         "))";
                 $params[$maildisplay] = core_user::MAILDISPLAY_HIDE;
                 $params[$userid1] = $USER->id;
             }
+
             $conditions[] = $email;
 
             // Search by idnumber.
             $idnumber = $DB->sql_like('idnumber', ':' . $searchkey3, false, false);
+
+            if ($notjoin) {
+                $idnumber = "(idnumber IS NOT NULL AND  {$idnumber})";
+            }
+
             if (!in_array('idnumber', $this->userfields)) {
                 $userid2 = 'userid' . $index . '2';
                 // Users who aren't allowed to see idnumbers should at most find themselves
@@ -538,6 +865,7 @@ class participants_search {
                 $idnumber = "(". $idnumber . " AND u.id = :$userid2)";
                 $params[$userid2] = $USER->id;
             }
+
             $conditions[] = $idnumber;
 
             if (!empty($CFG->showuseridentity)) {
@@ -551,6 +879,11 @@ class participants_search {
                     $param = $searchkey3 . $extrasearchfield;
                     $condition = $DB->sql_like($extrasearchfield, ':' . $param, false, false);
                     $params[$param] = "%$keyword%";
+
+                    if ($notjoin) {
+                        $condition = "($extrasearchfield IS NOT NULL AND {$condition})";
+                    }
+
                     if (!in_array($extrasearchfield, $this->userfields)) {
                         // User cannot see this field, but allow match if their own account.
                         $userid3 = 'userid' . $index . '3' . $extrasearchfield;
@@ -563,21 +896,48 @@ class participants_search {
 
             // Search by middlename.
             $middlename = $DB->sql_like('middlename', ':' . $searchkey4, false, false);
+
+            if ($notjoin) {
+                $middlename = "(middlename IS NOT NULL AND {$middlename})";
+            }
+
             $conditions[] = $middlename;
 
             // Search by alternatename.
             $alternatename = $DB->sql_like('alternatename', ':' . $searchkey5, false, false);
+
+            if ($notjoin) {
+                $alternatename = "(alternatename IS NOT NULL AND {$alternatename})";
+            }
+
             $conditions[] = $alternatename;
 
             // Search by firstnamephonetic.
             $firstnamephonetic = $DB->sql_like('firstnamephonetic', ':' . $searchkey6, false, false);
+
+            if ($notjoin) {
+                $firstnamephonetic = "(firstnamephonetic IS NOT NULL AND {$firstnamephonetic})";
+            }
+
             $conditions[] = $firstnamephonetic;
 
             // Search by lastnamephonetic.
             $lastnamephonetic = $DB->sql_like('lastnamephonetic', ':' . $searchkey7, false, false);
+
+            if ($notjoin) {
+                $lastnamephonetic = "(lastnamephonetic IS NOT NULL AND {$lastnamephonetic})";
+            }
+
             $conditions[] = $lastnamephonetic;
 
-            $wheres[] = "(". implode(" OR ", $conditions) .") ";
+            if (!empty($where)) {
+                $where .= $wherejoin;
+            } else if ($jointype === $keywordsfilter::JOINTYPE_NONE) {
+                // Join type 'None' requires the WHERE to begin with NOT.
+                $where .= ' NOT ';
+            }
+
+            $where .= "(". implode(" OR ", $conditions) .") ";
             $params[$searchkey1] = "%$keyword%";
             $params[$searchkey2] = "%$keyword%";
             $params[$searchkey3] = "%$keyword%";
@@ -588,7 +948,7 @@ class participants_search {
         }
 
         return [
-            'wheres' => $wheres,
+            'where' => $where,
             'params' => $params,
         ];
     }
index 1b669b6..078aa0b 100644 (file)
@@ -1537,41 +1537,68 @@ function user_get_participants($courseid, $groupid = 0, $accesssince, $roleid, $
 }
 
 /**
- * Returns SQL that can be used to limit a query to a period where the user last accessed a course.
+ * Returns SQL that can be used to limit a query to a period where the user last accessed / did not access a course.
  *
- * @param int $accesssince The time since last access
+ * @param int $accesssince The unix timestamp to compare to users' last access
  * @param string $tableprefix
+ * @param bool $haveaccessed Whether to match against users who HAVE accessed since $accesssince (optional)
  * @return string
  */
-function user_get_course_lastaccess_sql($accesssince = null, $tableprefix = 'ul') {
-    if (empty($accesssince)) {
-        return '';
-    }
-
-    if ($accesssince == -1) { // Never.
-        return $tableprefix . '.timeaccess = 0';
-    } else {
-        return $tableprefix . '.timeaccess != 0 AND ' . $tableprefix . '.timeaccess < ' . $accesssince;
-    }
+function user_get_course_lastaccess_sql($accesssince = null, $tableprefix = 'ul', $haveaccessed = false) {
+    return user_get_lastaccess_sql('timeaccess', $accesssince, $tableprefix, $haveaccessed);
 }
 
 /**
- * Returns SQL that can be used to limit a query to a period where the user last accessed the system.
+ * Returns SQL that can be used to limit a query to a period where the user last accessed / did not access the system.
  *
- * @param int $accesssince The time since last access
+ * @param int $accesssince The unix timestamp to compare to users' last access
  * @param string $tableprefix
+ * @param bool $haveaccessed Whether to match against users who HAVE accessed since $accesssince (optional)
+ * @return string
+ */
+function user_get_user_lastaccess_sql($accesssince = null, $tableprefix = 'u', $haveaccessed = false) {
+    return user_get_lastaccess_sql('lastaccess', $accesssince, $tableprefix, $haveaccessed);
+}
+
+/**
+ * Returns SQL that can be used to limit a query to a period where the user last accessed or
+ * did not access something recorded by a given table.
+ *
+ * @param string $columnname The name of the access column to check against
+ * @param int $accesssince The unix timestamp to compare to users' last access
+ * @param string $tableprefix The query prefix of the table to check
+ * @param bool $haveaccessed Whether to match against users who HAVE accessed since $accesssince (optional)
  * @return string
  */
-function user_get_user_lastaccess_sql($accesssince = null, $tableprefix = 'u') {
+function user_get_lastaccess_sql($columnname, $accesssince, $tableprefix, $haveaccessed = false) {
     if (empty($accesssince)) {
         return '';
     }
 
-    if ($accesssince == -1) { // Never.
-        return $tableprefix . '.lastaccess = 0';
+    // Only users who have accessed since $accesssince.
+    if ($haveaccessed) {
+        if ($accesssince == -1) {
+            // Include all users who have logged in at some point.
+            $sql = "({$tableprefix}.{$columnname} IS NOT NULL AND {$tableprefix}.{$columnname} != 0)";
+        } else {
+            // Users who have accessed since the specified time.
+            $sql = "{$tableprefix}.{$columnname} IS NOT NULL AND {$tableprefix}.{$columnname} != 0
+                AND {$tableprefix}.{$columnname} >= {$accesssince}";
+        }
     } else {
-        return $tableprefix . '.lastaccess != 0 AND ' . $tableprefix . '.lastaccess < ' . $accesssince;
+        // Only users who have not accessed since $accesssince.
+
+        if ($accesssince == -1) {
+            // Users who have never accessed.
+            $sql = "({$tableprefix}.{$columnname} IS NULL OR {$tableprefix}.{$columnname} = 0)";
+        } else {
+            // Users who have not accessed since the specified time.
+            $sql = "({$tableprefix}.{$columnname} IS NULL
+                    OR ({$tableprefix}.{$columnname} != 0 AND {$tableprefix}.{$columnname} < {$accesssince}))";
+        }
     }
+
+    return $sql;
 }
 
 /**
index 0290830..3b6fc75 100644 (file)
@@ -330,6 +330,90 @@ class participants_search_test extends advanced_testcase {
                             'b',
                         ],
                     ],
+                    'ALL: Filter on student, teacher' => (object) [
+                        'roles' => ['student', 'teacher'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ALL: Filter on student, manager (category level role))' => (object) [
+                        'roles' => ['student', 'manager'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ALL: Filter on student, coursecreator (not assigned))' => (object) [
+                        'roles' => ['student', 'coursecreator'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No role filter' => (object) [
+                        'roles' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 8,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student' => (object) [
+                        'roles' => ['student'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 6,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student, teacher' => (object) [
+                        'roles' => ['student', 'teacher'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student, manager (category level role))' => (object) [
+                        'roles' => ['student', 'manager'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                        ],
+                    ],
+                    'NONE: Filter on student, coursecreator (not assigned))' => (object) [
+                        'roles' => ['student', 'coursecreator'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 6,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
                 ],
             ],
             'Users with multiple roles' => (object) [
@@ -524,6 +608,135 @@ class participants_search_test extends advanced_testcase {
                             'e',
                         ],
                     ],
+                    'ALL: Filter on student, teacher' => (object) [
+                        'roles' => ['student', 'teacher'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'b',
+                        ],
+                    ],
+                    'ALL: Filter on teacher, editingteacher' => (object) [
+                        'roles' => ['teacher', 'editingteacher'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on student, manager (category level role)' => (object) [
+                        'roles' => ['student', 'manager'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ALL: Filter on student, coursecreator (not assigned)' => (object) [
+                        'roles' => ['student', 'coursecreator'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No role filter' => (object) [
+                        'roles' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 8,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student' => (object) [
+                        'roles' => ['student'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 6,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on teacher' => (object) [
+                        'roles' => ['teacher'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'c',
+                            'd',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on editingteacher' => (object) [
+                        'roles' => ['editingteacher'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student, teacher' => (object) [
+                        'roles' => ['student', 'teacher'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student, teacher' => (object) [
+                        'roles' => ['teacher', 'editingteacher'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'g',
+                            'h',
+                        ],
+                    ],
+                    'NONE: Filter on student, manager (category level role)' => (object) [
+                        'roles' => ['student', 'manager'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                        ],
+                    ],
+                    'NONE: Filter on student, coursecreator (not assigned)' => (object) [
+                        'roles' => ['student', 'coursecreator'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 6,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                            'f',
+                            'g',
+                            'h',
+                        ],
+                    ],
                 ],
             ],
         ];
@@ -560,6 +773,12 @@ class participants_search_test extends advanced_testcase {
         $users = [];
 
         foreach ($usersdata as $username => $userdata) {
+            // Prevent randomly generated field values that may cause false fails.
+            $userdata['firstnamephonetic'] = $userdata['firstnamephonetic'] ?? $userdata['firstname'];
+            $userdata['lastnamephonetic'] = $userdata['lastnamephonetic'] ?? $userdata['lastname'];
+            $userdata['middlename'] = $userdata['middlename'] ?? '';
+            $userdata['alternatename'] = $userdata['alternatename'] ?? $username;
+
             $user = $this->getDataGenerator()->create_user($userdata);
             $this->getDataGenerator()->enrol_user($user->id, $course->id, 'student');
             $users[$username] = $user;
@@ -603,7 +822,7 @@ class participants_search_test extends advanced_testcase {
      */
     public function keywords_provider(): array {
         $tests = [
-            // Users where the keyword matches firstname, lastname, or username.
+            // Users where the keyword matches basic user fields such as names and email.
             'Users with basic names' => (object) [
                 'users' => [
                     'adam.ant' => [
@@ -613,19 +832,25 @@ class participants_search_test extends advanced_testcase {
                     'barbara.bennett' => [
                         'firstname' => 'Barbara',
                         'lastname' => 'Bennett',
+                        'alternatename' => 'Babs',
+                        'firstnamephonetic' => 'Barbra',
+                        'lastnamephonetic' => 'Benit',
                     ],
                     'colin.carnforth' => [
                         'firstname' => 'Colin',
                         'lastname' => 'Carnforth',
+                        'middlename' => 'Jeffery',
                     ],
                     'tony.rogers' => [
                         'firstname' => 'Anthony',
                         'lastname' => 'Rogers',
+                        'lastnamephonetic' => 'Rowjours',
                     ],
                     'sarah.rester' => [
                         'firstname' => 'Sarah',
                         'lastname' => 'Rester',
                         'email' => 'zazu@example.com',
+                        'firstnamephonetic' => 'Sera',
                     ],
                 ],
                 'expect' => [
@@ -642,7 +867,7 @@ class participants_search_test extends advanced_testcase {
                             'sarah.rester',
                         ],
                     ],
-                    'ANY: First name only' => (object) [
+                    'ANY: Filter on first name only' => (object) [
                         'keywords' => ['adam'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 1,
@@ -650,7 +875,7 @@ class participants_search_test extends advanced_testcase {
                             'adam.ant',
                         ],
                     ],
-                    'ANY: Last name only' => (object) [
+                    'ANY: Filter on last name only' => (object) [
                         'keywords' => ['BeNNeTt'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 1,
@@ -658,7 +883,7 @@ class participants_search_test extends advanced_testcase {
                             'barbara.bennett',
                         ],
                     ],
-                    'ANY: First/Last name' => (object) [
+                    'ANY: Filter on first/Last name' => (object) [
                         'keywords' => ['ant'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 2,
@@ -667,13 +892,21 @@ class participants_search_test extends advanced_testcase {
                             'tony.rogers',
                         ],
                     ],
-                    'ANY: Username (no match)' => (object) [
+                    'ANY: Filter on middlename only' => (object) [
+                        'keywords' => ['Jeff'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'colin.carnforth',
+                        ],
+                    ],
+                    'ANY: Filter on username (no match)' => (object) [
                         'keywords' => ['sara.rester'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 0,
                         'expectedusers' => [],
                     ],
-                    'ANY: Email' => (object) [
+                    'ANY: Filter on email only' => (object) [
                         'keywords' => ['zazu'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 1,
@@ -681,6 +914,50 @@ class participants_search_test extends advanced_testcase {
                             'sarah.rester',
                         ],
                     ],
+                    'ANY: Filter on first name phonetic only' => (object) [
+                        'keywords' => ['Sera'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'sarah.rester',
+                        ],
+                    ],
+                    'ANY: Filter on last name phonetic only' => (object) [
+                        'keywords' => ['jour'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'tony.rogers',
+                        ],
+                    ],
+                    'ANY: Filter on alternate name only' => (object) [
+                        'keywords' => ['Babs'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'barbara.bennett',
+                        ],
+                    ],
+                    'ANY: Filter on multiple keywords (first/middle/last name)' => (object) [
+                        'keywords' => ['ant', 'Jeff', 'rog'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'colin.carnforth',
+                            'tony.rogers',
+                        ],
+                    ],
+                    'ANY: Filter on multiple keywords (phonetic/alternate names)' => (object) [
+                        'keywords' => ['era', 'Bab', 'ours'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'barbara.bennett',
+                            'sarah.rester',
+                            'tony.rogers',
+                        ],
+                    ],
 
                     // Tests for jointype: ALL.
                     'ALL: No filter' => (object) [
@@ -695,7 +972,7 @@ class participants_search_test extends advanced_testcase {
                             'sarah.rester',
                         ],
                     ],
-                    'ALL: First name only' => (object) [
+                    'ALL: Filter on first name only' => (object) [
                         'keywords' => ['adam'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 1,
@@ -703,7 +980,7 @@ class participants_search_test extends advanced_testcase {
                             'adam.ant',
                         ],
                     ],
-                    'ALL: Last name only' => (object) [
+                    'ALL: Filter on last name only' => (object) [
                         'keywords' => ['BeNNeTt'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 1,
@@ -711,7 +988,7 @@ class participants_search_test extends advanced_testcase {
                             'barbara.bennett',
                         ],
                     ],
-                    'ALL: First/Last name' => (object) [
+                    'ALL: Filter on first/Last name' => (object) [
                         'keywords' => ['ant'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 2,
@@ -720,13 +997,21 @@ class participants_search_test extends advanced_testcase {
                             'tony.rogers',
                         ],
                     ],
-                    'ALL: Username (no match)' => (object) [
+                    'ALL: Filter on middlename only' => (object) [
+                        'keywords' => ['Jeff'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'colin.carnforth',
+                        ],
+                    ],
+                    'ALL: Filter on username (no match)' => (object) [
                         'keywords' => ['sara.rester'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 0,
                         'expectedusers' => [],
                     ],
-                    'ALL: Email' => (object) [
+                    'ALL: Filter on email only' => (object) [
                         'keywords' => ['zazu'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 1,
@@ -734,7 +1019,31 @@ class participants_search_test extends advanced_testcase {
                             'sarah.rester',
                         ],
                     ],
-                    'ALL: Multiple keywords' => (object) [
+                    'ALL: Filter on first name phonetic only' => (object) [
+                        'keywords' => ['Sera'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'sarah.rester',
+                        ],
+                    ],
+                    'ALL: Filter on last name phonetic only' => (object) [
+                        'keywords' => ['jour'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'tony.rogers',
+                        ],
+                    ],
+                    'ALL: Filter on alternate name only' => (object) [
+                        'keywords' => ['Babs'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'barbara.bennett',
+                        ],
+                    ],
+                    'ALL: Filter on multiple keywords (first/last name)' => (object) [
                         'keywords' => ['ant', 'rog'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 1,
@@ -742,14 +1051,168 @@ class participants_search_test extends advanced_testcase {
                             'tony.rogers',
                         ],
                     ],
-                ],
-            ],
-        ];
+                    'ALL: Filter on multiple keywords (first/middle/last name)' => (object) [
+                        'keywords' => ['ant', 'Jeff', 'rog'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ALL: Filter on multiple keywords (phonetic/alternate names)' => (object) [
+                        'keywords' => ['Bab', 'bra', 'nit'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'barbara.bennett',
+                        ],
+                    ],
 
-        $finaltests = [];
-        foreach ($tests as $testname => $testdata) {
-            foreach ($testdata->expect as $expectname => $expectdata) {
-                $finaltests["{$testname} => {$expectname}"] = [
+                    // Tests for jointype: NONE.
+                    'NONE: No filter' => (object) [
+                        'keywords' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on first name only' => (object) [
+                        'keywords' => ['ara'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'colin.carnforth',
+                            'tony.rogers',
+                        ],
+                    ],
+                    'NONE: Filter on last name only' => (object) [
+                        'keywords' => ['BeNNeTt'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on first/Last name' => (object) [
+                        'keywords' => ['ar'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'tony.rogers',
+                        ],
+                    ],
+                    'NONE: Filter on middlename only' => (object) [
+                        'keywords' => ['Jeff'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on username (no match)' => (object) [
+                        'keywords' => ['sara.rester'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on email' => (object) [
+                        'keywords' => ['zazu'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                        ],
+                    ],
+                    'NONE: Filter on first name phonetic only' => (object) [
+                        'keywords' => ['Sera'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                        ],
+                    ],
+                    'NONE: Filter on last name phonetic only' => (object) [
+                        'keywords' => ['jour'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on alternate name only' => (object) [
+                        'keywords' => ['Babs'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on multiple keywords (first/last name)' => (object) [
+                        'keywords' => ['ara', 'rog'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'colin.carnforth',
+                        ],
+                    ],
+                    'NONE: Filter on multiple keywords (first/middle/last name)' => (object) [
+                        'keywords' => ['ant', 'Jeff', 'rog'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'barbara.bennett',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'NONE: Filter on multiple keywords (phonetic/alternate names)' => (object) [
+                        'keywords' => ['Bab', 'bra', 'nit'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                ],
+            ],
+        ];
+
+        $finaltests = [];
+        foreach ($tests as $testname => $testdata) {
+            foreach ($testdata->expect as $expectname => $expectdata) {
+                $finaltests["{$testname} => {$expectname}"] = [
                     'users' => $testdata->users,
                     'keywords' => $expectdata->keywords,
                     'jointype' => $expectdata->jointype,
@@ -860,47 +1323,57 @@ class participants_search_test extends advanced_testcase {
                             'self' => ENROL_USER_SUSPENDED,
                         ]
                     ],
+                    'e' => [
+                        'statuses' => [
+                            'manual' => ENROL_USER_ACTIVE,
+                            'self' => ENROL_USER_SUSPENDED,
+                        ]
+                    ],
                 ],
                 'expect' => [
                     // Tests for jointype: ANY.
                     'ANY: No filter' => (object) [
                         'statuses' => [],
                         'jointype' => filter::JOINTYPE_ANY,
-                        'count' => 4,
+                        'count' => 5,
                         'expectedusers' => [
                             'a',
                             'b',
                             'c',
                             'd',
+                            'e',
                         ],
                     ],
-                    'ANY: Active only' => (object) [
+                    'ANY: Filter on active only' => (object) [
                         'statuses' => [ENROL_USER_ACTIVE],
                         'jointype' => filter::JOINTYPE_ANY,
-                        'count' => 2,
+                        'count' => 3,
                         'expectedusers' => [
                             'a',
                             'b',
+                            'e',
                         ],
                     ],
-                    'ANY: Suspended only' => (object) [
+                    'ANY: Filter on suspended only' => (object) [
                         'statuses' => [ENROL_USER_SUSPENDED],
                         'jointype' => filter::JOINTYPE_ANY,
-                        'count' => 2,
+                        'count' => 3,
                         'expectedusers' => [
                             'c',
                             'd',
+                            'e',
                         ],
                     ],
-                    'ANY: Multiple statuses' => (object) [
+                    'ANY: Filter on multiple statuses' => (object) [
                         'statuses' => [ENROL_USER_ACTIVE, ENROL_USER_SUSPENDED],
                         'jointype' => filter::JOINTYPE_ANY,
-                        'count' => 4,
+                        'count' => 5,
                         'expectedusers' => [
                             'a',
                             'b',
                             'c',
                             'd',
+                            'e',
                         ],
                     ],
 
@@ -908,32 +1381,83 @@ class participants_search_test extends advanced_testcase {
                     'ALL: No filter' => (object) [
                        'statuses' => [],
                         'jointype' => filter::JOINTYPE_ALL,
-                        'count' => 4,
+                        'count' => 5,
                         'expectedusers' => [
                             'a',
                             'b',
                             'c',
                             'd',
+                            'e',
                         ],
                     ],
-                    'ALL: Active only' => (object) [
+                    'ALL: Filter on active only' => (object) [
                         'statuses' => [ENROL_USER_ACTIVE],
                         'jointype' => filter::JOINTYPE_ALL,
-                        'count' => 2,
+                        'count' => 3,
                         'expectedusers' => [
                             'a',
                             'b',
+                            'e',
                         ],
                     ],
-                    'ALL: Suspended only' => (object) [
+                    'ALL: Filter on suspended only' => (object) [
                         'statuses' => [ENROL_USER_SUSPENDED],
                         'jointype' => filter::JOINTYPE_ALL,
-                        'count' => 2,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on multiple statuses' => (object) [
+                        'statuses' => [ENROL_USER_ACTIVE, ENROL_USER_SUSPENDED],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'e',
+                        ],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No filter' => (object) [
+                       'statuses' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'NONE: Filter on active only' => (object) [
+                        'statuses' => [ENROL_USER_ACTIVE],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
                         'expectedusers' => [
                             'c',
                             'd',
+                            'e',
+                        ],
+                    ],
+                    'NONE: Filter on suspended only' => (object) [
+                        'statuses' => [ENROL_USER_SUSPENDED],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'e',
                         ],
                     ],
+                    'NONE: Filter on multiple statuses' => (object) [
+                        'statuses' => [ENROL_USER_ACTIVE, ENROL_USER_SUSPENDED],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
                 ],
             ],
         ];
@@ -1063,7 +1587,7 @@ class participants_search_test extends advanced_testcase {
                             'c',
                         ],
                     ],
-                    'ANY: Manual enrolments only' => (object) [
+                    'ANY: Filter by manual enrolments only' => (object) [
                         'enrolmethods' => ['manual'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 2,
@@ -1072,7 +1596,7 @@ class participants_search_test extends advanced_testcase {
                             'c',
                         ],
                     ],
-                    'ANY: Self enrolments only' => (object) [
+                    'ANY: Filter by self enrolments only' => (object) [
                         'enrolmethods' => ['self'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 2,
@@ -1081,7 +1605,7 @@ class participants_search_test extends advanced_testcase {
                             'c',
                         ],
                     ],
-                    'ANY: Multiple enrolment methods' => (object) [
+                    'ANY: Filter by multiple enrolment methods' => (object) [
                         'enrolmethods' => ['manual', 'self'],
                         'jointype' => filter::JOINTYPE_ANY,
                         'count' => 3,
@@ -1103,7 +1627,7 @@ class participants_search_test extends advanced_testcase {
                             'c',
                         ],
                     ],
-                    'ALL: Manual enrolments only' => (object) [
+                    'ALL: Filter by manual enrolments only' => (object) [
                         'enrolmethods' => ['manual'],
                         'jointype' => filter::JOINTYPE_ALL,
                         'count' => 2,
@@ -1112,6 +1636,40 @@ class participants_search_test extends advanced_testcase {
                             'c',
                         ],
                     ],
+                    'ALL: Filter by multiple enrolment methods' => (object) [
+                        'enrolmethods' => ['manual', 'self'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'c',
+                        ],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No filter' => (object) [
+                       'enrolmethods' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                        ],
+                    ],
+                    'NONE: Filter by manual enrolments only' => (object) [
+                        'enrolmethods' => ['manual'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'b',
+                        ],
+                    ],
+                    'NONE: Filter by multiple enrolment methods' => (object) [
+                        'enrolmethods' => ['manual', 'self'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
                 ],
             ],
         ];
@@ -1131,4 +1689,1145 @@ class participants_search_test extends advanced_testcase {
 
         return $finaltests;
     }
+
+    /**
+     * Ensure that the groups filter works as expected with the provided test cases.
+     *
+     * @param array $usersdata The list of users to create
+     * @param array $groupsavailable The names of groups that should be created in the course
+     * @param array $filtergroups The names of groups to filter by
+     * @param int $jointype The join type to use when combining filter values
+     * @param int $count The expected count
+     * @param array $expectedusers
+     * @dataProvider groups_provider
+     */
+    public function test_groups_filter(array $usersdata, array $groupsavailable, array $filtergroups, int $jointype, int $count,
+            array $expectedusers): void {
+
+        $course = $this->getDataGenerator()->create_course();
+        $coursecontext = context_course::instance($course->id);
+        $users = [];
+
+        // Prepare data for filtering by users in no groups.
+        $nogroupsdata = (object) [
+            'id' => USERSWITHOUTGROUP,
+        ];
+
+        // Map group names to group data.
+         $groupsdata = ['nogroups' => $nogroupsdata];
+        foreach ($groupsavailable as $groupname) {
+            $groupinfo = [
+                'courseid' => $course->id,
+                'name' => $groupname,
+            ];
+
+            $groupsdata[$groupname] = $this->getDataGenerator()->create_group($groupinfo);
+        }
+
+        foreach ($usersdata as $username => $userdata) {
+            $user = $this->getDataGenerator()->create_user(['username' => $username]);
+            $this->getDataGenerator()->enrol_user($user->id, $course->id, 'student');
+
+            if (array_key_exists('groups', $userdata)) {
+                foreach ($userdata['groups'] as $groupname) {
+                    $userinfo = [
+                        'userid' => $user->id,
+                        'groupid' => (int) $groupsdata[$groupname]->id,
+                    ];
+                    $this->getDataGenerator()->create_group_member($userinfo);
+                }
+            }
+
+            $users[$username] = $user;
+        }
+
+        // Create a secondary course with users. We should not see these users.
+        $this->create_course_with_users(1, 1, 1, 1);
+
+        // Create the basic filter.
+        $filterset = new participants_filterset();
+        $filterset->add_filter(new integer_filter('courseid', null, [(int) $course->id]));
+
+        // Create the groups filter.
+        $groupsfilter = new integer_filter('groups');
+        $filterset->add_filter($groupsfilter);
+
+        // Configure the filter.
+        foreach ($filtergroups as $filtergroupname) {
+            $groupsfilter->add_filter_value((int) $groupsdata[$filtergroupname]->id);
+        }
+        $groupsfilter->set_join_type($jointype);
+
+        // Run the search.
+        $search = new participants_search($course, $coursecontext, $filterset);
+        $rs = $search->get_participants();
+        $this->assertInstanceOf(moodle_recordset::class, $rs);
+        $records = $this->convert_recordset_to_array($rs);
+
+        $this->assertCount($count, $records);
+        $this->assertEquals($count, $search->get_total_participants_count());
+
+        foreach ($expectedusers as $expecteduser) {
+            $this->assertArrayHasKey($users[$expecteduser]->id, $records);
+        }
+    }
+
+    /**
+     * Data provider for groups filter tests.
+     *
+     * @return array
+     */
+    public function groups_provider(): array {
+        $tests = [
+            'Users in different groups' => (object) [
+                'groupsavailable' => [
+                    'groupa',
+                    'groupb',
+                    'groupc',
+                ],
+                'users' => [
+                    'a' => [
+                        'groups' => ['groupa'],
+                    ],
+                    'b' => [
+                        'groups' => ['groupb'],
+                    ],
+                    'c' => [
+                        'groups' => ['groupa', 'groupb'],
+                    ],
+                    'd' => [
+                        'groups' => [],
+                    ],
+                ],
+                'expect' => [
+                    // Tests for jointype: ANY.
+                    'ANY: No filter' => (object) [
+                        'groups' => [],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                        ],
+                    ],
+                    'ANY: Filter on a single group' => (object) [
+                        'groups' => ['groupa'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'a',
+                            'c',
+                        ],
+                    ],
+                    'ANY: Filter on a group with no members' => (object) [
+                        'groups' => ['groupc'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ANY: Filter on multiple groups' => (object) [
+                        'groups' => ['groupa', 'groupb'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                        ],
+                    ],
+                    'ANY: Filter on members of no groups only' => (object) [
+                        'groups' => ['nogroups'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'd',
+                        ],
+                    ],
+                    'ANY: Filter on a single group or no groups' => (object) [
+                        'groups' => ['groupa', 'nogroups'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'c',
+                            'd',
+                        ],
+                    ],
+                    'ANY: Filter on multiple groups or no groups' => (object) [
+                        'groups' => ['groupa', 'groupb', 'nogroups'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                        ],
+                    ],
+
+                    // Tests for jointype: ALL.
+                    'ALL: No filter' => (object) [
+                        'groups' => [],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                        ],
+                    ],
+                    'ALL: Filter on a single group' => (object) [
+                        'groups' => ['groupa'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'a',
+                            'c',
+                        ],
+                    ],
+                    'ALL: Filter on a group with no members' => (object) [
+                        'groups' => ['groupc'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ALL: Filter on members of no groups only' => (object) [
+                        'groups' => ['nogroups'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'd',
+                        ],
+                    ],
+                    'ALL: Filter on multiple groups' => (object) [
+                        'groups' => ['groupa', 'groupb'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'c',
+                        ],
+                    ],
+                    'ALL: Filter on a single group and no groups' => (object) [
+                        'groups' => ['groupa', 'nogroups'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                    'ALL: Filter on multiple groups and no groups' => (object) [
+                        'groups' => ['groupa', 'groupb', 'nogroups'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No filter' => (object) [
+                        'groups' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                        ],
+                    ],
+                    'NONE: Filter on a single group' => (object) [
+                        'groups' => ['groupa'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'b',
+                            'd',
+                        ],
+                    ],
+                    'NONE: Filter on a group with no members' => (object) [
+                        'groups' => ['groupc'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                        ],
+                    ],
+                    'NONE: Filter on members of no groups only' => (object) [
+                        'groups' => ['nogroups'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                        ],
+                    ],
+                    'NONE: Filter on multiple groups' => (object) [
+                        'groups' => ['groupa', 'groupb'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'd',
+                        ],
+                    ],
+                    'NONE: Filter on a single group and no groups' => (object) [
+                        'groups' => ['groupa', 'nogroups'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'b',
+                        ],
+                    ],
+                    'NONE: Filter on multiple groups and no groups' => (object) [
+                        'groups' => ['groupa', 'groupb', 'nogroups'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                ],
+            ],
+        ];
+
+        $finaltests = [];
+        foreach ($tests as $testname => $testdata) {
+            foreach ($testdata->expect as $expectname => $expectdata) {
+                $finaltests["{$testname} => {$expectname}"] = [
+                    'users' => $testdata->users,
+                    'groupsavailable' => $testdata->groupsavailable,
+                    'filtergroups' => $expectdata->groups,
+                    'jointype' => $expectdata->jointype,
+                    'count' => $expectdata->count,
+                    'expectedusers' => $expectdata->expectedusers,
+                ];
+            }
+        }
+
+        return $finaltests;
+    }
+
+    /**
+     * Ensure that the last access filter works as expected with the provided test cases.
+     *
+     * @param array $usersdata The list of users to create
+     * @param array $accesssince The last access data to filter by
+     * @param int $jointype The join type to use when combining filter values
+     * @param int $count The expected count
+     * @param array $expectedusers
+     * @dataProvider accesssince_provider
+     */
+    public function test_accesssince_filter(array $usersdata, array $accesssince, int $jointype, int $count,
+            array $expectedusers): void {
+
+        $course = $this->getDataGenerator()->create_course();
+        $coursecontext = context_course::instance($course->id);
+        $users = [];
+
+        foreach ($usersdata as $username => $userdata) {
+            $usertimestamp = empty($userdata['lastlogin']) ? 0 : strtotime($userdata['lastlogin']);
+
+            $user = $this->getDataGenerator()->create_user(['username' => $username]);
+            $this->getDataGenerator()->enrol_user($user->id, $course->id, 'student');
+
+            // Create the record of the user's last access to the course.
+            if ($usertimestamp > 0) {
+                $this->getDataGenerator()->create_user_course_lastaccess($user, $course, $usertimestamp);
+            }
+
+            $users[$username] = $user;
+        }
+
+        // Create a secondary course with users. We should not see these users.
+        $this->create_course_with_users(1, 1, 1, 1);
+
+        // Create the basic filter.
+        $filterset = new participants_filterset();
+        $filterset->add_filter(new integer_filter('courseid', null, [(int) $course->id]));
+
+        // Create the last access filter.
+        $lastaccessfilter = new integer_filter('accesssince');
+        $filterset->add_filter($lastaccessfilter);
+
+        // Configure the filter.
+        foreach ($accesssince as $accessstring) {
+            $lastaccessfilter->add_filter_value(strtotime($accessstring));
+        }
+        $lastaccessfilter->set_join_type($jointype);
+
+        // Run the search.
+        $search = new participants_search($course, $coursecontext, $filterset);
+        $rs = $search->get_participants();
+        $this->assertInstanceOf(moodle_recordset::class, $rs);
+        $records = $this->convert_recordset_to_array($rs);
+
+        $this->assertCount($count, $records);
+        $this->assertEquals($count, $search->get_total_participants_count());
+
+        foreach ($expectedusers as $expecteduser) {
+            $this->assertArrayHasKey($users[$expecteduser]->id, $records);
+        }
+    }
+
+    /**
+     * Data provider for last access filter tests.
+     *
+     * @return array
+     */
+    public function accesssince_provider(): array {
+        $tests = [
+            // Users with different last access times.
+            'Users in different groups' => (object) [
+                'users' => [
+                    'a' => [
+                        'lastlogin' => '-3 days',
+                    ],
+                    'b' => [
+                        'lastlogin' => '-2 weeks',
+                    ],
+                    'c' => [
+                        'lastlogin' => '-5 months',
+                    ],
+                    'd' => [
+                        'lastlogin' => '-11 months',
+                    ],
+                    'e' => [
+                        // Never logged in.
+                        'lastlogin' => '',
+                    ],
+                ],
+                'expect' => [
+                    // Tests for jointype: ANY.
+                    'ANY: No filter' => (object) [
+                        'accesssince' => [],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ANY: Filter on last login more than 1 year ago' => (object) [
+                        'accesssince' => ['-1 year'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'e',
+                        ],
+                    ],
+                    'ANY: Filter on last login more than 6 months ago' => (object) [
+                        'accesssince' => ['-6 months'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ANY: Filter on last login more than 3 weeks ago' => (object) [
+                        'accesssince' => ['-3 weeks'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ANY: Filter on last login more than 5 days ago' => (object) [
+                        'accesssince' => ['-5 days'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ANY: Filter on last login more than 2 days ago' => (object) [
+                        'accesssince' => ['-2 days'],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+
+                    // Tests for jointype: ALL.
+                    'ALL: No filter' => (object) [
+                        'accesssince' => [],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on last login more than 1 year ago' => (object) [
+                        'accesssince' => ['-1 year'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on last login more than 6 months ago' => (object) [
+                        'accesssince' => ['-6 months'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on last login more than 3 weeks ago' => (object) [
+                        'accesssince' => ['-3 weeks'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on last login more than 5 days ago' => (object) [
+                        'accesssince' => ['-5 days'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'ALL: Filter on last login more than 2 days ago' => (object) [
+                        'accesssince' => ['-2 days'],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No filter' => (object) [
+                        'accesssince' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 5,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                            'e',
+                        ],
+                    ],
+                    'NONE: Filter on last login more than 1 year ago' => (object) [
+                        'accesssince' => ['-1 year'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                            'd',
+                        ],
+                    ],
+                    'NONE: Filter on last login more than 6 months ago' => (object) [
+                        'accesssince' => ['-6 months'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                            'c',
+                        ],
+                    ],
+                    'NONE: Filter on last login more than 3 weeks ago' => (object) [
+                        'accesssince' => ['-3 weeks'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 2,
+                        'expectedusers' => [
+                            'a',
+                            'b',
+                        ],
+                    ],
+                    'NONE: Filter on last login more than 5 days ago' => (object) [
+                        'accesssince' => ['-5 days'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'a',
+                        ],
+                    ],
+                    'NONE: Filter on last login more than 2 days ago' => (object) [
+                        'accesssince' => ['-2 days'],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 0,
+                        'expectedusers' => [],
+                    ],
+                ],
+            ],
+        ];
+
+        $finaltests = [];
+        foreach ($tests as $testname => $testdata) {
+            foreach ($testdata->expect as $expectname => $expectdata) {
+                $finaltests["{$testname} => {$expectname}"] = [
+                    'users' => $testdata->users,
+                    'accesssince' => $expectdata->accesssince,
+                    'jointype' => $expectdata->jointype,
+                    'count' => $expectdata->count,
+                    'expectedusers' => $expectdata->expectedusers,
+                ];
+            }
+        }
+
+        return $finaltests;
+    }
+
+    /**
+     * Ensure that the joins between filters in the filterset work as expected with the provided test cases.
+     *
+     * @param array $usersdata The list of users to create
+     * @param array $filterdata The data to filter by
+     * @param array $groupsavailable The names of groups that should be created in the course
+     * @param int $jointype The join type to used between each filter being applied
+     * @param int $count The expected count
+     * @param array $expectedusers
+     * @dataProvider filterset_joins_provider
+     */
+    public function test_filterset_joins(array $usersdata, array $filterdata, array $groupsavailable, int $jointype, int $count,
+            array $expectedusers): void {
+        global $DB;
+
+        // Ensure sufficient capabilities to view all statuses.
+        $this->setAdminUser();
+
+        // Remove the default role.
+        set_config('roleid', 0, 'enrol_manual');
+
+        $course = $this->getDataGenerator()->create_course();
+        $coursecontext = context_course::instance($course->id);
+        $roles = $DB->get_records_menu('role', [], '', 'shortname, id');
+        $users = [];
+
+        // Ensure all enrolment methods are enabled (and mapped where required for filtering later).
+        $enrolinstances = enrol_get_instances($course->id, false);
+        $enrolinstancesmap = [];
+        foreach ($enrolinstances as $instance) {
+            $plugin = enrol_get_plugin($instance->enrol);
+            $plugin->update_status($instance, ENROL_INSTANCE_ENABLED);
+
+            $enrolinstancesmap[$instance->enrol] = (int) $instance->id;
+        }
+
+        // Create the required course groups and mapping.
+        $nogroupsdata = (object) [
+            'id' => USERSWITHOUTGROUP,
+        ];
+
+         $groupsdata = ['nogroups' => $nogroupsdata];
+        foreach ($groupsavailable as $groupname) {
+            $groupinfo = [
+                'courseid' => $course->id,
+                'name' => $groupname,
+            ];
+
+            $groupsdata[$groupname] = $this->getDataGenerator()->create_group($groupinfo);
+        }
+
+        // Create test users.
+        foreach ($usersdata as $username => $userdata) {
+            $usertimestamp = empty($userdata['lastlogin']) ? 0 : strtotime($userdata['lastlogin']);
+            unset($userdata['lastlogin']);
+
+            // Prevent randomly generated field values that may cause false fails.
+            $userdata['firstnamephonetic'] = $userdata['firstnamephonetic'] ?? $userdata['firstname'];
+            $userdata['lastnamephonetic'] = $userdata['lastnamephonetic'] ?? $userdata['lastname'];
+            $userdata['middlename'] = $userdata['middlename'] ?? '';
+            $userdata['alternatename'] = $userdata['alternatename'] ?? $username;
+
+            $user = $this->getDataGenerator()->create_user($userdata);
+
+            foreach ($userdata['enrolments'] as $details) {
+                $this->getDataGenerator()->enrol_user($user->id, $course->id, $roles[$details['role']],
+                        $details['method'], 0, 0, $details['status']);
+            }
+
+            foreach ($userdata['groups'] as $groupname) {
+                $userinfo = [
+                    'userid' => $user->id,
+                    'groupid' => (int) $groupsdata[$groupname]->id,
+                ];
+                $this->getDataGenerator()->create_group_member($userinfo);
+            }
+
+            if ($usertimestamp > 0) {
+                $this->getDataGenerator()->create_user_course_lastaccess($user, $course, $usertimestamp);
+            }
+
+            $users[$username] = $user;
+        }
+
+        // Create a secondary course with users. We should not see these users.
+        $this->create_course_with_users(10, 10, 10, 10);
+
+        // Create the basic filterset.
+        $filterset = new participants_filterset();
+        $filterset->set_join_type($jointype);
+        $filterset->add_filter(new integer_filter('courseid', null, [(int) $course->id]));
+
+        // Apply the keywords filter if required.
+        if (array_key_exists('keywords', $filterdata)) {
+            $keywordfilter = new string_filter('keywords');
+            $filterset->add_filter($keywordfilter);
+
+            foreach ($filterdata['keywords']['values'] as $keyword) {
+                $keywordfilter->add_filter_value($keyword);
+            }
+            $keywordfilter->set_join_type($filterdata['keywords']['jointype']);
+        }
+
+        // Apply enrolment methods filter if required.
+        if (array_key_exists('enrolmethods', $filterdata)) {
+            $enrolmethodfilter = new integer_filter('enrolments');
+            $filterset->add_filter($enrolmethodfilter);
+
+            foreach ($filterdata['enrolmethods']['values'] as $enrolmethod) {
+                $enrolmethodfilter->add_filter_value($enrolinstancesmap[$enrolmethod]);
+            }
+            $enrolmethodfilter->set_join_type($filterdata['enrolmethods']['jointype']);
+        }
+
+        // Apply roles filter if required.
+        if (array_key_exists('courseroles', $filterdata)) {
+            $rolefilter = new integer_filter('roles');
+            $filterset->add_filter($rolefilter);
+
+            foreach ($filterdata['courseroles']['values'] as $rolename) {
+                $rolefilter->add_filter_value((int) $roles[$rolename]);
+            }
+            $rolefilter->set_join_type($filterdata['courseroles']['jointype']);
+        }
+
+        // Apply status filter if required.
+        if (array_key_exists('status', $filterdata)) {
+            $statusfilter = new integer_filter('status');
+            $filterset->add_filter($statusfilter);
+
+            foreach ($filterdata['status']['values'] as $status) {
+                $statusfilter->add_filter_value($status);
+            }
+            $statusfilter->set_join_type($filterdata['status']['jointype']);
+        }
+
+        // Apply groups filter if required.
+        if (array_key_exists('groups', $filterdata)) {
+            $groupsfilter = new integer_filter('groups');
+            $filterset->add_filter($groupsfilter);
+
+            foreach ($filterdata['groups']['values'] as $filtergroupname) {
+                $groupsfilter->add_filter_value((int) $groupsdata[$filtergroupname]->id);
+            }
+            $groupsfilter->set_join_type($filterdata['groups']['jointype']);
+        }
+
+        // Apply last access filter if required.
+        if (array_key_exists('accesssince', $filterdata)) {
+            $lastaccessfilter = new integer_filter('accesssince');
+            $filterset->add_filter($lastaccessfilter);
+
+            foreach ($filterdata['accesssince']['values'] as $accessstring) {
+                $lastaccessfilter->add_filter_value(strtotime($accessstring));
+            }
+            $lastaccessfilter->set_join_type($filterdata['accesssince']['jointype']);
+        }
+
+        // Run the search.
+        $search = new participants_search($course, $coursecontext, $filterset);
+        $rs = $search->get_participants();
+        $this->assertInstanceOf(moodle_recordset::class, $rs);
+        $records = $this->convert_recordset_to_array($rs);
+
+        $this->assertCount($count, $records);
+        $this->assertEquals($count, $search->get_total_participants_count());
+
+        foreach ($expectedusers as $expecteduser) {
+            $this->assertArrayHasKey($users[$expecteduser]->id, $records);
+        }
+    }
+
+    /**
+     * Data provider for filterset join tests.
+     *
+     * @return array
+     */
+    public function filterset_joins_provider(): array {
+        $tests = [
+            // Users with different configurations.
+            'Users with different configurations' => (object) [
+                'groupsavailable' => [
+                    'groupa',
+                    'groupb',
+                    'groupc',
+                ],
+                'users' => [
+                    'adam.ant' => [
+                        'firstname' => 'Adam',
+                        'lastname' => 'Ant',
+                        'enrolments' => [
+                            [
+                                'role' => 'student',
+                                'method' => 'manual',
+                                'status' => ENROL_USER_ACTIVE,
+                            ],
+                        ],
+                        'groups' => ['groupa'],
+                        'lastlogin' => '-3 days',
+                    ],
+                    'barbara.bennett' => [
+                        'firstname' => 'Barbara',
+                        'lastname' => 'Bennett',
+                        'enrolments' => [
+                            [
+                                'role' => 'student',
+                                'method' => 'manual',
+                                'status' => ENROL_USER_ACTIVE,
+                            ],
+                            [
+                                'role' => 'teacher',
+                                'method' => 'manual',
+                                'status' => ENROL_USER_ACTIVE,
+                            ],
+                        ],
+                        'groups' => ['groupb'],
+                        'lastlogin' => '-2 weeks',
+                    ],
+                    'colin.carnforth' => [
+                        'firstname' => 'Colin',
+                        'lastname' => 'Carnforth',
+                        'enrolments' => [
+                            [
+                                'role' => 'editingteacher',
+                                'method' => 'self',
+                                'status' => ENROL_USER_SUSPENDED,
+                            ],
+                        ],
+                        'groups' => ['groupa', 'groupb'],
+                        'lastlogin' => '-5 months',
+                    ],
+                    'tony.rogers' => [
+                        'firstname' => 'Anthony',
+                        'lastname' => 'Rogers',
+                        'enrolments' => [
+                            [
+                                'role' => 'editingteacher',
+                                'method' => 'self',
+                                'status' => ENROL_USER_SUSPENDED,
+                            ],
+                        ],
+                        'groups' => [],
+                        'lastlogin' => '-10 months',
+                    ],
+                    'sarah.rester' => [
+                        'firstname' => 'Sarah',
+                        'lastname' => 'Rester',
+                        'email' => 'zazu@example.com',
+                        'enrolments' => [
+                            [
+                                'role' => 'teacher',
+                                'method' => 'manual',
+                                'status' => ENROL_USER_ACTIVE,
+                            ],
+                            [
+                                'role' => 'editingteacher',
+                                'method' => 'self',
+                                'status' => ENROL_USER_SUSPENDED,
+                            ],
+                        ],
+                        'groups' => [],
+                        'lastlogin' => '-11 months',
+                    ],
+                    'morgan.crikeyson' => [
+                        'firstname' => 'Morgan',
+                        'lastname' => 'Crikeyson',
+                        'enrolments' => [
+                            [
+                                'role' => 'teacher',
+                                'method' => 'manual',
+                                'status' => ENROL_USER_ACTIVE,
+                            ],
+                        ],
+                        'groups' => ['groupa'],
+                        'lastlogin' => '-1 week',
+                    ],
+                    'jonathan.bravo' => [
+                        'firstname' => 'Jonathan',
+                        'lastname' => 'Bravo',
+                        'enrolments' => [
+                            [
+                                'role' => 'student',
+                                'method' => 'manual',
+                                'status' => ENROL_USER_ACTIVE,
+                            ],
+                        ],
+                        'groups' => [],
+                        // Never logged in.
+                        'lastlogin' => '',
+                    ],
+                ],
+                'expect' => [
+                    // Tests for jointype: ANY.
+                    'ANY: No filters in filterset' => (object) [
+                        'filterdata' => [],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 7,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                            'morgan.crikeyson',
+                            'jonathan.bravo',
+                        ],
+                    ],
+                    'ANY: Filterset containing a single filter type' => (object) [
+                        'filterdata' => [
+                            'enrolmethods' => [
+                                'values' => ['self'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                        ],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'ANY: Filterset matching all filter types on different users' => (object) [
+                        'filterdata' => [
+                            // Match Adam only.
+                            'keywords' => [
+                                'values' => ['adam'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Match Sarah only.
+                            'enrolmethods' => [
+                                'values' => ['manual', 'self'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Match Barbara only.
+                            'courseroles' => [
+                                'values' => ['student', 'teacher'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Match Sarah only.
+                            'statuses' => [
+                                'values' => ['active', 'suspended'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Match Colin only.
+                            'groups' => [
+                                'values' => ['groupa', 'groupb'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Match Jonathan only.
+                            'accesssince' => [
+                                'values' => ['-1 year'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                                ],
+                        ],
+                        'jointype' => filter::JOINTYPE_ANY,
+                        'count' => 5,
+                        // Morgan and Tony are not matched, to confirm filtering is not just returning all users.
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'sarah.rester',
+                            'jonathan.bravo',
+                        ],
+                    ],
+
+                    // Tests for jointype: ALL.
+                    'ALL: No filters in filterset' => (object) [
+                        'filterdata' => [],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 7,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                            'morgan.crikeyson',
+                            'jonathan.bravo',
+                        ],
+                    ],
+                    'ALL: Filterset containing a single filter type' => (object) [
+                        'filterdata' => [
+                            'enrolmethods' => [
+                                'values' => ['self'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                        ],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 3,
+                        'expectedusers' => [
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                        ],
+                    ],
+                    'ALL: Filterset combining all filter types' => (object) [
+                        'filterdata' => [
+                            // Exclude Adam, Tony, Morgan and Jonathan.
+                            'keywords' => [
+                                'values' => ['ar'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                            // Exclude Colin and Tony.
+                            'enrolmethods' => [
+                                'values' => ['manual'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                            // Exclude Adam, Barbara and Jonathan.
+                            'courseroles' => [
+                                'values' => ['student'],
+                                'jointype' => filter::JOINTYPE_NONE,
+                            ],
+                            // Exclude Colin and Tony.
+                            'statuses' => [
+                                'values' => ['active'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Exclude Barbara.
+                            'groups' => [
+                                'values' => ['groupa', 'nogroups'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                            // Exclude Adam, Colin and Barbara.
+                            'accesssince' => [
+                                'values' => ['-6 months'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                                ],
+                        ],
+                        'jointype' => filter::JOINTYPE_ALL,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'sarah.rester',
+                        ],
+                    ],
+
+                    // Tests for jointype: NONE.
+                    'NONE: No filters in filterset' => (object) [
+                        'filterdata' => [],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 7,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'colin.carnforth',
+                            'tony.rogers',
+                            'sarah.rester',
+                            'morgan.crikeyson',
+                            'jonathan.bravo',
+                        ],
+                    ],
+                    'NONE: Filterset containing a single filter type' => (object) [
+                        'filterdata' => [
+                            'enrolmethods' => [
+                                'values' => ['self'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                        ],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 4,
+                        'expectedusers' => [
+                            'adam.ant',
+                            'barbara.bennett',
+                            'morgan.crikeyson',
+                            'jonathan.bravo',
+                        ],
+                    ],
+                    'NONE: Filterset combining all filter types' => (object) [
+                        'filterdata' => [
+                            // Excludes Adam.
+                            'keywords' => [
+                                'values' => ['adam'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                            // Excludes Colin, Tony and Sarah.
+                            'enrolmethods' => [
+                                'values' => ['self'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                            // Excludes Jonathan.
+                            'courseroles' => [
+                                'values' => ['student'],
+                                'jointype' => filter::JOINTYPE_NONE,
+                            ],
+                            // Excludes Colin, Tony and Sarah.
+                            'statuses' => [
+                                'values' => ['suspended'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                            ],
+                            // Excludes Adam, Colin, Tony, Sarah, Morgan and Jonathan.
+                            'groups' => [
+                                'values' => ['groupa', 'nogroups'],
+                                'jointype' => filter::JOINTYPE_ANY,
+                            ],
+                            // Excludes Tony and Sarah.
+                            'accesssince' => [
+                                'values' => ['-6 months'],
+                                'jointype' => filter::JOINTYPE_ALL,
+                                ],
+                        ],
+                        'jointype' => filter::JOINTYPE_NONE,
+                        'count' => 1,
+                        'expectedusers' => [
+                            'barbara.bennett',
+                        ],
+                    ],
+                ],
+            ],
+        ];
+
+        $finaltests = [];
+        foreach ($tests as $testname => $testdata) {
+            foreach ($testdata->expect as $expectname => $expectdata) {
+                $finaltests["{$testname} => {$expectname}"] = [
+                    'users' => $testdata->users,
+                    'filterdata' => $expectdata->filterdata,
+                    'groupsavailable' => $testdata->groupsavailable,
+                    'jointype' => $expectdata->jointype,
+                    'count' => $expectdata->count,
+                    'expectedusers' => $expectdata->expectedusers,
+                ];
+            }
+        }
+
+        return $finaltests;
+    }
 }
index 28c37fd..684e22f 100644 (file)
@@ -889,7 +889,7 @@ class core_userliblib_testcase extends advanced_testcase {
         groups_add_member($group->id, $student1->id);
         groups_add_member($group->id, $student2->id);
 
-        // Set it so the teacher and two of the students have accessed the courses within the last day,
+        // Set it so the teacher and two of the students have not accessed the courses within the last day,
         // but only one of the students is in the group.
         $accesssince = time() - DAYSECS;
         $lastaccess = new stdClass();
@@ -904,12 +904,12 @@ class core_userliblib_testcase extends advanced_testcase {
         $lastaccess->userid = $student3->id;
         $DB->insert_record('user_lastaccess', $lastaccess);
 
-        // Now, when we perform the following search we should only return 1 user. A student who belongs to
-        // the group and has the name 'searchforthis' and has also accessed the course in the last day.
+        // Now, when we perform the following search we should only return 2 users. Student who belong to
+        // the group and have the name 'searchforthis' and have not accessed the course in the last day.
         $count = user_get_total_participants($course->id, $group->id, $accesssince + 1, $roleids['student'], 0, -1,
             'searchforthis');
 
-        $this->assertEquals(1, $count);
+        $this->assertEquals(2, $count);
     }
 
     /**
@@ -918,14 +918,14 @@ class core_userliblib_testcase extends advanced_testcase {
     public function test_user_get_total_participants_on_front_page() {
         $this->resetAfterTest();
 
-        // Set it so that only 3 users have accessed the site within the last day.
+        // Set it so that only 3 users have not accessed the site within the last day (including one which has never accessed it).
         $accesssince = time() - DAYSECS;
 
         // Create a bunch of users.
         $user1 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => $accesssince]);
         $user2 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => $accesssince]);
-        $user3 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis']);
-        $user4 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => $accesssince]);
+        $user3 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => time()]);
+        $user4 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis']);
 
         // Create a group.
         $group = self::getDataGenerator()->create_group(array('courseid' => SITEID));
@@ -936,7 +936,7 @@ class core_userliblib_testcase extends advanced_testcase {
         groups_add_member($group->id, $user3->id);
 
         // Now, when we perform the following search we should only return 2 users. Users who belong to
-        // the group and have the name 'searchforthis' and have also accessed the site in the last day.
+        // the group and have the name 'searchforthis' and have not accessed the site in the last day.
         $count = user_get_total_participants(SITEID, $group->id, $accesssince + 1, 0, 0, -1, 'searchforthis');
 
         $this->assertEquals(2, $count);
@@ -978,8 +978,8 @@ class core_userliblib_testcase extends advanced_testcase {
         groups_add_member($group->id, $student1->id);
         groups_add_member($group->id, $student2->id);
 
-        // Set it so the teacher and two of the students have accessed the course within the last day, but only one of
-        // the students is in the group.
+        // Set it so the teacher and two of the students have not accessed the course within the last day, but only one of
+        // the students is in the group (student 3 has never accessed the course).
         $accesssince = time() - DAYSECS;
         $lastaccess = new stdClass();
         $lastaccess->userid = $teacher->id;
@@ -990,11 +990,12 @@ class core_userliblib_testcase extends advanced_testcase {
         $lastaccess->userid = $student1->id;
         $DB->insert_record('user_lastaccess', $lastaccess);
 
-        $lastaccess->userid = $student3->id;
+        $lastaccess->userid = $student2->id;
+        $lastaccess->timeaccess = time();
         $DB->insert_record('user_lastaccess', $lastaccess);
 
         // Now, when we perform the following search we should only return 1 user. A student who belongs to
-        // the group and has the name 'searchforthis' and has also accessed the course in the last day.
+        // the group and has the name 'searchforthis' and has not accessed the course in the last day.
         $userset = user_get_participants($course->id, $group->id, $accesssince + 1, $roleids['student'], 0, -1, 'searchforthis');
 
         $this->assertEquals($student1->id, $userset->current()->id);
@@ -1013,14 +1014,14 @@ class core_userliblib_testcase extends advanced_testcase {
     public function test_user_get_participants_on_front_page() {
         $this->resetAfterTest();
 
-        // Set it so that only 3 users have accessed the site within the last day.
+        // Set it so that only 3 users have not accessed the site within the last day (user 4 has never accessed the site).
         $accesssince = time() - DAYSECS;
 
         // Create a bunch of users.
         $user1 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => $accesssince]);
         $user2 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => $accesssince]);
-        $user3 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis']);
-        $user4 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => $accesssince]);
+        $user3 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis', 'lastaccess' => time()]);
+        $user4 = self::getDataGenerator()->create_user(['firstname' => 'searchforthis']);
 
         // Create a group.
         $group = self::getDataGenerator()->create_group(array('courseid' => SITEID));
@@ -1031,7 +1032,7 @@ class core_userliblib_testcase extends advanced_testcase {
         groups_add_member($group->id, $user3->id);
 
         // Now, when we perform the following search we should only return 2 users. Users who belong to
-        // the group and have the name 'searchforthis' and have also accessed the site in the last day.
+        // the group and have the name 'searchforthis' and have not accessed the site in the last day.
         $userset = user_get_participants(SITEID, $group->id, $accesssince + 1, 0, 0, -1, 'searchforthis', '', array(),
             'ORDER BY id ASC');