MDL-57027 accesslib: Improve perf of sql

diff --git a/lib/accesslib.php b/lib/accesslib.php
index 2798f85..64126eb 100644 (file)
--- a/lib/accesslib.php
+++ b/lib/accesslib.php
@@ -3647,11 +3647,15 @@ function get_users_by_capability(context $context, $capability, $fields = '', $s
                                                           AND roleid IN (".implode(',', array_keys($prohibited[$cap])) ."))";
 
                 } else {
-                    $unions[] = "SELECT ra.userid
-                                   FROM {role_assignments} ra
-                              LEFT JOIN {role_assignments} rap ON (rap.userid = ra.userid AND rap.contextid IN ($ctxids) AND rap.roleid IN (".implode(',', array_keys($prohibited[$cap])) ."))
-                                  WHERE ra.contextid IN ($ctxids) AND ra.roleid IN (".implode(',', array_keys($needed[$cap])) .")
-                                        AND rap.id IS NULL";
+                    $unions[] = "SELECT userid
+                                   FROM {role_assignments}
+                                  WHERE contextid IN ($ctxids) AND roleid IN (".implode(',', array_keys($needed[$cap])) .")
+                                        AND userid NOT IN (
+                                            SELECT userid
+                                              FROM {role_assignments}
+                                             WHERE contextid IN ($ctxids)
+                                                    AND roleid IN (" . implode(',', array_keys($prohibited[$cap])) . ")
+                                                        )";
                 }
             }
         }