Skip to content

GraphQL: Filter group members by access level

Ryan Cobb requested to merge rc/graphql_filter_by_access_levels into master

What does this MR do and why?

Issue: customers-gitlab-com#4009 (closed)
CustomersDot (client) MR: https://gitlab.com/gitlab-org/customers-gitlab-com/-/merge_requests/4479

As part of the above issue, CustomersDot needs to be able to fetch all owners of a group so we can send them billing related emails.

This MR adds the ability to filter GraphQL group members by access level.

Queries

No access_level param (before)

https://explain.depesz.com/s/MV8G

EXPLAIN SELECT
    "members".*
FROM ( SELECT DISTINCT ON (user_id, invite_email)
        *
    FROM
        "members"
    WHERE
        "members"."type" = 'GroupMember'
        AND "members"."source_type" = 'Namespace'
        AND "members"."requested_at" IS NULL
        AND "members"."source_id" IN (
            SELECT
                "namespaces"."id"
            FROM ((
                    SELECT
                        "namespaces"."id",
                        "namespaces"."name",
                        "namespaces"."path",
                        "namespaces"."owner_id",
                        "namespaces"."created_at",
                        "namespaces"."updated_at",
                        "namespaces"."type",
                        "namespaces"."description",
                        "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"."description_html",
                        "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"."cached_markdown_version",
                        "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"."emails_disabled",
                        "namespaces"."max_pages_size",
                        "namespaces"."max_artifacts_size",
                        "namespaces"."mentions_disabled",
                        "namespaces"."default_branch_protection",
                        "namespaces"."unlock_membership_to_ldap",
                        "namespaces"."max_personal_access_token_lifetime",
                        "namespaces"."push_rule_id",
                        "namespaces"."shared_runners_enabled",
                        "namespaces"."allow_descendants_override_disabled_shared_runners",
                        "namespaces"."traversal_ids"
                    FROM
                        "namespaces"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND "namespaces"."id" = 22)) namespaces
            WHERE
                "namespaces"."type" = 'Group')
        ORDER BY
            user_id,
            invite_email,
            access_level DESC,
            expires_at DESC,
            created_at ASC) members
WHERE
    "members"."type" = 'GroupMember'

With access_level param (after)

https://explain.depesz.com/s/sVpr

SELECT
    "members".*
FROM ( SELECT DISTINCT ON (user_id, invite_email)
        *
    FROM
        "members"
    WHERE
        "members"."type" = 'GroupMember'
        AND "members"."source_type" = 'Namespace'
        AND "members"."requested_at" IS NULL
        AND "members"."source_id" IN (
            SELECT
                "namespaces"."id"
            FROM ((
                    SELECT
                        "namespaces"."id",
                        "namespaces"."name",
                        "namespaces"."path",
                        "namespaces"."owner_id",
                        "namespaces"."created_at",
                        "namespaces"."updated_at",
                        "namespaces"."type",
                        "namespaces"."description",
                        "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"."description_html",
                        "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"."cached_markdown_version",
                        "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"."emails_disabled",
                        "namespaces"."max_pages_size",
                        "namespaces"."max_artifacts_size",
                        "namespaces"."mentions_disabled",
                        "namespaces"."default_branch_protection",
                        "namespaces"."unlock_membership_to_ldap",
                        "namespaces"."max_personal_access_token_lifetime",
                        "namespaces"."push_rule_id",
                        "namespaces"."shared_runners_enabled",
                        "namespaces"."allow_descendants_override_disabled_shared_runners",
                        "namespaces"."traversal_ids"
                    FROM
                        "namespaces"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND "namespaces"."id" = 22)) namespaces
            WHERE
                "namespaces"."type" = 'Group')
        ORDER BY
            user_id,
            invite_email,
            access_level DESC,
            expires_at DESC,
            created_at ASC) members
    LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
    "members"."type" = 'GroupMember'
    AND (("members"."user_id" IS NULL
            AND "members"."invite_token" IS NOT NULL)
        OR "users"."state" = 'active')
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
AND "members"."access_level" IN (50, 40)
Edited by Ryan Cobb

Merge request reports