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, 31 insertions, 0 deletions

diff --git a/spec/lib/gitlab/visibility_level_spec.rb b/spec/lib/gitlab/visibility_level_spec.rb
index 3255c6f1ef7..a8f21803ec7 100644
--- a/spec/lib/gitlab/visibility_level_spec.rb
+++ b/spec/lib/gitlab/visibility_level_spec.rb
@@ -18,4 +18,35 @@ describe Gitlab::VisibilityLevel, lib: true do
       expect(described_class.level_value(100)).to eq(Gitlab::VisibilityLevel::PRIVATE)
     end
   end
+
+  describe '.levels_for_user' do
+    it 'returns all levels for an admin' do
+      user = double(:user, admin?: true)
+
+      expect(described_class.levels_for_user(user)).
+        to eq([Gitlab::VisibilityLevel::PRIVATE,
+               Gitlab::VisibilityLevel::INTERNAL,
+               Gitlab::VisibilityLevel::PUBLIC])
+    end
+
+    it 'returns INTERNAL and PUBLIC for internal users' do
+      user = double(:user, admin?: false, external?: false)
+
+      expect(described_class.levels_for_user(user)).
+        to eq([Gitlab::VisibilityLevel::INTERNAL,
+               Gitlab::VisibilityLevel::PUBLIC])
+    end
+
+    it 'returns PUBLIC for external users' do
+      user = double(:user, admin?: false, external?: true)
+
+      expect(described_class.levels_for_user(user)).
+        to eq([Gitlab::VisibilityLevel::PUBLIC])
+    end
+
+    it 'returns PUBLIC when no user is given' do
+      expect(described_class.levels_for_user).
+        to eq([Gitlab::VisibilityLevel::PUBLIC])
+    end
+  end
 end