Filter Group and Project members by Role

What does this MR do and why?

Filter Group and Project members by Role.

Issue: #431397 (closed)

Screenshots or screen recordings

Before	After

Database

There are 2 scopes introduced in this MR:

1. Member.with_static_role
2. Member.with_member_role_id

Both query the member_role_id FK-column, backed by the index_members_on_member_role_id index.

The queries where they are used are as follows:

Member.with_static_role

Rails

GroupMembersFinder.new(group, user, params: { max_role: 'static-10' }).execute

Raw SQL

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"."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"
              FROM
                "namespaces"
              WHERE
                "namespaces"."type" = 'Group'
                AND "namespaces"."id" = 9970
            )
          ) namespaces
        WHERE
          "namespaces"."type" = 'Group'
      )
    ORDER BY
      user_id,
      invite_email,
      CASE
        WHEN source_id = 9970
        and source_type = 'Namespace' THEN access_level + 1
        ELSE access_level
      END DESC,
      expires_at DESC,
      created_at ASC
  ) members
WHERE
  "members"."type" = 'GroupMember'
  AND "members"."access_level" = 10
  AND "members"."member_role_id" IS NULL

Plan: https://console.postgres.ai/shared/8682af27-4755-452c-910a-15c50bac6d18

Member.with_custom_role

Rails

GroupMembersFinder.new(group, user, params: { max_role: 'custom-10' }).execute

Raw SQL

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"."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"
              FROM
                "namespaces"
              WHERE
                "namespaces"."type" = 'Group'
                AND "namespaces"."id" = 9970
            )
          ) namespaces
        WHERE
          "namespaces"."type" = 'Group'
      )
    ORDER BY
      user_id,
      invite_email,
      CASE
        WHEN source_id = 9970
        and source_type = 'Namespace' THEN access_level + 1
        ELSE access_level
      END DESC,
      expires_at DESC,
      created_at ASC
  ) members
WHERE
  "members"."type" = 'GroupMember'
  AND "members"."member_role_id" = 10

Plan: https://console.postgres.ai/shared/5125433b-1a93-4cc0-8a1d-fd5563c8136f

How to set up and validate locally

1. Visit any group or project member pages such as http://127.0.0.1:3000/groups/flightjs/-/group_members, click in the search bar, select Role and a role to filter by, and hit enter.