/** * 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')); }
/** * Needs to make sure that we get only the groups that are visible. * * Needs groups, coursemodules, a course and some settings. * * @todo Make this. */ public function test_group_max_subquery() { global $USER, $DB; // Make a coursemodule, group, etc. $this->set_up_for_group_subqueries(); // Get the query. list($query, $params) = block_ajax_marking_group_max_subquery(); // Ought to be all groupid-userid-coursemoduleid triples for all group memberships. // User1 is in group1 and there are two coursemodules. Should be 2 entries. $results = $this->get_visibility_array_results($query, $params); $this->assertEquals(2, count($results)); // TODO is it the right group? // Hide one coursemodule and we ought to get one. // Hide a group2a at coursemoduleid level. $cmsetting = new stdClass(); $cmsetting->userid = $USER->id; $cmsetting->tablename = 'course_modules'; $cmsetting->instanceid = $this->assign1->cmid; $cmsetting->display = 1; $cmsetting->id = $DB->insert_record('block_ajax_marking', $cmsetting); $cmgroupsetting = new stdClass(); $cmgroupsetting->configid = $cmsetting->id; $cmgroupsetting->groupid = $this->group1->id; $cmgroupsetting->display = 0; $cmgroupsetting->id = $DB->insert_record('block_ajax_marking_groups', $cmgroupsetting); $results = $this->get_visibility_array_results($query, $params); $this->assertEquals(1, count($results)); // Now hide at course level and we ought to get nothing. $coursesetting = new stdClass(); $coursesetting->userid = $USER->id; $coursesetting->tablename = 'course'; $coursesetting->instanceid = $this->course->id; $coursesetting->display = 1; $coursesetting->id = $DB->insert_record('block_ajax_marking', $coursesetting); $coursegroupsetting = new stdClass(); $coursegroupsetting->configid = $coursesetting->id; $coursegroupsetting->groupid = $this->group1->id; $coursegroupsetting->display = 0; $coursegroupsetting->id = $DB->insert_record('block_ajax_marking_groups', $coursegroupsetting); $results = $this->get_visibility_array_results($query, $params); $this->assertEquals(0, count($results)); // Now override at coursemodule level so we ought to have one only. $cmgroupsetting->display = 1; $cmgroupsetting->id = $DB->insert_record('block_ajax_marking_groups', $cmgroupsetting); $results = $this->get_visibility_array_results($query, $params); $this->assertEquals(0, count($results)); }