Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-51720

Sort order for grades is wrong on the last page under some conditions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.7.10, 2.8.9, 2.9.3, 3.0
    • Fix Version/s: 2.8.10, 2.9.4
    • Component/s: Assignment
    • Labels:
    • Testing Instructions:
      Hide

      Add a role overide to prohibit the non-editing teacher role in a course from having grade capability for assignments.

      As a non-editing teacher in that course view the assignment grading table.

      Add $DB->set_debug(true) somewhere in the constructor for the grading table so you can see the queries. Verify there is always an "order by" for the table, even when paginating (the query to watch is the big one starting with SELECT u.id,u.picture,u.firstname,u.lastname...).

      Show
      Add a role overide to prohibit the non-editing teacher role in a course from having grade capability for assignments. As a non-editing teacher in that course view the assignment grading table. Add $DB->set_debug(true) somewhere in the constructor for the grading table so you can see the queries. Verify there is always an "order by" for the table, even when paginating (the query to watch is the big one starting with SELECT u.id,u.picture,u.firstname,u.lastname...).
    • Affected Branches:
      MOODLE_27_STABLE, MOODLE_28_STABLE, MOODLE_29_STABLE, MOODLE_30_STABLE
    • Fixed Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-51720-master

      Description

      We are having a problem with the sort order for Assignment (assign) View/Grade submissions function when used by a local, created role that allows a teaching assistant to edit course pages but not assign grades.

      There is no problem when viewed as the editing Teacher role , but when viewed our T/A role, with 43 users, the first 40 display correctly but the last (5th) page does not

      We have analyzed the problem and have suggested a fix below. We are using Moodle 2.7.3 (Build: 20141110), however the code for this is identical in the current version, Moodle 2.9.2.

      =============================================
      mod/assign/gradingtable.php

      88 // Check permissions up front.
      89 $this->hasgrantextension = has_capability('mod/assign:grantextension',
      90 $this->assignment->get_context());
      91 $this->hasgrade = $this->assignment->can_grade();
      :
      :
      :
      320 if (!$this->is_downloading() && $this->hasgrade) {
      321 // We have to call this column userid so we can use userid as a default sortable column.
      322 $columns[] = 'userid';
      323 $headers[] = get_string('edit');
      =============================================

      According to line 320, when hasgrade == 0, the data is not sorted by userid. It seems that can_grade() contains the current value of mod/assign:grade

      This following code thus generates different SQL for editingteacher and our T/A role:

      =============================================
      150 $submissionmaxattempt = 'SELECT mxs.userid, MAX(mxs.attemptnumber) AS maxattempt
      151 FROM

      {assign_submission} mxs
      152 WHERE mxs.assignment = :assignmentid4 GROUP BY mxs.userid';
      153 $grademaxattempt = 'SELECT mxg.userid, MAX(mxg.attemptnumber) AS maxattempt
      154 FROM {assign_grades} mxg
      155 WHERE mxg.assignment = :assignmentid5 GROUP BY mxg.userid';
      156 $from = '{user} u
      157 LEFT JOIN ( ' . $submissionmaxattempt . ' ) smx ON u.id = smx.userid
      158 LEFT JOIN ( ' . $grademaxattempt . ' ) gmx ON u.id = gmx.userid
      159 LEFT JOIN {assign_submission}

      s ON
      160 u.id = s.userid AND
      161 s.assignment = :assignmentid1 AND
      162 s.attemptnumber = smx.maxattempt
      163 LEFT JOIN

      {assign_grades}

      g ON
      164 u.id = g.userid AND
      165 g.assignment = :assignmentid2 AND
      166 g.attemptnumber = gmx.maxattempt
      167 LEFT JOIN

      {assign_user_flags}

      uf ON u.id = uf.userid AND uf.assignment = :assignmentid3';
      168
      169 $userparams = array();
      170 $userindex = 0;
      171
      172 list($userwhere, $userparams) = $DB->get_in_or_equal($users, SQL_PARAMS_NAMED, 'user');
      173 $where = 'u.id ' . $userwhere;
      174 $params = array_merge($params, $userparams);
      =============================================

      The difference comes in the secton after then "Where" clause causing differences in ORDER BY, LIMIT and OFFSET parameters.

      ***EditingTeacher
      SELECT
      ~~~~
      FROM mdl_user u
      LEFT JOIN ( SELECT mxs.userid, MAX(mxs.attemptnumber) AS maxattempt
      FROM mdl_assign_submission mxs
      WHERE mxs.assignment = $1 GROUP BY mxs.userid ) smx ON u.id = smx.userid
      LEFT JOIN ( SELECT mxg.userid, MAX(mxg.attemptnumber) AS maxattempt
      FROM mdl_assign_grades mxg
      WHERE mxg.assignment = $2 GROUP BY mxg.userid ) gmx ON u.id = gmx.userid
      LEFT JOIN mdl_assign_submission s ON
      u.id = s.userid AND
      s.assignment = $3 AND
      s.attemptnumber = smx.maxattempt
      LEFT JOIN mdl_assign_grades g ON
      u.id = g.userid AND
      g.assignment = $4 AND
      g.attemptnumber = gmx.maxattempt
      LEFT JOIN mdl_assign_user_flags uf ON u.id = uf.userid AND uf.assignment = $5
      WHERE u.id IN (~~~~~
      ORDER BY userid ASC LIMIT 10 OFFSET 50

      ***T/A Role ("mod/assign:grade"==0)
      SELECT
      ~~~~
      FROM mdl_user u
      LEFT JOIN ( SELECT mxs.userid, MAX(mxs.attemptnumber) AS maxattempt
      FROM mdl_assign_submission mxs
      WHERE mxs.assignment = $1 GROUP BY mxs.userid ) smx ON u.id = smx.userid
      LEFT JOIN ( SELECT mxg.userid, MAX(mxg.attemptnumber) AS maxattempt
      FROM mdl_assign_grades mxg
      WHERE mxg.assignment = $2 GROUP BY mxg.userid ) gmx ON u.id = gmx.userid
      LEFT JOIN mdl_assign_submission s ON
      u.id = s.userid AND
      s.assignment = $3 AND
      s.attemptnumber = smx.maxattempt
      LEFT JOIN mdl_assign_grades g ON
      u.id = g.userid AND
      g.assignment = $4 AND
      g.attemptnumber = gmx.maxattempt
      LEFT JOIN mdl_assign_user_flags uf ON u.id = uf.userid AND uf.assignment = $5
      WHERE u.id IN (~~~~~
      LIMIT 10 OFFSET 50
      ^^^^

      As can be seen, the "ORDER BY userid" parameter is missin.

      ***FIX
      It seems that the parameter "&& $this->hasgrade" is not needed for this function to work properly.
      =============================================
      mod/assign/gradingtable.php

      320 //if (!$this->is_downloading() && $this->hasgrade) {
      321 if (!$this->is_downloading()) {
      322 // We have to call this column userid so we can use userid as a default sortable column.
      323 $columns[] = 'userid';
      324 $headers[] = get_string('edit');
      =============================================

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/Jan/16