MDL-26371 sql - avoid distinct + text in lesson essay & report

diff --git a/mod/lesson/essay.php b/mod/lesson/essay.php
index 6015d55..04d3fad 100644 (file)
--- a/mod/lesson/essay.php
+++ b/mod/lesson/essay.php
@@ -135,12 +135,17 @@ switch ($mode) {
         } else {
             $queryadd = '';
             $params = array ("lessonid" => $lesson->id);
-            if (!$users = $DB->get_records_sql("SELECT DISTINCT u.id, u.*
-                                     FROM {user} u,
-                                          {lesson_attempts} a
-                                     WHERE a.lessonid = :lessonid and
-                                           u.id = a.userid
-                                     ORDER BY u.lastname", $params)) {
+            // Need to use inner view to avoid distinct + text
+            if (!$users = $DB->get_records_sql("
+                SELECT u.*
+                  FROM {user} u
+                  JOIN (
+                    SELECT DISTINCT u.id
+                      FROM {user} u,
+                           {lesson_attempts} a
+                     WHERE a.lessonid = :lessonid and
+                           u.id = a.userid) ui ON (u.id = ui.id)
+                  ORDER BY u.lastname", $params)) {
                 print_error('cannotfinduser', 'lesson');
             }
         }

diff --git a/mod/lesson/report.php b/mod/lesson/report.php
index 8fdcb1f..1d670c9 100644 (file)
--- a/mod/lesson/report.php
+++ b/mod/lesson/report.php
@@ -41,10 +41,12 @@ require_login($course, false, $cm);
 $context = get_context_instance(CONTEXT_MODULE, $cm->id);
 require_capability('mod/lesson:manage', $context);
 
+$ufields = user_picture::fields('u'); // These fields are enough
 $params = array("lessonid" => $lesson->id);
+// TODO: Improve this. Fetching all students always is crazy!
 if (!empty($cm->groupingid)) {
     $params["groupid"] = $cm->groupingid;
-    $sql = "SELECT DISTINCT u.id, u.*
+    $sql = "SELECT DISTINCT $ufields
                 FROM {lesson_attempts} a
                     INNER JOIN {user} u ON u.id = a.userid
                     INNER JOIN {groups_members} gm ON gm.userid = u.id
@@ -52,7 +54,7 @@ if (!empty($cm->groupingid)) {
                 WHERE a.lessonid = :lessonid
                 ORDER BY u.lastname";
 } else {
-    $sql = "SELECT DISTINCT u.id, u.*
+    $sql = "SELECT DISTINCT $ufields
             FROM {user} u,
                  {lesson_attempts} a
             WHERE a.lessonid = :lessonid and