From 2dff3a06810757967bb519f5d36c4ea555add43a Mon Sep 17 00:00:00 2001
From: martinlanghoff <martinlanghoff>
Date: Wed, 19 Sep 2007 07:27:46 +0000
Subject: [PATCH] accesslib: get_assignable_roles() reworked to be
 constant-queries

get_assignable_roles() was calling user_can_assign() (cost of 1~2 DBq)
once-per-role. Instead, we can do a single DB query that answers
all our questions in one go.

On a Moodle w 8 roles defined, saves 19 DB queries for the course page
for teachers/admins.

NOTE NOTE NOTE! With this patch we drop the insane strip/escape bit.
Only the caller knows if this is for display on html or for other uses,
so we'll be true and not mangle the data.

A review of all callers in 1.8 shows no problem - the strings were being
strip/escaped already.
---
 lib/accesslib.php | 40 +++++++++++++++++++++++++++++++++-------
 1 file changed, 33 insertions(+), 7 deletions(-)

diff --git a/lib/accesslib.php b/lib/accesslib.php
index 3e97237f00..bcfe63982e 100755
--- a/lib/accesslib.php
+++ b/lib/accesslib.php
@@ -3831,20 +3831,46 @@ function allow_assign($sroleid, $troleid) {
 /**
  * Gets a list of roles that this user can assign in this context
  * @param object $context
+ * @param string $field
  * @return array
  */
 function get_assignable_roles ($context, $field="name") {
 
-    $options = array();
+    global $CFG;
 
-    if ($roles = get_all_roles()) {
-        foreach ($roles as $role) {
-            if (user_can_assign($context, $role->id)) {
-                $options[$role->id] = strip_tags(format_string($role->{$field}, true));
-            }
+    // this users RAs
+    $ras = get_user_roles($context);
+    $roleids = array();
+    foreach ($ras as $ra) {
+        $roleids[] = $ra->roleid;
+    }
+    unset($ra);
+
+    if (count($roleids)===0) {
+        return array();
+    }
+
+    $roleids = implode(',',$roleids);
+
+    // The subselect scopes the DISTINCT down to
+    // the role ids - a DISTINCT over the whole of
+    // the role table is much more expensive on some DBs
+    $sql = "SELECT r.id, r.$field
+            FROM {$CFG->prefix}role r
+            JOIN ( SELECT DISTINCT allowassign as allowedrole 
+                   FROM  {$CFG->prefix}role_allow_assign raa
+                   WHERE raa.roleid IN ($roleids) ) ar
+              ON r.id=ar.allowedrole
+            ORDER BY sortorder ASC";
+
+    $rs = get_recordset_sql($sql);
+    $roles = array();
+    if ($rs->RecordCount()) {
+        while ($r = rs_fetch_next_record($rs)) {
+            $roles[$r->id] = $r->{$field};
         }
     }
-    return $options;
+    return $roles;
 }
 
 /**
-- 
2.39.5