From 334edb71c12146cdda44d753fa2059b2e23a306f Mon Sep 17 00:00:00 2001
From: jamiesensei <jamiesensei>
Date: Mon, 30 Jun 2008 11:53:47 +0000
Subject: [PATCH] MDL-15198 "non-standard sql in reportlib.php" fixed this with
 new sql that does not require a limit clause.

---
 mod/quiz/report/overview/report.php |  6 ++-
 mod/quiz/report/reportlib.php       | 66 ++++++++++++++++-------------
 2 files changed, 41 insertions(+), 31 deletions(-)

diff --git a/mod/quiz/report/overview/report.php b/mod/quiz/report/overview/report.php
index 95b44fb13d..fad12b6e3d 100644
--- a/mod/quiz/report/overview/report.php
+++ b/mod/quiz/report/overview/report.php
@@ -53,7 +53,9 @@ class quiz_overview_report extends quiz_default_report {
         $pageoptions['mode'] = 'overview';
 
         $reporturl = new moodle_url($CFG->wwwroot.'/mod/quiz/report.php', $pageoptions);
-        $qmsubselect = quiz_report_qm_filter_subselect($quiz); // careful: these are named params in $params!!
+        $qmsubselect = quiz_report_qm_filter_select($quiz);
+
+        
 
         /// find out current groups mode
         $currentgroup = groups_get_activity_group($cm, true);
@@ -183,7 +185,7 @@ class quiz_overview_report extends quiz_default_report {
     
             // Construct the SQL
             $fields = $DB->sql_concat('u.id', '\'#\'', 'COALESCE(qa.attempt, \'0\')').' AS uniqueid, '.
-                ($qmsubselect?$qmsubselect.' AS gradedattempt, ':'').
+                ($qmsubselect?"($qmsubselect) AS gradedattempt, ":'').
                 'qa.uniqueid AS attemptuniqueid, qa.id AS attempt, u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, '.
                 'qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
     
diff --git a/mod/quiz/report/reportlib.php b/mod/quiz/report/reportlib.php
index 170244ceac..ebc188f5f6 100644
--- a/mod/quiz/report/reportlib.php
+++ b/mod/quiz/report/reportlib.php
@@ -40,20 +40,18 @@ function quiz_get_newgraded_states($attemptids, $idxattemptq = true, $fields='qs
 
 function quiz_get_average_grade_for_questions($quiz, $userids){
     global $CFG, $DB;
-    $qmfilter = quiz_report_qm_filter_subselect($quiz, 'qa.userid');
-    $params['quizid'] = $quiz->id;
-    list($usql, $u_params) = $DB->get_in_or_equal($userids, SQL_PARAMS_NAMED, 'u0000');
-    $params += $u_params;
-    $questionavgssql = "SELECT qs.question, AVG(qs.grade) FROM
-                        {question_sessions} qns,
-                        {quiz_attempts} qa,
-                        {question_states} qs
-                        WHERE qns.attemptid = qa.uniqueid AND " .
-                        ($qmfilter?$qmfilter.' AND ':'') . "
-                        qa.quiz = :quizid AND
-                        qa.userid $usql AND
-                        qs.event IN (".QUESTION_EVENTS_GRADED.") AND
-                        qns.newgraded = qs.id GROUP BY qs.question";
+    $qmfilter = quiz_report_qm_filter_select($quiz);
+    list($usql, $params) = $DB->get_in_or_equal($userids);
+    $params[] = $quiz->id;
+    $questionavgssql = "SELECT qns.questionid, AVG(qs.grade) FROM
+                        {quiz_attempts} qa 
+                        LEFT JOIN {question_sessions} qns ON (qns.attemptid = qa.uniqueid)
+                        LEFT JOIN {question_states} qs ON (qns.newgraded = qs.id AND qs.event IN (".QUESTION_EVENTS_GRADED."))
+                        WHERE " .
+                        "($qmfilter) AND " .
+                        "qa.userid $usql AND " .
+                        "qa.quiz = ? ".
+                        "GROUP BY qs.question";
     return $DB->get_records_sql_menu($questionavgssql, $params);
 }
 
@@ -143,37 +141,47 @@ function quiz_report_load_questions($quiz){
  * one attempt that will be graded for each user. Or return
  * empty string if all attempts contribute to final grade.
  */
-function quiz_report_qm_filter_subselect($quiz, $useridsql = 'u.id', $quizidsql = 'qa.quiz'){
-    global $CFG;
+function quiz_report_qm_filter_select($quiz){
     if ($quiz->attempts == 1) {//only one attempt allowed on this quiz
         return '';
     }
+    $useridsql = 'qa.userid';
+    $quizidsql = 'qa.quiz';
     $qmfilterattempts = true;
     switch ($quiz->grademethod) {
     case QUIZ_GRADEHIGHEST :
-        $qmorderby = 'sumgrades DESC, timestart ASC';
+        $field1 = 'sumgrades';
+        $field2 = 'timestart';
+        $aggregator1 = 'MAX';
+        $aggregator2 = 'MIN';
+        $qmselectpossible = true;
         break;
     case QUIZ_GRADEAVERAGE :
-        $qmfilterattempts = false;
+        $qmselectpossible = false;
         break;
     case QUIZ_ATTEMPTFIRST :
-        $qmorderby = 'timestart ASC';
+        $field1 = 'timestart';
+        $field2 = 'id';
+        $aggregator1 = 'MIN';
+        $aggregator2 = 'MIN';
+        $qmselectpossible = true;
         break;
     case QUIZ_ATTEMPTLAST :
-        $qmorderby = 'timestart DESC';
+        $field1 = 'timestart';
+        $field2 = 'id';
+        $aggregator1 = 'MAX';
+        $aggregator2 = 'MAX';
+        $qmselectpossible = true;
         break;
     }
-
-    //no new params in this query, it is assumed that quizid will be used somewhere else
-    //in the main query.
-    if ($qmfilterattempts){
-        $qmsubselect = "(SELECT id FROM {quiz_attempts} " .
-                "WHERE quiz = $quizidsql AND userid = $useridsql " .
-                "ORDER BY $qmorderby LIMIT 1)=qa.id";
+    if ($qmselectpossible){
+        $qmselect = "qa.$field1 = (SELECT $aggregator1(qa2.$field1) FROM {quiz_attempts} qa2 WHERE qa2.quiz = $quizidsql AND qa2.userid = $useridsql) AND " .
+                    "qa.$field2 = (SELECT $aggregator2(qa3.$field2) FROM {quiz_attempts} qa3 WHERE qa3.quiz = $quizidsql AND qa3.userid = $useridsql AND qa3.$field1 = qa.$field1)";
     } else {
-        $qmsubselect = '';
+        $qmselect = '';
     }
-    return $qmsubselect;
+
+    return $qmselect;
 }
 
 function quiz_report_grade_bands($bandwidth, $bands, $quizid, $userids=array()){
-- 
2.39.5