Optimize query for User#groups_through_project_authorizationstc-fix-group-finder-subgrouping-9-2

Instead of plucking paths, and doing LIKE/OR on each of them, use a subquery to
find groups that are the ancestor of the projects the user is member of.

2 files changed, 9 insertions, 10 deletions

diff --git a/app/models/user.rb b/app/models/user.rb
index e2d840a1fe0..157e0948cd3 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -493,15 +493,14 @@ class User < ActiveRecord::Base
   end
 
   def groups_through_project_authorizations
-    paths = Project.member_self_and_descendants(id).pluck('routes.path')
-
-    return Group.none if paths.empty?
-
-    wheres = paths.map do |path|
-      "#{ActiveRecord::Base.connection.quote(path)} LIKE CONCAT(routes.path, '/%')"
-    end
-
-    Group.joins(:route).where(wheres.join(' OR '))
+    projects = Project.joins(:project_authorizations).
+                 where('project_authorizations.user_id = ?', id ).
+                 joins(:route).
+                 select('routes.path AS full_path')
+
+    Group.joins(:route).
+      joins("INNER JOIN (#{projects.to_sql}) project_paths
+            ON project_paths.full_path LIKE CONCAT(routes_namespaces.path, '/%')")
   end
 
   def nested_groups
diff --git a/spec/finders/groups_finder_spec.rb b/spec/finders/groups_finder_spec.rb
index 31f1f753d4d..e082631ce68 100644
--- a/spec/finders/groups_finder_spec.rb
+++ b/spec/finders/groups_finder_spec.rb
@@ -91,7 +91,7 @@ describe GroupsFinder do
               subsubproject = create(:empty_project, :private, namespace: private_subsubgroup)
               subsubproject.add_guest(user)
 
-              is_expected.to include(private_subsubgroup)
+              expect(described_class.new(user).execute).to include(private_subsubgroup, private_subgroup, parent_group)
             end
           end
         end