/** * This will add a groupid to each of the submissions coming out of the moduleunion. This means getting * all the group memberships for this course and choosing the maximum groupid. We can't count more than * once when students are in two groups, so we need to do it like this. It's a greatest-n-per-group * problem, solved with the classic left-join approach. * * @param block_ajax_marking_query $query * @return void */ protected function alter_query(block_ajax_marking_query $query) { list($maxquery, $maxparams) = block_ajax_marking_group_max_subquery(); list($memberquery, $memberparams) = block_ajax_marking_group_members_subquery(); list($visibilitysubquery, $visibilityparams) = block_ajax_marking_group_visibility_subquery(); // We need to join to groups members to see if there are any memberships at all (in which case // we use the highest visible id if there is one), or 0 if there are no memberships at all. $table = array('join' => 'LEFT JOIN', 'table' => $memberquery, 'on' => 'membergroupquery.userid = moduleunion.userid AND membergroupquery.coursemoduleid = moduleunion.coursemoduleid', 'alias' => 'membergroupquery', 'subquery' => true); $query->add_from($table); $query->add_params($memberparams); // To make sure it's the highest visible one, we use this subquery as a greatest-n-per-group thing. $table = array('join' => 'LEFT JOIN', 'table' => $maxquery, 'on' => 'membergroupquery.userid = maxgroupquery.userid AND membergroupquery.coursemoduleid = maxgroupquery.coursemoduleid AND maxgroupquery.groupid > membergroupquery.groupid', 'alias' => 'maxgroupquery', 'subquery' => true); $query->add_from($table); $query->add_params($maxparams); // We join only if the group id is larger, then specify that it must be null. This means that // the membergroupquery group id will be the largest available. $query->add_where(array('type' => 'AND', 'condition' => 'maxgroupquery.groupid IS NULL')); // Make sure it's not hidden. We want to know if there are people with no group, compared to a group that // is hidden, so the aim is to get a null group id if there are no memberships by left joining, then // hide that null row if the settings for group id 0 say so. $table = array('join' => 'LEFT JOIN', 'table' => $visibilitysubquery, 'on' => '(membervisibilityquery.groupid = membergroupquery.groupid OR (membervisibilityquery.groupid = 0 AND membergroupquery.groupid IS NULL)) AND membervisibilityquery.coursemoduleid = membergroupquery.coursemoduleid', 'alias' => 'membervisibilityquery', 'subquery' => true); $query->add_from($table); $query->add_params($visibilityparams); $query->add_where(array('type' => 'AND', 'condition' => 'membervisibilityquery.coursemoduleid IS NULL')); }
/** * We have to do greatest-n-per-group to get the highest group id that's not specified as hidden by the user. * This involves repeating the same SQL twice because MySQL doesn't support CTEs. * * This function provides the base query which shows us the highest groupid per user per coursemodule. We need to also * take account of people who have no group memberships, so we need to distinguish between null results because the * group is hidden and null results because there is no group. */ function block_ajax_marking_group_max_subquery() { global $DB; // Params need new names every time. static $counter = 1; $counter++; list($visibilitysubquery, $visibilityparams) = block_ajax_marking_group_visibility_subquery(); $courses = block_ajax_marking_get_my_teacher_courses(); list($coursessql, $coursesparams) = $DB->get_in_or_equal(array_keys($courses), SQL_PARAMS_NAMED, "gmax_{$counter}_courses"); // This only shows people who have group memberships, so we need to say if there isn't one or not in the outer // query. For this reason, this query will return all group memberships, plus whether they ought to be displayed. // The outer query can then do a left join. $sql = <<<SQL /* Start max groupid query */ SELECT gmax_members{$counter}.userid, gmax_groups{$counter}.id AS groupid, gmax_course_modules{$counter}.id AS coursemoduleid, CASE WHEN visquery{$counter}.groupid IS NULL THEN 1 ELSE 0 END AS display FROM {groups_members} gmax_members{$counter} INNER JOIN {groups} gmax_groups{$counter} ON gmax_groups{$counter}.id = gmax_members{$counter}.groupid INNER JOIN {course_modules} gmax_course_modules{$counter} ON gmax_course_modules{$counter}.course = gmax_groups{$counter}.courseid LEFT JOIN ({$visibilitysubquery}) visquery{$counter} ON visquery{$counter}.groupid = gmax_groups{$counter}.id AND visquery{$counter}.coursemoduleid = gmax_course_modules{$counter}.id /* Limit the size of the subquery for performance */ WHERE gmax_groups{$counter}.courseid {$coursessql} AND visquery{$counter}.groupid IS NULL /* End max groupid query */ SQL; return array($sql, array_merge($visibilityparams, $coursesparams)); }
/** * Makes sure that the query we use to hide groups that the user has set to be hidden in the per user block settings * works. */ public function test_group_visibility_subquery() { global $DB, $USER; $this->set_up_for_group_subqueries(); // Check we got everything. $this->assertEquals(2, $DB->count_records('course_modules'), 'Wrong number of course modules'); $this->assertEquals(2, $DB->count_records('groups'), 'Wrong number of groups'); $this->assertEquals(1, $DB->count_records('groups_members'), 'Wrong number of group members'); list($query, $params) = block_ajax_marking_group_visibility_subquery(); // We should get everything turning up as OK to display. // Don't use normal $DB functions as we will get duplicate array keys and it'll overwrite the rows. $vanillalist = $this->get_visibility_array_results($query, $params); // No idea what order the stuff's going to come in, so we need to list whether we expect things to be there // or not. This uses the groupid and coursemodule id concatenated. $expectedarray = array($this->group1->id . '-' . $this->assign1->id => false, $this->group1->id . '-' . $this->forum1->id => false, $this->group1->id . '-' . $this->forum1->id => false, $this->group2->id . '-' . $this->forum1->id => false); $this->check_visibility_results($expectedarray, $vanillalist); // Now see if altering the settings work. Hide group1 for one course module (forum1). $cmsetting = new stdClass(); $cmsetting->userid = $USER->id; $cmsetting->tablename = 'course_modules'; $cmsetting->instanceid = $this->forum1->cmid; $cmsetting->display = 1; $cmsetting->id = $DB->insert_record('block_ajax_marking', $cmsetting); $groupsetting = new stdClass(); $groupsetting->configid = $cmsetting->id; $groupsetting->groupid = $this->group1->id; $groupsetting->display = 0; $groupsetting->id = $DB->insert_record('block_ajax_marking_groups', $groupsetting); $expectedarray[$this->group1->id . '-' . $this->forum1->id] = true; $vanillalist = $this->get_visibility_array_results($query, $params); $this->check_visibility_results($expectedarray, $vanillalist); // Now unhide, then hide it at course level instead, so we should see it hidden for both course modules. $DB->delete_records('block_ajax_marking_groups', array('id' => $groupsetting->id)); $setting = new stdClass(); $setting->userid = $USER->id; $setting->tablename = 'course'; $setting->instanceid = $this->course->id; $setting->display = 1; $setting->id = $DB->insert_record('block_ajax_marking', $setting); $groupsetting = new stdClass(); $groupsetting->configid = $setting->id; $groupsetting->groupid = $this->group1->id; $groupsetting->display = 0; $DB->insert_record('block_ajax_marking_groups', $groupsetting); $expectedarray[$this->group1->id . '-' . $this->forum1->id] = true; $expectedarray[$this->group1->id . '-' . $this->assign1->id] = true; $vanillalist = $this->get_visibility_array_results($query, $params); $this->check_visibility_results($expectedarray, $vanillalist); // Now reveal it for forum 1 and make sure we don't have it any more, i.e. forum 1 should override for // this group, but assign 1 should keep the course setting. $groupsetting = new stdClass(); $groupsetting->configid = $cmsetting->id; $groupsetting->groupid = $this->group1->id; $groupsetting->display = 1; $groupsetting->id = $DB->insert_record('block_ajax_marking_groups', $groupsetting); $expectedarray[$this->group1->id . '-' . $this->forum1->id] = false; $vanillalist = $this->get_visibility_array_results($query, $params); $this->check_visibility_results($expectedarray, $vanillalist); }