/**
* 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);
}
