Use `EXISTS` to load projects from owned groupsbvl-optimize-owned-projects-query

This should be more efficient than the old `namespace_id IN` query.

2 files changed, 15 insertions, 4 deletions

diff --git a/app/models/user.rb b/app/models/user.rb
index b8c55205ab8..c896b7be78d 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -5,6 +5,7 @@ class User < ActiveRecord::Base
 
   include Gitlab::ConfigHelper
   include Gitlab::SQL::Pattern
+  include Gitlab::Utils::StrongMemoize
   include AfterCommitQueue
   include Avatarable
   include Referable
@@ -98,7 +99,7 @@ class User < ActiveRecord::Base
 
   # Groups
   has_many :members
-  has_many :group_members, -> { where(requested_at: nil) }, source: 'GroupMember'
+  has_many :group_members, -> { where(requested_at: nil) }
   has_many :groups, through: :group_members
   has_many :owned_groups, -> { where members: { access_level: Gitlab::Access::OWNER } }, through: :group_members, source: :group
   has_many :masters_groups, -> { where members: { access_level: Gitlab::Access::MASTER } }, through: :group_members, source: :group
@@ -623,9 +624,15 @@ class User < ActiveRecord::Base
   end
 
   def owned_projects
-    @owned_projects ||=
-      Project.where('namespace_id IN (?) OR namespace_id = ?',
-                    owned_groups.select(:id), namespace.id).joins(:namespace)
+    strong_memoize(:owned_projects) do
+      group_owned = group_members
+                      .where(access_level: Gitlab::Access::OWNER)
+                      .where("projects.namespace_id = members.source_id")
+                      .select(1)
+
+      Project.where('namespace_id = ? OR EXISTS (?)',
+                    namespace.id, group_owned)
+    end
   end
 
   # Returns projects which user can admin issues on (for example to move an issue to that project).
diff --git a/spec/models/user_spec.rb b/spec/models/user_spec.rb
index 5680eb24985..bd0a0c0158c 100644
--- a/spec/models/user_spec.rb
+++ b/spec/models/user_spec.rb
@@ -661,6 +661,8 @@ describe User do
   describe 'projects' do
     before do
       @user = create(:user)
+      owned_group = create(:group)
+      owned_group.add_owner(@user)
 
       @project = create(:project, namespace: @user.namespace)
       @project_2 = create(:project, group: create(:group)) do |project|
@@ -669,6 +671,7 @@ describe User do
       @project_3 = create(:project, group: create(:group)) do |project|
         project.add_developer(@user)
       end
+      @project_4 = create(:project, group: owned_group)
     end
 
     it { expect(@user.authorized_projects).to include(@project) }
@@ -677,6 +680,7 @@ describe User do
     it { expect(@user.owned_projects).to include(@project) }
     it { expect(@user.owned_projects).not_to include(@project_2) }
     it { expect(@user.owned_projects).not_to include(@project_3) }
+    it { expect(@user.owned_projects).to include(@project_4) }
     it { expect(@user.personal_projects).to include(@project) }
     it { expect(@user.personal_projects).not_to include(@project_2) }
     it { expect(@user.personal_projects).not_to include(@project_3) }