Skip to content

Add language filter to projects GraphQL query

Peter Hegman requested to merge add-filters-to-projects-graphql-query into master

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 language argument to the 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

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 "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
    INNER JOIN "repository_languages" ON "repository_languages"."project_id" = "projects"."id"
WHERE
    "project_authorizations"."user_id" = 5413811
    AND "projects"."pending_delete" = FALSE
    AND "projects"."archived" = FALSE
    AND "repository_languages"."programming_language_id" IN (
        SELECT
            "programming_languages"."id"
        FROM
            "programming_languages"
        WHERE ("programming_languages"."name" ILIKE 'ruby'))
AND "projects"."hidden" = FALSE
ORDER BY
    "projects"."id" DESC
LIMIT 101

Query plan

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31533/commands/97795

Click to expand 
 Limit  (cost=15624.23..15624.27 rows=17 width=854) (actual time=16380.238..16380.292 rows=101 loops=1)
   Buffers: shared hit=103889 read=18240 dirtied=2110
   I/O Timings: read=15897.995 write=0.000
   ->  Sort  (cost=15624.23..15624.27 rows=17 width=854) (actual time=16380.235..16380.281 rows=101 loops=1)
         Sort Key: projects.id DESC
         Sort Method: top-N heapsort  Memory: 91kB
         Buffers: shared hit=103889 read=18240 dirtied=2110
         I/O Timings: read=15897.995 write=0.000
         ->  Nested Loop  (cost=29.46..15623.88 rows=17 width=854) (actual time=84.823..16368.924 rows=1323 loops=1)
               Buffers: shared hit=103886 read=18240 dirtied=2110
               I/O Timings: read=15897.995 write=0.000
               ->  Hash Join  (cost=28.89..15598.18 rows=40 width=8) (actual time=80.931..12886.077 rows=1550 loops=1)
                     Hash Cond: (repository_languages.programming_language_id = programming_languages.id)
                     Buffers: shared hit=99350 read=14994 dirtied=1989
                     I/O Timings: read=12491.776 write=0.000
                     ->  Nested Loop  (cost=1.14..15504.71 rows=24867 width=12) (actual time=14.554..12863.769 rows=14610 loops=1)
                           Buffers: shared hit=99350 read=14989 dirtied=1989
                           I/O Timings: read=12488.345 write=0.000
                           ->  Index Only Scan Backward using project_authorizations_pkey on public.project_authorizations  (cost=0.58..1321.44 rows=6586 width=4) (actual time=10.769..3060.432 rows=23465 loops=1)
                                 Index Cond: (project_authorizations.user_id = 5413811)
                                 Heap Fetches: 2311
                                 Buffers: shared hit=12015 read=2438 dirtied=1587
                                 I/O Timings: read=2975.509 write=0.000
                           ->  Index Only Scan using repository_languages_pkey on public.repository_languages  (cost=0.56..2.11 rows=4 width=8) (actual time=0.403..0.416 rows=1 loops=23465)
                                 Index Cond: (repository_languages.project_id = project_authorizations.project_id)
                                 Heap Fetches: 667
                                 Buffers: shared hit=87335 read=12551 dirtied=402
                                 I/O Timings: read=9512.836 write=0.000
                     ->  Hash  (cost=27.74..27.74 rows=1 width=4) (actual time=4.041..4.042 rows=1 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                           Buffers: shared read=5
                           I/O Timings: read=3.431 write=0.000
                           ->  Seq Scan on public.programming_languages  (cost=0.00..27.74 rows=1 width=4) (actual time=1.452..4.033 rows=1 loops=1)
                                 Filter: ((programming_languages.name)::text ~~* 'ruby'::text)
                                 Rows Removed by Filter: 618
                                 Buffers: shared read=5
                                 I/O Timings: read=3.431 write=0.000
               ->  Index Scan using projects_pkey on public.projects  (cost=0.56..0.63 rows=1 width=854) (actual time=2.237..2.237 rows=1 loops=1550)
                     Index Cond: (projects.id = repository_languages.project_id)
                     Filter: ((NOT projects.pending_delete) AND (NOT projects.archived) AND (NOT projects.hidden))
                     Rows Removed by Filter: 0
                     Buffers: shared hit=4513 read=3246 dirtied=98
                     I/O Timings: read=3406.219 write=0.000

How to set up and validate locally

  1. Go to http://gdk.test:3000/-/graphql-explorer
  2. Run the following
query getProjects {
  projects(membership: true, language: "ruby") {
    nodes {
      name
    }
  }
}
Edited by Peter Hegman

Merge request reports