Refactor ProjectsFinder#init_collection

This changes ProjectsFinder#init_collection so it no longer relies on a
UNION. For example, to get starred projects of a user we used to run:

    SELECT projects.*
    FROM projects
    WHERE projects.pending_delete = 'f'
    AND (
        projects.id IN (
            SELECT projects.id
            FROM projects
            INNER JOIN users_star_projects
                ON users_star_projects.project_id = projects.id
            INNER JOIN project_authorizations
                ON projects.id = project_authorizations.project_id
            WHERE projects.pending_delete = 'f'
            AND project_authorizations.user_id = 1
            AND users_star_projects.user_id = 1

            UNION

            SELECT projects.id
            FROM projects
            INNER JOIN users_star_projects
                ON users_star_projects.project_id = projects.id
            WHERE projects.visibility_level IN (20, 10)
            AND users_star_projects.user_id = 1
        )
    )
    ORDER BY projects.id DESC;

With these changes the above query is turned into the following instead:

    SELECT projects.*
    FROM projects
    INNER JOIN users_star_projects
        ON users_star_projects.project_id = projects.id
    WHERE projects.pending_delete = 'f'
    AND (
        EXISTS (
            SELECT 1
            FROM project_authorizations
            WHERE project_authorizations.user_id = 1
            AND (project_id = projects.id)
        )
        OR projects.visibility_level IN (20,10)
    )
    AND users_star_projects.user_id = 1
    ORDER BY projects.id DESC;

This query in turn produces a better execution plan and takes less time,
though the difference is only a few milliseconds (this however depends
on the amount of data involved and additional conditions that may be
added).

1 files changed, 14 insertions, 12 deletions

diff --git a/lib/gitlab/visibility_level.rb b/lib/gitlab/visibility_level.rb
index 2b53798e70f..36e5b5041a6 100644
--- a/lib/gitlab/visibility_level.rb
+++ b/lib/gitlab/visibility_level.rb
@@ -13,18 +13,8 @@ module Gitlab
       scope :public_and_internal_only,  -> { where(visibility_level: [PUBLIC, INTERNAL] ) }
       scope :non_public_only,           -> { where.not(visibility_level: PUBLIC) }
 
-      scope :public_to_user, -> (user) do
-        if user
-          if user.admin?
-            all
-          elsif !user.external?
-            public_and_internal_only
-          else
-            public_only
-          end
-        else
-          public_only
-        end
+      scope :public_to_user, -> (user = nil) do
+        where(visibility_level: VisibilityLevel.levels_for_user(user))
       end
     end
 
@@ -35,6 +25,18 @@ module Gitlab
     class << self
       delegate :values, to: :options
 
+      def levels_for_user(user = nil)
+        return [PUBLIC] unless user
+
+        if user.admin?
+          [PRIVATE, INTERNAL, PUBLIC]
+        elsif user.external?
+          [PUBLIC]
+        else
+          [INTERNAL, PUBLIC]
+        end
+      end
+
       def string_values
         string_options.keys
       end