Refactor Group#all_projects to use recursive CTEall-projects-recursive-cte

Use recursive CTE to find all projects under group instead of querieng
routes with LIKE.

1 files changed, 17 insertions, 1 deletions

diff --git a/app/models/namespace.rb b/app/models/namespace.rb
index 058350b16ce..d6e2d87c70d 100644
--- a/app/models/namespace.rb
+++ b/app/models/namespace.rb
@@ -232,7 +232,23 @@ class Namespace < ApplicationRecord
   # Includes projects from this namespace and projects from all subgroups
   # that belongs to this namespace
   def all_projects
-    Project.inside_path(full_path)
+    return Project.none unless id
+
+    cte = <<~SQL
+      (WITH RECURSIVE groups AS
+         (SELECT id,
+                 parent_id
+          FROM namespaces
+          WHERE id = #{id}
+          UNION SELECT namespaces.id,
+                       namespaces.parent_id
+          FROM namespaces
+          INNER JOIN groups ON groups.id = namespaces.parent_id) SELECT projects.*
+       FROM groups
+       JOIN projects ON projects.namespace_id = groups.id)
+    SQL
+
+    Project.from("#{cte} AS projects")
   end
 
   # Includes pipelines from this namespace and pipelines from all subgroups