Add search argument to contributed projects GraphQL query
What does this MR do and why?
Needed for &13066 (closed) where we are moving Your work -> Projects to Vue and fetching the data with GraphQL. We need to provide search functionality in the UI on the Contributed tab so we are adding a search argument to the contributed projects GraphQL query.
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Database
Search string over 3 characters
Raw SQL
Using my user ID and gitlab-ui as a search string
Click to expand
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
"projects"."id" IN ( SELECT DISTINCT
"events"."project_id"
FROM
"events"
WHERE (action IN (5, 6)
OR (target_type IN ('MergeRequest', 'Issue', 'WorkItem')
AND action IN (1, 3, 7, 12)))
AND "events"."author_id" = 5413811
AND "events"."created_at" >= '2023-10-03 03:48:28.702026')
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5413811
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0, 10, 20))
AND (("projects"."path" ILIKE '%gitlab-ui%'
OR "projects"."name" ILIKE '%gitlab-ui%')
OR "projects"."description" ILIKE '%gitlab-ui%')
AND "projects"."namespace_id" != 5413811
ORDER BY
"projects"."last_activity_at" DESC,
"projects"."id" DESC
LIMIT 101Query plan
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32238/commands/99624
Click to expand
Limit (cost=6559.63..6559.64 rows=1 width=827) (actual time=803.821..803.826 rows=2 loops=1)
Buffers: shared hit=2016 read=1027 dirtied=108
I/O Timings: read=786.386 write=0.000
-> Sort (cost=6559.63..6559.64 rows=1 width=827) (actual time=803.819..803.822 rows=2 loops=1)
Sort Key: projects.last_activity_at DESC, projects.id DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=2016 read=1027 dirtied=108
I/O Timings: read=786.386 write=0.000
-> Nested Loop (cost=1.27..6559.62 rows=1 width=827) (actual time=651.021..803.762 rows=2 loops=1)
Buffers: shared hit=2010 read=1027 dirtied=108
I/O Timings: read=786.386 write=0.000
-> Unique (cost=0.70..152.78 rows=890 width=4) (actual time=8.158..596.814 rows=48 loops=1)
Buffers: shared hit=1903 read=870 dirtied=93
I/O Timings: read=581.628 write=0.000
-> Index Only Scan using index_events_author_id_project_id_action_target_type_created_at on public.events (cost=0.70..150.56 rows=891 width=4) (actual time=8.156..596.034 rows=4851 loops=1)
Index Cond: ((events.author_id = 5413811) AND (events.created_at >= '2023-10-03 03:48:28.702026+00'::timestamp with time zone))
Heap Fetches: 190
Filter: ((events.action = ANY ('{5,6}'::integer[])) OR (((events.target_type)::text = ANY ('{MergeRequest,Issue,WorkItem}'::text[])) AND (events.action = ANY ('{1,3,7,12}'::integer[]))))
Rows Removed by Filter: 23
Buffers: shared hit=1903 read=870 dirtied=93
I/O Timings: read=581.628 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.56..7.19 rows=1 width=827) (actual time=4.307..4.307 rows=0 loops=48)
Index Cond: (projects.id = events.project_id)
Filter: ((projects.marked_for_deletion_at IS NULL) AND (NOT projects.pending_delete) AND (projects.namespace_id <> 5413811) AND (((projects.path)::text ~~* '%gitlab-ui%'::text) OR ((projects.name)::text ~~* '%gitlab-ui%'::text) OR (projects.description ~~* '%gitlab-ui%'::text)) AND ((SubPlan 1) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 1
Buffers: shared hit=107 read=157 dirtied=15
I/O Timings: read=204.758 write=0.000
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.58..3.60 rows=1 width=0) (actual time=4.103..4.104 rows=1 loops=2)
Index Cond: ((project_authorizations.user_id = 5413811) AND (project_authorizations.project_id = projects.id))
Heap Fetches: 0
Buffers: shared hit=3 read=7
I/O Timings: read=8.088 write=0.000Search string under 3 characters
Using my user ID and fo as a search string. Also including personal projects since I created a personal project on GitLab.com since there were no projects with names less than 3 characters.
Raw SQL
Click to expand
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
"projects"."id" IN ( SELECT DISTINCT
"events"."project_id"
FROM
"events"
WHERE (action IN (5, 6)
OR (target_type IN ('MergeRequest', 'Issue', 'WorkItem')
AND action IN (1, 3, 7, 12)))
AND "events"."author_id" = 5413811
AND "events"."created_at" >= '2023-10-03 15:32:51.060740')
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND (EXISTS (
SELECT
1
FROM
"project_authorizations"
WHERE
"project_authorizations"."user_id" = 5413811
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (0, 10, 20))
AND (("projects"."path" ILIKE 'fo'
OR "projects"."name" ILIKE 'fo')
OR "projects"."description" ILIKE 'fo')
ORDER BY
"projects"."last_activity_at" DESC,
"projects"."id" DESC
LIMIT 101Query plan
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32238/commands/99674
Click to expand
Limit (cost=6550.16..6550.17 rows=1 width=827) (actual time=4.818..4.820 rows=0 loops=1)
Buffers: shared hit=3013
I/O Timings: read=0.000 write=0.000
-> Sort (cost=6550.16..6550.17 rows=1 width=827) (actual time=4.816..4.818 rows=0 loops=1)
Sort Key: projects.last_activity_at DESC, projects.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3013
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.27..6550.15 rows=1 width=827) (actual time=4.773..4.774 rows=0 loops=1)
Buffers: shared hit=3007
I/O Timings: read=0.000 write=0.000
-> Unique (cost=0.70..152.74 rows=889 width=4) (actual time=0.058..4.210 rows=48 loops=1)
Buffers: shared hit=2772
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_events_author_id_project_id_action_target_type_created_at on public.events (cost=0.70..150.51 rows=890 width=4) (actual time=0.057..3.914 rows=4850 loops=1)
Index Cond: ((events.author_id = 5413811) AND (events.created_at >= '2023-10-03 15:32:51.06074+00'::timestamp with time zone))
Heap Fetches: 190
Filter: ((events.action = ANY ('{5,6}'::integer[])) OR (((events.target_type)::text = ANY ('{MergeRequest,Issue,WorkItem}'::text[])) AND (events.action = ANY ('{1,3,7,12}'::integer[]))))
Rows Removed by Filter: 23
Buffers: shared hit=2772
I/O Timings: read=0.000 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.56..7.19 rows=1 width=827) (actual time=0.011..0.011 rows=0 loops=48)
Index Cond: (projects.id = events.project_id)
Filter: ((projects.marked_for_deletion_at IS NULL) AND (NOT projects.pending_delete) AND (((projects.path)::text ~~* 'fo'::text) OR ((projects.name)::text ~~* 'fo'::text) OR (projects.description ~~* 'fo'::text)) AND ((SubPlan 1) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 1
Buffers: shared hit=235
I/O Timings: read=0.000 write=0.000
SubPlan 1
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.58..3.60 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: ((project_authorizations.user_id = 5413811) AND (project_authorizations.project_id = projects.id))
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000How to set up and validate locally
- Go to a project and comment on an issue or push a commit
- Go to http://gdk.test:3000/-/graphql-explorer
- Run the following and make note of a project's name
query getProjects {
currentUser {
contributedProjects {
nodes {
nameWithNamespace
}
}
}
}- Now add the search argument with the project name
query getProjects {
currentUser {
contributedProjects(search: "flight") {
nodes {
nameWithNamespace
}
}
}
}Edited by Peter Hegman