Unify subgroups and projects pagination on the backend

rossfuhrmanrequested to merge
poc-unified-items into master

What does this MR do and why?

Introduces a unified GraphQL API for querying both groups and projects as children of a parent group, enabling efficient cursor-based pagination across both resource types in a single result set.

Changelog: added
EE: true

Key Features:

  • Unified Result Set: Combines groups and projects into a single paginated response using Namespace.from_union
  • Keyset Pagination: Implements custom keyset ordering to support prev/next pagination across heterogeneous types
  • Consistent Ordering: Groups always appear before projects, with alphabetical sorting within each type
  • Authorization: Leverages existing GroupsFinder and ProjectsFinder for proper permission filtering
  • Search Support: Allows filtering children by name or path across both groups and projects
  • Flexible Filtering: Supports optional inclusion/exclusion of groups or projects via parameters

Implementation Details:

  • New Namespaces::ChildrenFinder combines group and project queries using UNION
  • Custom keyset order definition ensures stable pagination cursors
  • GraphQL union type NamespaceChildType handles both Group and Project types
  • Automatic conversion from ProjectNamespace to Project for GraphQL resolution

References

Unify subgroups and projects pagination on the ... (#578876)

Sample GraphQL Query

Request 
query GetGroupChildren {
  group(fullPath: "gitlab-org") {
    id
    name
    path
    children(first: 10, search: "shell") {
      pageInfo {
        hasNextPage
        hasPreviousPage
        startCursor
        endCursor
      }
      edges {
        cursor
        node {
          __typename
          ... on Group {
            id
            name
            fullPath
          }
          ... on Project {
            id
            name
            fullPath
            analyzerStatuses {
              analyzerType
              status
            }
          }
        }
      }
    }
  }
}
Response 
{
  "data": {
    "group": {
      "id": "gid://gitlab/Group/24",
      "name": "Gitlab Org",
      "path": "gitlab-org",
      "children": {
        "pageInfo": {
          "hasNextPage": false,
          "hasPreviousPage": false,
          "startCursor": "eyJ0eXBlX29yZGVyIjoiMSIsIm5hbWUiOiJHaXRsYWIgU2hlbGwiLCJpZCI6IjI2In0",
          "endCursor": "eyJ0eXBlX29yZGVyIjoiMSIsIm5hbWUiOiJHaXRsYWIgU2hlbGwiLCJpZCI6IjI2In0"
        },
        "edges": [
          {
            "cursor": "eyJ0eXBlX29yZGVyIjoiMSIsIm5hbWUiOiJHaXRsYWIgU2hlbGwiLCJpZCI6IjI2In0",
            "node": {
              "__typename": "Project",
              "id": "gid://gitlab/Namespaces::ProjectNamespace/26",
              "name": "Gitlab Shell",
              "fullPath": "gitlab-org/gitlab-shell",
              "analyzerStatuses": [
                {
                  "analyzerType": "CONTAINER_SCANNING",
                  "status": "NOT_CONFIGURED"
                },
                {
                  "analyzerType": "SECRET_DETECTION",
                  "status": "NOT_CONFIGURED"
                },
                {
                  "analyzerType": "SECRET_DETECTION_SECRET_PUSH_PROTECTION",
                  "status": "NOT_CONFIGURED"
                },
                {
                  "analyzerType": "CONTAINER_SCANNING_FOR_REGISTRY",
                  "status": "NOT_CONFIGURED"
                }
              ]
            }
          }
        ]
      }
    }
  }
}

Query Plans

Namespaces::ChildrenFinder#execute
SQL Query 
SELECT "namespaces"."id",
       "namespaces"."name",
       "namespaces"."path",
       "namespaces"."owner_id",
       "namespaces"."created_at",
       "namespaces"."updated_at",
       "namespaces"."type",
       "namespaces"."avatar",
       "namespaces"."membership_lock",
       "namespaces"."share_with_group_lock",
       "namespaces"."visibility_level",
       "namespaces"."request_access_enabled",
       "namespaces"."ldap_sync_status",
       "namespaces"."ldap_sync_error",
       "namespaces"."ldap_sync_last_update_at",
       "namespaces"."ldap_sync_last_successful_update_at",
       "namespaces"."ldap_sync_last_sync_at",
       "namespaces"."lfs_enabled",
       "namespaces"."parent_id",
       "namespaces"."shared_runners_minutes_limit",
       "namespaces"."repository_size_limit",
       "namespaces"."require_two_factor_authentication",
       "namespaces"."two_factor_grace_period",
       "namespaces"."project_creation_level",
       "namespaces"."runners_token",
       "namespaces"."file_template_project_id",
       "namespaces"."saml_discovery_token",
       "namespaces"."runners_token_encrypted",
       "namespaces"."custom_project_templates_group_id",
       "namespaces"."auto_devops_enabled",
       "namespaces"."extra_shared_runners_minutes_limit",
       "namespaces"."last_ci_minutes_notification_at",
       "namespaces"."last_ci_minutes_usage_notification_level",
       "namespaces"."subgroup_creation_level",
       "namespaces"."max_pages_size",
       "namespaces"."max_artifacts_size",
       "namespaces"."mentions_disabled",
       "namespaces"."default_branch_protection",
       "namespaces"."max_personal_access_token_lifetime",
       "namespaces"."push_rule_id",
       "namespaces"."shared_runners_enabled",
       "namespaces"."allow_descendants_override_disabled_shared_runners",
       "namespaces"."traversal_ids",
       "namespaces"."organization_id",
       "namespaces"."state",
       CASE
           WHEN TYPE = 'Group' THEN 0
           ELSE 1
       END AS type_order
FROM (
        (SELECT "namespaces"."id",
                "namespaces"."name",
                "namespaces"."path",
                "namespaces"."owner_id",
                "namespaces"."created_at",
                "namespaces"."updated_at",
                "namespaces"."type",
                "namespaces"."avatar",
                "namespaces"."membership_lock",
                "namespaces"."share_with_group_lock",
                "namespaces"."visibility_level",
                "namespaces"."request_access_enabled",
                "namespaces"."ldap_sync_status",
                "namespaces"."ldap_sync_error",
                "namespaces"."ldap_sync_last_update_at",
                "namespaces"."ldap_sync_last_successful_update_at",
                "namespaces"."ldap_sync_last_sync_at",
                "namespaces"."lfs_enabled",
                "namespaces"."parent_id",
                "namespaces"."shared_runners_minutes_limit",
                "namespaces"."repository_size_limit",
                "namespaces"."require_two_factor_authentication",
                "namespaces"."two_factor_grace_period",
                "namespaces"."project_creation_level",
                "namespaces"."runners_token",
                "namespaces"."file_template_project_id",
                "namespaces"."saml_discovery_token",
                "namespaces"."runners_token_encrypted",
                "namespaces"."custom_project_templates_group_id",
                "namespaces"."auto_devops_enabled",
                "namespaces"."extra_shared_runners_minutes_limit",
                "namespaces"."last_ci_minutes_notification_at",
                "namespaces"."last_ci_minutes_usage_notification_level",
                "namespaces"."subgroup_creation_level",
                "namespaces"."max_pages_size",
                "namespaces"."max_artifacts_size",
                "namespaces"."mentions_disabled",
                "namespaces"."default_branch_protection",
                "namespaces"."max_personal_access_token_lifetime",
                "namespaces"."push_rule_id",
                "namespaces"."shared_runners_enabled",
                "namespaces"."allow_descendants_override_disabled_shared_runners",
                "namespaces"."traversal_ids",
                "namespaces"."organization_id",
                "namespaces"."state"
         FROM "namespaces"
         WHERE "namespaces"."type" = 'Group'
           AND "namespaces"."parent_id" = 9970
           AND NOT (EXISTS
                      (SELECT 1
                       FROM "namespace_settings"
                       WHERE "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids)
                         AND "namespace_settings"."archived" = TRUE))
           AND "namespaces"."type" != 'Project'
           AND ("namespaces"."path" ILIKE '%shell%'
                OR "namespaces"."name" ILIKE '%shell%')
         LIMIT 21) UNION ALL 
        (SELECT "namespaces"."id",
                "namespaces"."name",
                "namespaces"."path",
                "namespaces"."owner_id",
                "namespaces"."created_at",
                "namespaces"."updated_at",
                "namespaces"."type",
                "namespaces"."avatar",
                "namespaces"."membership_lock",
                "namespaces"."share_with_group_lock",
                "namespaces"."visibility_level",
                "namespaces"."request_access_enabled",
                "namespaces"."ldap_sync_status",
                "namespaces"."ldap_sync_error",
                "namespaces"."ldap_sync_last_update_at",
                "namespaces"."ldap_sync_last_successful_update_at",
                "namespaces"."ldap_sync_last_sync_at",
                "namespaces"."lfs_enabled",
                "namespaces"."parent_id",
                "namespaces"."shared_runners_minutes_limit",
                "namespaces"."repository_size_limit",
                "namespaces"."require_two_factor_authentication",
                "namespaces"."two_factor_grace_period",
                "namespaces"."project_creation_level",
                "namespaces"."runners_token",
                "namespaces"."file_template_project_id",
                "namespaces"."saml_discovery_token",
                "namespaces"."runners_token_encrypted",
                "namespaces"."custom_project_templates_group_id",
                "namespaces"."auto_devops_enabled",
                "namespaces"."extra_shared_runners_minutes_limit",
                "namespaces"."last_ci_minutes_notification_at",
                "namespaces"."last_ci_minutes_usage_notification_level",
                "namespaces"."subgroup_creation_level",
                "namespaces"."max_pages_size",
                "namespaces"."max_artifacts_size",
                "namespaces"."mentions_disabled",
                "namespaces"."default_branch_protection",
                "namespaces"."max_personal_access_token_lifetime",
                "namespaces"."push_rule_id",
                "namespaces"."shared_runners_enabled",
                "namespaces"."allow_descendants_override_disabled_shared_runners",
                "namespaces"."traversal_ids",
                "namespaces"."organization_id",
                "namespaces"."state"
         FROM "namespaces"
         INNER JOIN
           (SELECT "projects"."project_namespace_id"
            FROM "projects"
            LEFT JOIN project_authorizations ON project_authorizations.project_id = projects.id
            AND project_authorizations.user_id = 1
            WHERE "projects"."namespace_id" = 9970
              AND "projects"."hidden" = FALSE
              AND "projects"."pending_delete" = FALSE
              AND "projects"."archived" = FALSE
              AND (projects.name LIKE 'shell%')
              AND (project_authorizations.user_id IS NOT NULL
                   OR projects.visibility_level IN (0,
                                                    10,
                                                    20))
            LIMIT 21) filtered_projects  ON filtered_projects.project_namespace_id = namespaces.id
         WHERE "namespaces"."type" = 'Project')) namespaces
ORDER BY CASE
             WHEN TYPE = 'Group' THEN 0
             ELSE 1
         END ASC, "namespaces"."name" ASC,
                  "namespaces"."id" ASC;
Execution Plan

See full query plan analysis: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45506/commands/139452

 Sort  (cost=95.02..95.02 rows=2 width=377) (actual time=0.779..0.781 rows=0 loops=1)
   Sort Key: (CASE WHEN ((namespaces_1.type)::text = 'Group'::text) THEN 0 ELSE 1 END), namespaces_1.name, namespaces_1.id
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=420
   I/O Timings: read=0.000 write=0.000
   ->  Result  (cost=0.72..95.01 rows=2 width=377) (actual time=0.756..0.758 rows=0 loops=1)
         Buffers: shared hit=414
         I/O Timings: read=0.000 write=0.000
         ->  Append  (cost=0.72..94.98 rows=2 width=373) (actual time=0.756..0.757 rows=0 loops=1)
               Buffers: shared hit=414
               I/O Timings: read=0.000 write=0.000
               ->  Limit  (cost=0.72..40.49 rows=1 width=373) (actual time=0.281..0.282 rows=0 loops=1)
                     Buffers: shared hit=115
                     I/O Timings: read=0.000 write=0.000
                     ->  Nested Loop Anti Join  (cost=0.72..40.49 rows=1 width=373) (actual time=0.280..0.281 rows=0 loops=1)
                           Buffers: shared hit=115
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_groups_on_parent_id_id on public.namespaces namespaces_1  (cost=0.43..7.96 rows=1 width=373) (actual time=0.279..0.280 rows=0 loops=1)
                                 Index Cond: (namespaces_1.parent_id = 9970)
                                 Filter: (((namespaces_1.path)::text ~~* '%shell%'::text) OR ((namespaces_1.name)::text ~~* '%shell%'::text))
                                 Rows Removed by Filter: 112
                                 Buffers: shared hit=115
                                 I/O Timings: read=0.000 write=0.000
                           ->  Index Only Scan using index_namespace_settings_on_namespace_id_where_archived_true on public.namespace_settings  (cost=0.28..16.41 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                 Index Cond: (namespace_settings.namespace_id = ANY (namespaces_1.traversal_ids))
                                 Heap Fetches: 0
                                 I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=1.71..54.48 rows=1 width=373) (actual time=0.474..0.474 rows=0 loops=1)
                     Buffers: shared hit=299
                     I/O Timings: read=0.000 write=0.000
                     ->  Limit  (cost=1.14..50.89 rows=1 width=8) (actual time=0.473..0.474 rows=0 loops=1)
                           Buffers: shared hit=299
                           I/O Timings: read=0.000 write=0.000
                           ->  Nested Loop Left Join  (cost=1.14..50.89 rows=1 width=8) (actual time=0.473..0.473 rows=0 loops=1)
                                 Filter: ((project_authorizations.user_id IS NOT NULL) OR (projects.visibility_level = ANY ('{0,10,20}'::integer[])))
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=299
                                 I/O Timings: read=0.000 write=0.000
                                 ->  Index Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.56..47.28 rows=1 width=16) (actual time=0.472..0.473 rows=0 loops=1)
                                       Index Cond: (projects.namespace_id = 9970)
                                       Filter: ((NOT projects.hidden) AND (NOT projects.pending_delete) AND (NOT projects.archived) AND ((projects.name)::text ~~ 'shell%'::text))
                                       Rows Removed by Filter: 294
                                       Buffers: shared hit=299
                                       I/O Timings: read=0.000 write=0.000
                                 ->  Index Only Scan using project_authorizations_pkey on public.project_authorizations  (cost=0.58..3.60 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
                                       Index Cond: ((project_authorizations.user_id = 1) AND (project_authorizations.project_id = projects.id))
                                       Heap Fetches: 0
                                       I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_namespaces_on_type_and_id on public.namespaces namespaces_2  (cost=0.57..3.59 rows=1 width=373) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (((namespaces_2.type)::text = 'Project'::text) AND (namespaces_2.id = projects.project_namespace_id))
                           I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB'

How to set up and validate locally

  1. Start your GDK environment
  2. Open GraphiQL at http://gdk.test:3000/-/graphql-explorer
  3. Run the sample query above (replace "gitlab-org" with a group path from your local instance)
  4. Verify the response includes both groups and projects as children
  5. Test pagination by adjusting the first parameter and using endCursor for subsequent pages
  6. Test search functionality by adding a search parameter
  7. Test filtering by using includeGroups: false or includeProjects: false

Example variations:

# Only groups
children(first: 10, includeProjects: false) { ... }

# Only projects  
children(first: 10, includeGroups: false) { ... }

# With search
children(first: 10, search: "test") { ... }

# Pagination
children(first: 10, after: "eyJ0eXBlX29yZGVyIjoiMSIsIm5hbWUiOiJHaXRsYWIgU2hlbGwiLCJpZCI6IjI2In0") { ... }

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Nicolae Rotaru

Merge request reports