Skip to content

Add organization_id to ProjectsFinder

Abdul Wadood requested to merge 429761-add-organization-id-projects-finder into master

What does this MR do and why?

It will be used to filter projects belonging to an organization to create a GraphQL query to get projects of an organization in Create organization projects GraphQL resolver (#423327 - closed).

Query plans

Before

Raw Query 
SELECT "projects"."id",
       "projects"."name",
       "projects"."path",
       "projects"."description",
       "projects"."created_at",
       "projects"."updated_at",
       "projects"."creator_id",
       "projects"."namespace_id",
       "projects"."last_activity_at",
       "projects"."import_url",
       "projects"."visibility_level",
       "projects"."archived",
       "projects"."avatar",
       "projects"."merge_requests_template",
       "projects"."star_count",
       "projects"."merge_requests_rebase_enabled",
       "projects"."import_type",
       "projects"."import_source",
       "projects"."approvals_before_merge",
       "projects"."reset_approvals_on_push",
       "projects"."merge_requests_ff_only_enabled",
       "projects"."issues_template",
       "projects"."mirror",
       "projects"."mirror_last_update_at",
       "projects"."mirror_last_successful_update_at",
       "projects"."mirror_user_id",
       "projects"."shared_runners_enabled",
       "projects"."runners_token",
       "projects"."build_allow_git_fetch",
       "projects"."build_timeout",
       "projects"."mirror_trigger_builds",
       "projects"."pending_delete",
       "projects"."public_builds",
       "projects"."last_repository_check_failed",
       "projects"."last_repository_check_at",
       "projects"."only_allow_merge_if_pipeline_succeeds",
       "projects"."has_external_issue_tracker",
       "projects"."repository_storage",
       "projects"."repository_read_only",
       "projects"."request_access_enabled",
       "projects"."has_external_wiki",
       "projects"."ci_config_path",
       "projects"."lfs_enabled",
       "projects"."description_html",
       "projects"."only_allow_merge_if_all_discussions_are_resolved",
       "projects"."repository_size_limit",
       "projects"."printing_merge_request_link_enabled",
       "projects"."auto_cancel_pending_pipelines",
       "projects"."service_desk_enabled",
       "projects"."cached_markdown_version",
       "projects"."delete_error",
       "projects"."last_repository_updated_at",
       "projects"."disable_overriding_approvers_per_merge_request",
       "projects"."storage_version",
       "projects"."resolve_outdated_diff_discussions",
       "projects"."remote_mirror_available_overridden",
       "projects"."only_mirror_protected_branches",
       "projects"."pull_mirror_available_overridden",
       "projects"."jobs_cache_index",
       "projects"."external_authorization_classification_label",
       "projects"."mirror_overwrites_diverged_branches",
       "projects"."pages_https_only",
       "projects"."external_webhook_token",
       "projects"."packages_enabled",
       "projects"."merge_requests_author_approval",
       "projects"."pool_repository_id",
       "projects"."runners_token_encrypted",
       "projects"."bfg_object_map",
       "projects"."detected_repository_languages",
       "projects"."merge_requests_disable_committers_approval",
       "projects"."require_password_to_approve",
       "projects"."max_pages_size",
       "projects"."max_artifacts_size",
       "projects"."pull_mirror_branch_prefix",
       "projects"."remove_source_branch_after_merge",
       "projects"."marked_for_deletion_at",
       "projects"."marked_for_deletion_by_user_id",
       "projects"."autoclose_referenced_issues",
       "projects"."suggestion_commit_message",
       "projects"."project_namespace_id",
       "projects"."hidden",
       "projects"."organization_id"
FROM "projects"
WHERE (EXISTS (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 10327656
                 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10, 20))
  AND "projects"."pending_delete" = FALSE
  AND "projects"."hidden" = FALSE
ORDER BY "projects"."id" DESC
LIMIT 20;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23811/commands/76341

After

Raw query 
EXPLAIN
SELECT "projects"."id",
       "projects"."name",
       "projects"."path",
       "projects"."description",
       "projects"."created_at",
       "projects"."updated_at",
       "projects"."creator_id",
       "projects"."namespace_id",
       "projects"."last_activity_at",
       "projects"."import_url",
       "projects"."visibility_level",
       "projects"."archived",
       "projects"."avatar",
       "projects"."merge_requests_template",
       "projects"."star_count",
       "projects"."merge_requests_rebase_enabled",
       "projects"."import_type",
       "projects"."import_source",
       "projects"."approvals_before_merge",
       "projects"."reset_approvals_on_push",
       "projects"."merge_requests_ff_only_enabled",
       "projects"."issues_template",
       "projects"."mirror",
       "projects"."mirror_last_update_at",
       "projects"."mirror_last_successful_update_at",
       "projects"."mirror_user_id",
       "projects"."shared_runners_enabled",
       "projects"."runners_token",
       "projects"."build_allow_git_fetch",
       "projects"."build_timeout",
       "projects"."mirror_trigger_builds",
       "projects"."pending_delete",
       "projects"."public_builds",
       "projects"."last_repository_check_failed",
       "projects"."last_repository_check_at",
       "projects"."only_allow_merge_if_pipeline_succeeds",
       "projects"."has_external_issue_tracker",
       "projects"."repository_storage",
       "projects"."repository_read_only",
       "projects"."request_access_enabled",
       "projects"."has_external_wiki",
       "projects"."ci_config_path",
       "projects"."lfs_enabled",
       "projects"."description_html",
       "projects"."only_allow_merge_if_all_discussions_are_resolved",
       "projects"."repository_size_limit",
       "projects"."printing_merge_request_link_enabled",
       "projects"."auto_cancel_pending_pipelines",
       "projects"."service_desk_enabled",
       "projects"."cached_markdown_version",
       "projects"."delete_error",
       "projects"."last_repository_updated_at",
       "projects"."disable_overriding_approvers_per_merge_request",
       "projects"."storage_version",
       "projects"."resolve_outdated_diff_discussions",
       "projects"."remote_mirror_available_overridden",
       "projects"."only_mirror_protected_branches",
       "projects"."pull_mirror_available_overridden",
       "projects"."jobs_cache_index",
       "projects"."external_authorization_classification_label",
       "projects"."mirror_overwrites_diverged_branches",
       "projects"."pages_https_only",
       "projects"."external_webhook_token",
       "projects"."packages_enabled",
       "projects"."merge_requests_author_approval",
       "projects"."pool_repository_id",
       "projects"."runners_token_encrypted",
       "projects"."bfg_object_map",
       "projects"."detected_repository_languages",
       "projects"."merge_requests_disable_committers_approval",
       "projects"."require_password_to_approve",
       "projects"."max_pages_size",
       "projects"."max_artifacts_size",
       "projects"."pull_mirror_branch_prefix",
       "projects"."remove_source_branch_after_merge",
       "projects"."marked_for_deletion_at",
       "projects"."marked_for_deletion_by_user_id",
       "projects"."autoclose_referenced_issues",
       "projects"."suggestion_commit_message",
       "projects"."project_namespace_id",
       "projects"."hidden",
       "projects"."organization_id"
FROM "projects"
WHERE (EXISTS (SELECT 1
               FROM "project_authorizations"
               WHERE "project_authorizations"."user_id" = 10327656
                 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10, 20))
  AND "projects"."pending_delete" = FALSE
  AND "projects"."organization_id" = 1
  AND "projects"."hidden" = FALSE
ORDER BY "projects"."id" DESC
LIMIT 20;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23811/commands/76343

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #429761 (closed)

Edited by Abdul Wadood

Merge request reports