diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2017-06-13 17:44:55 +0300 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2017-06-16 14:49:09 +0300 |
commit | d29347220c07ab0191cf208d3775475c7b5d71ca (patch) | |
tree | cdbf60cadb6a85c494385a97c4fe8a9497c8586c /app/finders | |
parent | 2b34f3f20d5a5c8ecdf6e8842892cb2b4ed3c89a (diff) |
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).
Diffstat (limited to 'app/finders')
-rw-r--r-- | app/finders/projects_finder.rb | 76 |
1 files changed, 57 insertions, 19 deletions
diff --git a/app/finders/projects_finder.rb b/app/finders/projects_finder.rb index 5bf722d1ec6..72e9c7a1cd7 100644 --- a/app/finders/projects_finder.rb +++ b/app/finders/projects_finder.rb @@ -28,34 +28,72 @@ class ProjectsFinder < UnionFinder end def execute - items = init_collection - items = items.map do |item| - item = by_ids(item) - item = by_personal(item) - item = by_starred(item) - item = by_trending(item) - item = by_visibilty_level(item) - item = by_tags(item) - item = by_search(item) - by_archived(item) - end - items = union(items) - sort(items) + collection = init_collection + collection = by_ids(collection) + collection = by_personal(collection) + collection = by_starred(collection) + collection = by_trending(collection) + collection = by_visibilty_level(collection) + collection = by_tags(collection) + collection = by_search(collection) + collection = by_archived(collection) + + sort(collection) end private def init_collection - projects = [] + if current_user + collection_with_user + else + collection_without_user + end + end - if params[:owned].present? - projects << current_user.owned_projects if current_user + def collection_with_user + if owned_projects? + current_user.owned_projects else - projects << current_user.authorized_projects if current_user - projects << Project.unscoped.public_to_user(current_user) unless params[:non_public].present? + if private_only? + current_user.authorized_projects + else + collection_with_user_and_public_projects + end end + end + + # Builds a collection for a signed in user that includes additional projects + # such as public and internal ones. + # + # This method manually constructs some WHERE conditions in order to ensure the + # produced query is as efficient as possible. + def collection_with_user_and_public_projects + levels = Gitlab::VisibilityLevel.levels_for_user(current_user) + authorized = current_user.project_authorizations. + select(1). + where('project_id = projects.id') + + Project.where('EXISTS (?) OR projects.visibility_level IN (?)', + authorized, + levels) + end + + # Builds a collection for an anonymous user. + def collection_without_user + if private_only? || owned_projects? + Project.none + else + Project.public_to_user + end + end + + def owned_projects? + params[:owned].present? + end - projects + def private_only? + params[:non_public].present? end def by_ids(items) |