MDL-41944 block_onlines_users: Improve performance
authorBarry Oosthuizen <barry.oosthuizen@nottingham.ac.uk>
Fri, 14 Aug 2015 12:36:16 +0000 (13:36 +0100)
committerEloy Lafuente (stronk7) <stronk7@moodle.org>
Wed, 19 Aug 2015 16:30:35 +0000 (18:30 +0200)
blocks/online_users/block_online_users.php
blocks/online_users/classes/fetcher.php [new file with mode: 0644]

index c47d460..d524a23 100644 (file)
@@ -22,6 +22,8 @@
  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  */
 
+use block_online_users\fetcher;
+
 /**
  * This block needs to be reworked.
  * The new roles system does away with the concepts of rigid student and
@@ -56,7 +58,6 @@ class block_online_users extends block_base {
             $timetoshowusers = $CFG->block_online_users_timetosee * 60;
         }
         $now = time();
-        $timefrom = 100 * floor(($now - $timetoshowusers) / 100); // Round to nearest 100 seconds for better query cache
 
         //Calculate if we are in separate groups
         $isseparategroups = ($this->page->course->groupmode == SEPARATEGROUPS
@@ -66,82 +67,24 @@ class block_online_users extends block_base {
         //Get the user current group
         $currentgroup = $isseparategroups ? groups_get_course_group($this->page->course) : NULL;
 
-        $groupmembers = "";
-        $groupselect  = "";
-        $params = array();
-
-        //Add this to the SQL to show only group users
-        if ($currentgroup !== NULL) {
-            $groupmembers = ", {groups_members} gm";
-            $groupselect = "AND u.id = gm.userid AND gm.groupid = :currentgroup";
-            $params['currentgroup'] = $currentgroup;
-        }
+        $sitelevel = $this->page->course->id == SITEID || $this->page->context->contextlevel < CONTEXT_COURSE;
 
-        $userfields = user_picture::fields('u', array('username'));
-        $params['now'] = $now;
-        $params['timefrom'] = $timefrom;
-        if ($this->page->course->id == SITEID or $this->page->context->contextlevel < CONTEXT_COURSE) {  // Site-level
-            $sql = "SELECT $userfields, MAX(u.lastaccess) AS lastaccess
-                      FROM {user} u $groupmembers
-                     WHERE u.lastaccess > :timefrom
-                           AND u.lastaccess <= :now
-                           AND u.deleted = 0
-                           $groupselect
-                  GROUP BY $userfields
-                  ORDER BY lastaccess DESC ";
-
-           $csql = "SELECT COUNT(u.id)
-                      FROM {user} u $groupmembers
-                     WHERE u.lastaccess > :timefrom
-                           AND u.lastaccess <= :now
-                           AND u.deleted = 0
-                           $groupselect";
-
-        } else {
-            // Course level - show only enrolled users for now
-            // TODO: add a new capability for viewing of all users (guests+enrolled+viewing)
-
-            list($esqljoin, $eparams) = get_enrolled_sql($this->page->context);
-            $params = array_merge($params, $eparams);
-
-            $sql = "SELECT $userfields, MAX(ul.timeaccess) AS lastaccess
-                      FROM {user_lastaccess} ul $groupmembers, {user} u
-                      JOIN ($esqljoin) euj ON euj.id = u.id
-                     WHERE ul.timeaccess > :timefrom
-                           AND u.id = ul.userid
-                           AND ul.courseid = :courseid
-                           AND ul.timeaccess <= :now
-                           AND u.deleted = 0
-                           $groupselect
-                  GROUP BY $userfields
-                  ORDER BY lastaccess DESC";
-
-           $csql = "SELECT COUNT(u.id)
-                      FROM {user_lastaccess} ul $groupmembers, {user} u
-                      JOIN ($esqljoin) euj ON euj.id = u.id
-                     WHERE ul.timeaccess > :timefrom
-                           AND u.id = ul.userid
-                           AND ul.courseid = :courseid
-                           AND ul.timeaccess <= :now
-                           AND u.deleted = 0
-                           $groupselect";
-
-            $params['courseid'] = $this->page->course->id;
-        }
+        $onlineusers = new fetcher($currentgroup, $now, $timetoshowusers, $sitelevel,
+                $this->page->context, $this->page->course->id);
 
         //Calculate minutes
         $minutes  = floor($timetoshowusers/60);
 
         // Verify if we can see the list of users, if not just print number of users
         if (!has_capability('block/online_users:viewlist', $this->page->context)) {
-            if (!$usercount = $DB->count_records_sql($csql, $params)) {
+            if (!$usercount = $onlineusers->count_users()) {
                 $usercount = get_string("none");
             }
             $this->content->text = "<div class=\"info\">".get_string("periodnminutes","block_online_users",$minutes).": $usercount</div>";
             return $this->content;
         }
-
-        if ($users = $DB->get_records_sql($sql, $params, 0, 50)) {   // We'll just take the most recent 50 maximum
+        $userlimit = 50; // We'll just take the most recent 50 maximum.
+        if ($users = $onlineusers->get_users($userlimit)) {
             foreach ($users as $user) {
                 $users[$user->id]->fullname = fullname($user);
             }
@@ -149,10 +92,10 @@ class block_online_users extends block_base {
             $users = array();
         }
 
-        if (count($users) < 50) {
+        if (count($users) < $userlimit) {
             $usercount = "";
         } else {
-            $usercount = $DB->count_records_sql($csql, $params);
+            $usercount = $onlineusers->count_users();
             $usercount = ": $usercount";
         }
 
diff --git a/blocks/online_users/classes/fetcher.php b/blocks/online_users/classes/fetcher.php
new file mode 100644 (file)
index 0000000..1c6d15a
--- /dev/null
@@ -0,0 +1,165 @@
+<?php
+// This file is part of Moodle - http://moodle.org/
+//
+// Moodle is free software: you can redistribute it and/or modify
+// it under the terms of the GNU General Public License as published by
+// the Free Software Foundation, either version 3 of the License, or
+// (at your option) any later version.
+//
+// Moodle is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+//
+// You should have received a copy of the GNU General Public License
+// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
+
+/**
+ * File containing onlineusers class.
+ *
+ * @package    block_online_users
+ * @copyright  1999 onwards Martin Dougiamas (http://dougiamas.com)
+ * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
+ */
+
+namespace block_online_users;
+
+defined('MOODLE_INTERNAL') || die();
+
+/**
+ * Class used to list and count online users
+ *
+ * @package    block_online_users
+ * @copyright  1999 onwards Martin Dougiamas (http://dougiamas.com)
+ * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
+ */
+class fetcher {
+
+    /** @var string The SQL query for retrieving a list of online users */
+    public $sql;
+    /** @var string The SQL query for counting the number of online users */
+    public $csql;
+    /** @var string The params for the SQL queries */
+    public $params;
+
+    /**
+     * Class constructor
+     *
+     * @param int $currentgroup The group (if any) to filter on
+     * @param int $now Time now
+     * @param int $timetoshowusers Number of seconds to show online users
+     * @param context $context Context object used to generate the sql for users enrolled in a specific course
+     * @param bool $sitelevel Whether to check online users at site level.
+     * @param int $courseid The course id to check
+     */
+    public function __construct($currentgroup, $now, $timetoshowusers, $context, $sitelevel = true, $courseid = null) {
+        $this->set_sql($currentgroup, $now, $timetoshowusers, $context, $sitelevel, $courseid);
+    }
+
+    /**
+     * Store the SQL queries & params for listing online users
+     *
+     * @param int $currentgroup The group (if any) to filter on
+     * @param int $now Time now
+     * @param int $timetoshowusers Number of seconds to show online users
+     * @param context $context Context object used to generate the sql for users enrolled in a specific course
+     * @param bool $sitelevel Whether to check online users at site level.
+     * @param int $courseid The course id to check
+     */
+    protected function set_sql($currentgroup, $now, $timetoshowusers, $context, $sitelevel, $courseid) {
+        $timefrom = 100 * floor(($now - $timetoshowusers) / 100); // Round to nearest 100 seconds for better query cache.
+
+        $groupmembers = "";
+        $groupselect  = "";
+        $groupby       = "";
+        $lastaccess    = ", lastaccess";
+        $timeaccess    = ", ul.timeaccess AS lastaccess";
+        $params = array();
+
+        $userfields = \user_picture::fields('u', array('username'));
+
+        // Add this to the SQL to show only group users.
+        if ($currentgroup !== null) {
+            $groupmembers = ", {groups_members} gm";
+            $groupselect = "AND u.id = gm.userid AND gm.groupid = :currentgroup";
+            $groupby = "GROUP BY $userfields";
+            $lastaccess = ", MAX(u.lastaccess) AS lastaccess";
+            $timeaccess = ", MAX(ul.timeaccess) AS lastaccess";
+            $params['currentgroup'] = $currentgroup;
+        }
+
+        $params['now'] = $now;
+        $params['timefrom'] = $timefrom;
+        if ($sitelevel) {
+            $sql = "SELECT $userfields $lastaccess
+                      FROM {user} u $groupmembers
+                     WHERE u.lastaccess > :timefrom
+                           AND u.lastaccess <= :now
+                           AND u.deleted = 0
+                           $groupselect $groupby
+                  ORDER BY lastaccess DESC ";
+
+            $csql = "SELECT COUNT(u.id)
+                      FROM {user} u $groupmembers
+                     WHERE u.lastaccess > :timefrom
+                           AND u.lastaccess <= :now
+                           AND u.deleted = 0
+                           $groupselect";
+
+        } else {
+            // Course level - show only enrolled users for now.
+            // TODO: add a new capability for viewing of all users (guests+enrolled+viewing).
+            list($esqljoin, $eparams) = get_enrolled_sql($context);
+            $params = array_merge($params, $eparams);
+
+            $sql = "SELECT $userfields $timeaccess
+                      FROM {user_lastaccess} ul $groupmembers, {user} u
+                      JOIN ($esqljoin) euj ON euj.id = u.id
+                     WHERE ul.timeaccess > :timefrom
+                           AND u.id = ul.userid
+                           AND ul.courseid = :courseid
+                           AND ul.timeaccess <= :now
+                           AND u.deleted = 0
+                           $groupselect $groupby
+                  ORDER BY lastaccess DESC";
+
+            $csql = "SELECT COUNT(u.id)
+                      FROM {user_lastaccess} ul $groupmembers, {user} u
+                      JOIN ($esqljoin) euj ON euj.id = u.id
+                     WHERE ul.timeaccess > :timefrom
+                           AND u.id = ul.userid
+                           AND ul.courseid = :courseid
+                           AND ul.timeaccess <= :now
+                           AND u.deleted = 0
+                           $groupselect";
+
+            $params['courseid'] = $courseid;
+        }
+        $this->sql = $sql;
+        $this->csql = $csql;
+        $this->params = $params;
+    }
+
+    /**
+     * Get a list of the most recent online users
+     *
+     * @param int $userlimit The maximum number of users that will be returned (optional, unlimited if not set)
+     * @return array
+     */
+    public function get_users($userlimit = 0) {
+        global $DB;
+        $users = $DB->get_records_sql($this->sql, $this->params, 0, $userlimit);
+        return $users;
+    }
+
+    /**
+     * Count the number of online users
+     *
+     * @return int
+     */
+    public function count_users() {
+        global $DB;
+        return $DB->count_records_sql($this->csql, $this->params);
+    }
+
+}