1 files changed, 60 insertions, 11 deletions

diff --git a/app/models/concerns/routable.rb b/app/models/concerns/routable.rb
index f2badfe48dd..ef14ff5fbe2 100644
--- a/app/models/concerns/routable.rb
+++ b/app/models/concerns/routable.rb
@@ -14,7 +14,17 @@ module Routable
   #     Routable.find_by_full_path('groupname/projectname') # -> Project
   #
   # Returns a single object, or nil.
-  def self.find_by_full_path(path, follow_redirects: false, route_scope: Route, redirect_route_scope: RedirectRoute)
+
+  # rubocop:disable Metrics/CyclomaticComplexity
+  # rubocop:disable Metrics/PerceivedComplexity
+  def self.find_by_full_path(
+    path,
+    follow_redirects: false,
+    route_scope: Route,
+    redirect_route_scope: RedirectRoute,
+    optimize_routable: Routable.optimize_routable_enabled?
+  )
+
     return unless path.present?
 
     # Convert path to string to prevent DB error: function lower(integer) does not exist
@@ -25,20 +35,50 @@ module Routable
     #
     # We need to qualify the columns with the table name, to support both direct lookups on
     # Route/RedirectRoute, and scoped lookups through the Routable classes.
-    Gitlab::Database.allow_cross_joins_across_databases(url: "https://gitlab.com/gitlab-org/gitlab/-/issues/420046") do
+    if optimize_routable
+      path_condition = { path: path }
+
+      source_type_condition = if route_scope == Route
+                                {}
+                              else
+                                { source_type: route_scope.klass.base_class }
+                              end
+
       route =
-        route_scope.find_by(routes: { path: path }) ||
-        route_scope.iwhere(Route.arel_table[:path] => path).take
+        Route.where(source_type_condition).find_by(path_condition) ||
+        Route.where(source_type_condition).iwhere(path_condition).take
 
       if follow_redirects
-        route ||= redirect_route_scope.iwhere(RedirectRoute.arel_table[:path] => path).take
+        route ||= RedirectRoute.where(source_type_condition).iwhere(path_condition).take
       end
 
-      next unless route
+      return unless route
+      return route.source if route_scope == Route
+
+      route_scope.find_by(id: route.source_id)
+    else
+      Gitlab::Database.allow_cross_joins_across_databases(url:
+        "https://gitlab.com/gitlab-org/gitlab/-/issues/420046") do
+        route =
+          route_scope.find_by(routes: { path: path }) ||
+          route_scope.iwhere(Route.arel_table[:path] => path).take
+
+        if follow_redirects
+          route ||= redirect_route_scope.iwhere(RedirectRoute.arel_table[:path] => path).take
+        end
 
-      route.is_a?(Routable) ? route : route.source
+        next unless route
+
+        route.is_a?(Routable) ? route : route.source
+      end
     end
   end
+  # rubocop:enable Metrics/PerceivedComplexity
+  # rubocop:enable Metrics/CyclomaticComplexity
+
+  def self.optimize_routable_enabled?
+    Feature.enabled?(:optimize_routable)
+  end
 
   included do
     # Remove `inverse_of: source` when upgraded to rails 5.2
@@ -67,13 +107,22 @@ module Routable
     #
     # Returns a single object, or nil.
     def find_by_full_path(path, follow_redirects: false)
-      # TODO: Optimize these queries by avoiding joins
-      # https://gitlab.com/gitlab-org/gitlab/-/issues/292252
+      optimize_routable = Routable.optimize_routable_enabled?
+
+      if optimize_routable
+        route_scope = all
+        redirect_route_scope = RedirectRoute
+      else
+        route_scope = includes(:route).references(:routes)
+        redirect_route_scope = joins(:redirect_routes)
+      end
+
       Routable.find_by_full_path(
         path,
         follow_redirects: follow_redirects,
-        route_scope: includes(:route).references(:routes),
-        redirect_route_scope: joins(:redirect_routes)
+        route_scope: route_scope,
+        redirect_route_scope: redirect_route_scope,
+        optimize_routable: optimize_routable
       )
     end