MDL-45087 report_log: do not show anonymous actions, use indexes

diff --git a/report/log/classes/table_log.php b/report/log/classes/table_log.php
index cdf25c5..a41c0da 100644 (file)
--- a/report/log/classes/table_log.php
+++ b/report/log/classes/table_log.php
@@ -328,9 +328,14 @@ class report_log_table_log extends table_sql {
                 $sql = $DB->sql_like('action', ':action', false);
                 $params['action'] = '%'.$action.'%';
             }
-        } else {
+        } else if (!empty($this->filterparams->action)) {
             $sql = "crud = :crud";
             $params['crud'] = $this->filterparams->action;
+        } else {
+            // Add condition for all possible values of crud (to use db index).
+            list($sql, $params) = $DB->get_in_or_equal(array('c', 'r', 'u', 'd'),
+                    SQL_PARAMS_NAMED, 'crud');
+            $sql = "crud ".$sql;
         }
         return array($sql, $params);
     }
@@ -342,10 +347,15 @@ class report_log_table_log extends table_sql {
      * @param bool $useinitialsbar do you want to use the initials bar.
      */
     public function query_db($pagesize, $useinitialsbar = true) {
+        global $DB;
 
         $joins = array();
         $params = array();
 
+        // If we filter by userid and module id we also need to filter by crud and edulevel to ensure DB index is engaged.
+        $useextendeddbindex = !($this->filterparams->logreader instanceof logstore_legacy\log\store)
+                && !empty($this->filterparams->userid) && !empty($this->filterparams->modid);
+
         $groupid = 0;
         if (!empty($this->filterparams->courseid)) {
             if (!empty($this->filterparams->groupid)) {
@@ -362,10 +372,12 @@ class report_log_table_log extends table_sql {
 
         if (!empty($this->filterparams->modid)) {
             $joins[] = "contextinstanceid = :contextinstanceid";
+            $joins[] = "contextlevel = :contextmodule";
             $params['contextinstanceid'] = $this->filterparams->modid;
+            $params['contextmodule'] = CONTEXT_MODULE;
         }
 
-        if (!empty($this->filterparams->action)) {
+        if (!empty($this->filterparams->action) || $useextendeddbindex) {
             list($actionsql, $actionparams) = $this->get_action_sql();
             $joins[] = $actionsql;
             $params = array_merge($params, $actionparams);
@@ -392,6 +404,16 @@ class report_log_table_log extends table_sql {
         if (isset($this->filterparams->edulevel) && ($this->filterparams->edulevel >= 0)) {
             $joins[] = "edulevel = :edulevel";
             $params['edulevel'] = $this->filterparams->edulevel;
+        } else if ($useextendeddbindex) {
+            list($edulevelsql, $edulevelparams) = $DB->get_in_or_equal(array(\core\event\base::LEVEL_OTHER,
+                \core\event\base::LEVEL_PARTICIPATING, \core\event\base::LEVEL_TEACHING), SQL_PARAMS_NAMED, 'edulevel');
+            $joins[] = "edulevel ".$edulevelsql;
+            $params = array_merge($params, $edulevelparams);
+        }
+
+        if (!($this->filterparams->logreader instanceof logstore_legacy\log\store)) {
+            // Filter out anonymous actions, this is N/A for legacy log because it never stores them.
+            $joins[] = "anonymous = 0";
         }
 
         $selector = implode(' AND ', $joins);

diff --git a/report/log/locallib.php b/report/log/locallib.php
index 8ddb193..8d2d37b 100644 (file)
--- a/report/log/locallib.php
+++ b/report/log/locallib.php
@@ -92,10 +92,13 @@ function report_log_usercourse($userid, $courseid, $coursestart, $logreader = ''
         $logtable = 'log';
         $timefield = 'time';
         $coursefield = 'course';
+        // Anonymous actions are never logged in legacy log.
+        $nonanonymous = '';
     } else {
         $logtable = $reader->get_internal_log_table_name();
         $timefield = 'timecreated';
         $coursefield = 'courseid';
+        $nonanonymous = 'AND anonymous = 0';
     }
 
     $params = array();
@@ -108,7 +111,7 @@ function report_log_usercourse($userid, $courseid, $coursestart, $logreader = ''
     return $DB->get_records_sql("SELECT FLOOR(($timefield - $coursestart)/" . DAYSECS . ") AS day, COUNT(*) AS num
                                    FROM {" . $logtable . "}
                                   WHERE userid = :userid
-                                        AND $timefield > $coursestart $courseselect
+                                        AND $timefield > $coursestart $courseselect $nonanonymous
                                GROUP BY FLOOR(($timefield - $coursestart)/" . DAYSECS .")", $params);
 }
 
@@ -142,10 +145,13 @@ function report_log_userday($userid, $courseid, $daystart, $logreader = '') {
         $logtable = 'log';
         $timefield = 'time';
         $coursefield = 'course';
+        // Anonymous actions are never logged in legacy log.
+        $nonanonymous = '';
     } else {
         $logtable = $reader->get_internal_log_table_name();
         $timefield = 'timecreated';
         $coursefield = 'courseid';
+        $nonanonymous = 'AND anonymous = 0';
     }
     $params = array('userid' => $userid);
 
@@ -157,7 +163,7 @@ function report_log_userday($userid, $courseid, $daystart, $logreader = '') {
     return $DB->get_records_sql("SELECT FLOOR(($timefield - $daystart)/" . HOURSECS . ") AS hour, COUNT(*) AS num
                                    FROM {" . $logtable . "}
                                   WHERE userid = :userid
-                                        AND $timefield > $daystart $courseselect
+                                        AND $timefield > $daystart $courseselect $nonanonymous
                                GROUP BY FLOOR(($timefield - $daystart)/" . HOURSECS . ") ", $params);
 }