Skip to content

Add a GraphQL query to get organization groups

Abdul Wadood requested to merge 409311-graphql-organization-group-dashboard into master

What does this MR do and why?

Add a GraphQL query to get organization groups that support multiple sort options.

Everything added here is experimental therefore I have marked all the fields/arguments as alpha.

Query plan

  1. Plan https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21169/commands/69079
Sample raw query 
EXPLAIN
SELECT
    "namespaces".*
FROM (( WITH "direct_groups" AS MATERIALIZED (
            SELECT
            "namespaces".*
            FROM (
            (
            SELECT
            "namespaces".*
            FROM
            "namespaces"
            INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
            WHERE
            "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "namespaces"."type" = 'Group'
            AND "members"."user_id" = 10327656
            AND "members"."requested_at" IS NULL
            AND (
            access_level >= 10
            )
            )
            UNION (
            SELECT
            "namespaces".*
            FROM
            "namespaces"
            WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."id" IN (
            SELECT
            "projects"."namespace_id"
            FROM
            "projects"
            INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
            WHERE
            "project_authorizations"."user_id" = 10327656
            )
            )
            ) namespaces
            WHERE
            "namespaces"."type" = 'Group'
    )
    SELECT
    "namespaces".*
    FROM ((
    SELECT
    "namespaces".*
    FROM
    "direct_groups" "namespaces"
    WHERE
    "namespaces"."type" = 'Group')
    UNION (
    SELECT
    "namespaces".*
    FROM
    "namespaces"
    INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
    WHERE
    "namespaces"."type" = 'Group'
    AND "group_group_links"."shared_with_group_id" IN (
    SELECT
    "namespaces"."id"
    FROM
    "direct_groups" "namespaces"
    WHERE
    "namespaces"."type" = 'Group'))) namespaces
    WHERE
    "namespaces"."type" = 'Group')
UNION (
    SELECT
        "namespaces".*
    FROM
        "namespaces"
            INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
    WHERE
            "members"."type" = 'GroupMember'
      AND "members"."source_type" = 'Namespace'
      AND "namespaces"."type" = 'Group'
      AND "members"."user_id" = 10327656
      AND "members"."access_level" = 5)) namespaces
        WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."organization_id" = 1
        ORDER BY
            "namespaces"."name" ASC,
            "namespaces"."id" DESC
        LIMIT 101;
  1. Plan https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21169/commands/69078
Sample raw query with search param 
EXPLAIN
SELECT
    "namespaces".*
FROM (( WITH "direct_groups" AS MATERIALIZED (
            SELECT
            "namespaces".*
            FROM (
            (
            SELECT
            "namespaces".*
            FROM
            "namespaces"
            INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
            WHERE
            "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "namespaces"."type" = 'Group'
            AND "members"."user_id" = 10327656
            AND "members"."requested_at" IS NULL
            AND (
            access_level >= 10
            )
            )
            UNION (
            SELECT
            "namespaces".*
            FROM
            "namespaces"
            WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."id" IN (
            SELECT
            "projects"."namespace_id"
            FROM
            "projects"
            INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
            WHERE
            "project_authorizations"."user_id" = 10327656
            )
            )
            ) namespaces
            WHERE
            "namespaces"."type" = 'Group'
    )
    SELECT
    "namespaces".*
    FROM ((
    SELECT
    "namespaces".*
    FROM
    "direct_groups" "namespaces"
    WHERE
    "namespaces"."type" = 'Group')
    UNION (
    SELECT
    "namespaces".*
    FROM
    "namespaces"
    INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
    WHERE
    "namespaces"."type" = 'Group'
    AND "group_group_links"."shared_with_group_id" IN (
    SELECT
    "namespaces"."id"
    FROM
    "direct_groups" "namespaces"
    WHERE
    "namespaces"."type" = 'Group'))) namespaces
    WHERE
    "namespaces"."type" = 'Group')
UNION (
    SELECT
        "namespaces".*
    FROM
        "namespaces"
            INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
    WHERE
            "members"."type" = 'GroupMember'
      AND "members"."source_type" = 'Namespace'
      AND "namespaces"."type" = 'Group'
      AND "members"."user_id" = 10327656
      AND "members"."access_level" = 5)) namespaces
        WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."organization_id" = 1
            AND "namespaces"."type" != 'Project'
            AND ("namespaces"."path" ILIKE '%git%'
                OR "namespaces"."name" ILIKE '%git%')
    ORDER BY
        "namespaces"."name" ASC,
        "namespaces"."id" DESC
    LIMIT 101;
  1. Sort by id: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21331/commands/69439
  2. Sort by name: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21169/commands/69079
  3. Sort by path: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21331/commands/69440
  4. Sort by updated_at: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21331/commands/69441
  5. Sort by created_at: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21331/commands/69442
Sample query with sort param 
-- Order by path
SELECT
    "namespaces".*
FROM (( WITH "direct_groups" AS MATERIALIZED (
    SELECT
        "namespaces".*
    FROM (
             (
                 SELECT
                     "namespaces".*
                 FROM
                     "namespaces"
                         INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                 WHERE
                         "members"."type" = 'GroupMember'
                   AND "members"."source_type" = 'Namespace'
                   AND "namespaces"."type" = 'Group'
                   AND "members"."user_id" = 10327656
                   AND "members"."requested_at" IS NULL
                   AND (
                         access_level >= 10
                     )
             )
             UNION (
                 SELECT
                     "namespaces".*
                 FROM
                     "namespaces"
                 WHERE
                         "namespaces"."type" = 'Group'
                   AND "namespaces"."id" IN (
                     SELECT
                         "projects"."namespace_id"
                     FROM
                         "projects"
                             INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                     WHERE
                             "project_authorizations"."user_id" = 10327656
                 )
             )
         ) namespaces
    WHERE
            "namespaces"."type" = 'Group'
)
        SELECT
            "namespaces".*
        FROM ((
                  SELECT
                      "namespaces".*
                  FROM
                      "direct_groups" "namespaces"
                  WHERE
                          "namespaces"."type" = 'Group')
              UNION (
                  SELECT
                      "namespaces".*
                  FROM
                      "namespaces"
                          INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
                  WHERE
                          "namespaces"."type" = 'Group'
                    AND "group_group_links"."shared_with_group_id" IN (
                      SELECT
                          "namespaces"."id"
                      FROM
                          "direct_groups" "namespaces"
                      WHERE
                              "namespaces"."type" = 'Group'))) namespaces
        WHERE
                "namespaces"."type" = 'Group')
      UNION (
          SELECT
              "namespaces".*
          FROM
              "namespaces"
                  INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
          WHERE
                  "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "namespaces"."type" = 'Group'
            AND "members"."user_id" = 10327656
            AND "members"."access_level" = 5)) namespaces
WHERE
        "namespaces"."type" = 'Group'
  AND "namespaces"."organization_id" = 1
ORDER BY
    "namespaces"."path" ASC,
    "namespaces"."id" DESC
LIMIT 101;

How to set up and validate locally

  1. Update the organization_id for a group: Group.last.update(organization_id: 1)
  2. Get the organization and it's associated groups by using the following query on https://gdk.test:3000/-/graphql-explorer:
{
  organization(id: "gid://gitlab/Organizations::Organization/1") {
    id
    path
    groups(sort: NAME_DESC) {
      edges {
        node {
          id
          name
          fullName
          fullPath
        }
      }
    }
  }
}

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #409311 (closed)

Edited by Abdul Wadood

Merge request reports