Create CTE query for clusters hierarchy

- This enables us to use a scope to query all clusters in group
hierarchy order in one query, and also enables us to union to instance
clusters later.

- Handle case where clusters not present at level. In which case the
query should go ahead and return the next level's clusters.

- Swap with new CTE query behind Feature flag. This FF is default
disabled.

2 files changed, 121 insertions, 2 deletions

diff --git a/app/models/clusters/clusters_hierarchy.rb b/app/models/clusters/clusters_hierarchy.rb
new file mode 100644
index 00000000000..dab034b7234
--- /dev/null
+++ b/app/models/clusters/clusters_hierarchy.rb
@@ -0,0 +1,104 @@
+# frozen_string_literal: true
+
+module Clusters
+  class ClustersHierarchy
+    DEPTH_COLUMN = :depth
+
+    def initialize(clusterable)
+      @clusterable = clusterable
+    end
+
+    # Returns clusters in order from deepest to highest group
+    def base_and_ancestors
+      cte = recursive_cte
+      cte_alias = cte.table.alias(model.table_name)
+
+      model
+        .unscoped
+        .where('clusters.id IS NOT NULL')
+        .with
+        .recursive(cte.to_arel)
+        .from(cte_alias)
+        .order(DEPTH_COLUMN => :asc)
+    end
+
+    private
+
+    attr_reader :clusterable
+
+    def recursive_cte
+      cte = Gitlab::SQL::RecursiveCTE.new(:clusters_cte)
+
+      base_query = case clusterable
+                   when ::Group
+                     group_clusters_base_query
+                   when ::Project
+                     project_clusters_base_query
+                   else
+                     raise ArgumentError, "unknown type for #{clusterable}"
+                   end
+
+      cte << base_query
+      cte << parent_query(cte)
+
+      cte
+    end
+
+    def group_clusters_base_query
+      group_parent_id_alias = alias_as_column(groups[:parent_id], 'group_parent_id')
+      join_sources = ::Group.left_joins(:clusters).join_sources
+
+      model
+        .unscoped
+        .select([clusters_star, group_parent_id_alias, "1 AS #{DEPTH_COLUMN}"])
+        .where(groups[:id].eq(clusterable.id))
+        .from(groups)
+        .joins(join_sources)
+    end
+
+    def project_clusters_base_query
+      projects = ::Project.arel_table
+      project_parent_id_alias = alias_as_column(projects[:namespace_id], 'group_parent_id')
+      join_sources = ::Project.left_joins(:clusters).join_sources
+
+      model
+        .unscoped
+        .select([clusters_star, project_parent_id_alias, "1 AS #{DEPTH_COLUMN}"])
+        .where(projects[:id].eq(clusterable.id))
+        .from(projects)
+        .joins(join_sources)
+    end
+
+    def parent_query(cte)
+      group_parent_id_alias = alias_as_column(groups[:parent_id], 'group_parent_id')
+
+      model
+        .unscoped
+        .select([clusters_star, group_parent_id_alias, cte.table[DEPTH_COLUMN] + 1])
+        .from([cte.table, groups])
+        .joins('LEFT OUTER JOIN cluster_groups ON cluster_groups.group_id = namespaces.id')
+        .joins('LEFT OUTER JOIN clusters ON cluster_groups.cluster_id = clusters.id')
+        .where(groups[:id].eq(cte.table[:group_parent_id]))
+    end
+
+    def model
+      Clusters::Cluster
+    end
+
+    def clusters
+      @clusters ||= model.arel_table
+    end
+
+    def groups
+      @groups ||= ::Group.arel_table
+    end
+
+    def clusters_star
+      @clusters_star ||= clusters[Arel.star]
+    end
+
+    def alias_as_column(value, alias_to)
+      Arel::Nodes::As.new(value, Arel::Nodes::SqlLiteral.new(alias_to))
+    end
+  end
+end
diff --git a/app/models/concerns/deployment_platform.rb b/app/models/concerns/deployment_platform.rb
index 5a358ae2ef6..8f28c897eb6 100644
--- a/app/models/concerns/deployment_platform.rb
+++ b/app/models/concerns/deployment_platform.rb
@@ -12,11 +12,26 @@ module DeploymentPlatform
   private
 
   def find_deployment_platform(environment)
-    find_cluster_platform_kubernetes(environment: environment) ||
-      find_group_cluster_platform_kubernetes(environment: environment) ||
+    find_platform_kubernetes(environment) ||
       find_instance_cluster_platform_kubernetes(environment: environment)
   end
 
+  def find_platform_kubernetes(environment)
+    if Feature.enabled?(:clusters_cte)
+      find_platform_kubernetes_with_cte(environment)
+    else
+      find_cluster_platform_kubernetes(environment: environment) ||
+        find_group_cluster_platform_kubernetes(environment: environment)
+    end
+  end
+
+  # EE would override this and utilize environment argument
+  def find_platform_kubernetes_with_cte(_environment)
+    Clusters::ClustersHierarchy.new(self).base_and_ancestors
+      .enabled.default_environment
+      .first&.platform_kubernetes
+  end
+
   # EE would override this and utilize environment argument
   def find_cluster_platform_kubernetes(environment: nil)
     clusters.enabled.default_environment