1 files changed, 46 insertions, 33 deletions

diff --git a/app/models/user.rb b/app/models/user.rb
index 61abea1f6ea..9374f01f99f 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -389,42 +389,23 @@ class User < ActiveRecord::Base
     end
   end
 
-  # Groups user has access to
+  # Returns the groups a user has access to
   def authorized_groups
-    @authorized_groups ||= begin
-                             group_ids = (groups.pluck(:id) + authorized_projects.pluck(:namespace_id))
-                             Group.where(id: group_ids)
-                           end
-  end
+    union = Gitlab::SQL::Union.
+      new([groups.select(:id), authorized_projects.select(:namespace_id)])
 
-  def authorized_projects_id
-    @authorized_projects_id ||= begin
-      project_ids = personal_projects.pluck(:id)
-      project_ids.push(*groups_projects.pluck(:id))
-      project_ids.push(*projects.pluck(:id).uniq)
-    end
-  end
-
-  def master_or_owner_projects_id
-    @master_or_owner_projects_id ||= begin
-      scope = { access_level: [ Gitlab::Access::MASTER, Gitlab::Access::OWNER ] }
-      project_ids = personal_projects.pluck(:id)
-      project_ids.push(*groups_projects.where(members: scope).pluck(:id))
-      project_ids.push(*projects.where(members: scope).pluck(:id).uniq)
-    end
+    Group.where("namespaces.id IN (#{union.to_sql})")
   end
 
-  # Projects user has access to
+  # Returns the groups a user is authorized to access.
   def authorized_projects
-    @authorized_projects ||= Project.where(id: authorized_projects_id)
+    Project.where("projects.id IN (#{projects_union.to_sql})")
   end
 
   def owned_projects
     @owned_projects ||=
-      begin
-        namespace_ids = owned_groups.pluck(:id).push(namespace.id)
-        Project.in_namespace(namespace_ids).joins(:namespace)
-      end
+      Project.where('namespace_id IN (?) OR namespace_id = ?',
+                    owned_groups.select(:id), namespace.id).joins(:namespace)
   end
 
   # Team membership in authorized projects
@@ -739,12 +720,25 @@ class User < ActiveRecord::Base
     Doorkeeper::AccessToken.where(resource_owner_id: self.id, revoked_at: nil)
   end
 
-  def contributed_projects_ids
-    Event.contributions.where(author_id: self).
+  # Returns the projects a user contributed to in the last year.
+  #
+  # This method relies on a subquery as this performs significantly better
+  # compared to a JOIN when coupled with, for example,
+  # `Project.visible_to_user`. That is, consider the following code:
+  #
+  #     some_user.contributed_projects.visible_to_user(other_user)
+  #
+  # If this method were to use a JOIN the resulting query would take roughly 200
+  # ms on a database with a similar size to GitLab.com's database. On the other
+  # hand, using a subquery means we can get the exact same data in about 40 ms.
+  def contributed_projects
+    events = Event.select(:project_id).
+      contributions.where(author_id: self).
       where("created_at > ?", Time.now - 1.year).
-      reorder(project_id: :desc).
-      select(:project_id).
-      uniq.map(&:project_id)
+      uniq.
+      reorder(nil)
+
+    Project.where(id: events)
   end
 
   def restricted_signup_domains
@@ -777,8 +771,27 @@ class User < ActiveRecord::Base
   def ci_authorized_runners
     @ci_authorized_runners ||= begin
       runner_ids = Ci::RunnerProject.joins(:project).
-        where(ci_projects: { gitlab_id: master_or_owner_projects_id }).select(:runner_id)
+        where("ci_projects.gitlab_id IN (#{ci_projects_union.to_sql})").
+        select(:runner_id)
+
       Ci::Runner.specific.where(id: runner_ids)
     end
   end
+
+  private
+
+  def projects_union
+    Gitlab::SQL::Union.new([personal_projects.select(:id),
+                            groups_projects.select(:id),
+                            projects.select(:id)])
+  end
+
+  def ci_projects_union
+    scope  = { access_level: [Gitlab::Access::MASTER, Gitlab::Access::OWNER] }
+    groups = groups_projects.where(members: scope)
+    other  = projects.where(members: scope)
+
+    Gitlab::SQL::Union.new([personal_projects.select(:id), groups.select(:id),
+                            other.select(:id)])
+  end
 end