Add programming language filter to contributed projects GraphQL query
What does this MR do and why?
Needed for &13066 where we are moving Your work -> Projects to Vue and fetching the data with GraphQL. We need to provide a Language filter in the UI so we are adding a programming_language_name argument to the contributed projects GraphQL query.
We added the same filter to the projects GraphQL query in !166098 (merged)
Database
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"
INNER JOIN "repository_languages" ON "repository_languages"."project_id" = "projects"."id"
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-09-24 14:38:49.227519')
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 "repository_languages"."programming_language_id" IN (
SELECT
"programming_languages"."id"
FROM
"programming_languages"
WHERE ("programming_languages"."name" ILIKE 'ruby'))
AND "projects"."namespace_id" != 7137960
ORDER BY
"projects"."last_activity_at" DESC,
"projects"."id" DESC
LIMIT 101Query plan
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31835/commands/98521
Click to expand
Limit (cost=2798.75..2798.77 rows=5 width=818) (actual time=1162.341..1162.353 rows=16 loops=1)
Buffers: shared hit=2109 read=1044 dirtied=112
I/O Timings: read=1112.347 write=0.000
-> Sort (cost=2798.75..2798.77 rows=5 width=818) (actual time=1162.338..1162.349 rows=16 loops=1)
Sort Key: projects.last_activity_at DESC, projects.id DESC
Sort Method: quicksort Memory: 43kB
Buffers: shared hit=2109 read=1044 dirtied=112
I/O Timings: read=1112.347 write=0.000
-> Nested Loop (cost=29.66..2798.69 rows=5 width=818) (actual time=44.653..1162.089 rows=16 loops=1)
Buffers: shared hit=2103 read=1044 dirtied=112
I/O Timings: read=1112.347 write=0.000
-> Hash Join (cost=29.09..2777.52 rows=5 width=8) (actual time=24.594..1014.969 rows=16 loops=1)
Hash Cond: (repository_languages.programming_language_id = programming_languages.id)
Buffers: shared hit=2008 read=974 dirtied=103
I/O Timings: read=968.886 write=0.000
-> Nested Loop (cost=1.27..2741.19 rows=3221 width=12) (actual time=14.391..1007.780 rows=120 loops=1)
Buffers: shared hit=2008 read=969 dirtied=103
I/O Timings: read=962.555 write=0.000
-> Unique (cost=0.70..144.46 rows=883 width=4) (actual time=13.606..696.533 rows=47 loops=1)
Buffers: shared hit=1842 read=796 dirtied=50
I/O Timings: read=676.542 write=0.000
-> Index Only Scan using index_events_author_id_project_id_action_target_type_created_at on public.events (cost=0.70..142.25 rows=884 width=4) (actual time=13.602..695.503 rows=4771 loops=1)
Index Cond: ((events.author_id = 5413811) AND (events.created_at >= '2023-09-24 14:38:49.227519+00'::timestamp with time zone))
Heap Fetches: 125
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=1842 read=796 dirtied=50
I/O Timings: read=676.542 write=0.000
-> Index Only Scan using repository_languages_pkey on public.repository_languages (cost=0.56..2.89 rows=4 width=8) (actual time=3.683..6.613 rows=3 loops=47)
Index Cond: (repository_languages.project_id = events.project_id)
Heap Fetches: 84
Buffers: shared hit=166 read=173 dirtied=53
I/O Timings: read=286.013 write=0.000
-> Hash (cost=27.81..27.81 rows=1 width=4) (actual time=6.751..6.753 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared read=5
I/O Timings: read=6.330 write=0.000
-> Seq Scan on public.programming_languages (cost=0.00..27.81 rows=1 width=4) (actual time=5.155..6.740 rows=1 loops=1)
Filter: ((programming_languages.name)::text ~~* 'ruby'::text)
Rows Removed by Filter: 624
Buffers: shared read=5
I/O Timings: read=6.330 write=0.000
-> Index Scan using projects_pkey on public.projects (cost=0.56..4.23 rows=1 width=818) (actual time=9.177..9.177 rows=1 loops=16)
Index Cond: (projects.id = repository_languages.project_id)
Filter: ((projects.marked_for_deletion_at IS NULL) AND (NOT projects.pending_delete) AND (projects.namespace_id <> 7137960) AND ((SubPlan 1) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[]))))
Rows Removed by Filter: 0
Buffers: shared hit=89 read=70 dirtied=3
I/O Timings: read=143.461 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=1.370..1.370 rows=1 loops=16)
Index Cond: ((project_authorizations.user_id = 5413811) AND (project_authorizations.project_id = projects.id))
Heap Fetches: 0
Buffers: shared hit=55 read=18
I/O Timings: read=21.325 write=0.000MR 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.
How to set up and validate locally
- Go to http://gdk.test:3000/-/graphql-explorer
- Run the following:
query getProjects {
currentUser {
contributedProjects(programmingLanguageName: "ruby") {
nodes {
nameWithNamespace
}
}
}
}If you don't have any contributed projects go comment on an issue in a project or push a code change to a project
Edited by Peter Hegman