1 files changed, 30 insertions, 7 deletions
diff --git a/app/models/namespaces/traversal/linear_scopes.rb b/app/models/namespaces/traversal/linear_scopes.rb
index 0dfb7320461..9f0f49e729c 100644
--- a/app/models/namespaces/traversal/linear_scopes.rb
+++ b/app/models/namespaces/traversal/linear_scopes.rb
@@ -22,19 +22,28 @@ module Namespaces
           unscoped.where(id: root_ids)
         end
 
-        def self_and_ancestors(include_self: true, hierarchy_order: nil)
+        def self_and_ancestors(include_self: true, upto: nil, hierarchy_order: nil)
           return super unless use_traversal_ids_for_ancestor_scopes?
 
+          ancestors_cte, base_cte = ancestor_ctes
+          namespaces = Arel::Table.new(:namespaces)
+
           records = unscoped
-            .where(id: select('unnest(traversal_ids)'))
+            .with(base_cte.to_arel, ancestors_cte.to_arel)
+            .distinct
+            .from([ancestors_cte.table, namespaces])
+            .where(namespaces[:id].eq(ancestors_cte.table[:ancestor_id]))
             .order_by_depth(hierarchy_order)
-            .normal_select
 
-          if include_self
-            records
-          else
-            records.where.not(id: all.as_ids)
+          unless include_self
+            records = records.where(ancestors_cte.table[:base_id].not_eq(ancestors_cte.table[:ancestor_id]))
+          end
+
+          if upto
+            records = records.where.not(id: unscoped.where(id: upto).select('unnest(traversal_ids)'))
           end
+
+          records
         end
 
         def self_and_ancestor_ids(include_self: true)
@@ -150,6 +159,20 @@ module Namespaces
             records.where('namespaces.id <> base.id')
           end
         end
+
+        def ancestor_ctes
+          base_scope = all.select('namespaces.id', 'namespaces.traversal_ids')
+          base_cte = Gitlab::SQL::CTE.new(:base_ancestors_cte, base_scope)
+
+          # We have to alias id with 'AS' to avoid ambiguous column references by calling methods.
+          ancestors_scope = unscoped
+            .unscope(where: [:type])
+            .select('id as base_id', 'unnest(traversal_ids) as ancestor_id')
+            .from(base_cte.table)
+          ancestors_cte = Gitlab::SQL::CTE.new(:ancestors_cte, ancestors_scope)
+
+          [ancestors_cte, base_cte]
+        end
       end
     end
   end