Count users in nested projects on Gitlab.com

What does this MR do?

Count users in nested projects on Gitlab.com

Summary

It appears that the GitLab.com Seats currently in use count on the parent group billing page is no longer counting nested project membership.

Steps to reproduce

1. Create a group
2. Create a subgroup nested within the group
3. Add a new member to the subgroup
4. Check the billing page on the parent group and observe that there are correctly 2 Seats currently in use
5. Create a project (within the parent group or subgroup)
6. Add a new member to the project
7. Check the billing page on the parent group and observe that there are incorrectly 2 Seats currently in use

Please look into this MR for reference !22046 (closed)

The following 4 queries are made to fetch the user_ids in an array and then merged using the ruby pipe and then counting the unique array.

Get direct group member's user_id

SELECT DISTINCT
    "members"."user_id"
FROM
    "members"
    LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "users"."state" = 'active'
    AND "members"."requested_at" IS NULL
    AND "members"."source_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = 9970)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
        SELECT
            "namespaces"."id"
        FROM
            "base_and_descendants" AS "namespaces")
    AND (members.access_level > 10)

And the corresponding EXPLAIN statement is https://explain.depesz.com/s/My75

Get project members

SELECT DISTINCT
    "members"."user_id"
FROM
    "members"
    LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE
    "members"."type" = 'ProjectMember'
    AND "members"."source_type" = 'Project'
    AND "users"."state" = 'active'
    AND "members"."requested_at" IS NULL
    AND "members"."source_id" IN (
        SELECT
            "projects"."id"
        FROM
            "projects"
            INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
                AND "namespaces"."type" = 'Group'
        WHERE
            "projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                        SELECT
                            "namespaces".*
                        FROM
                            "namespaces"
                        WHERE
                            "namespaces"."type" = 'Group'
                            AND "namespaces"."id" = 9970)
                    UNION (
                        SELECT
                            "namespaces".*
                        FROM
                            "namespaces",
                            "base_and_descendants"
                        WHERE
                            "namespaces"."type" = 'Group'
                            AND "namespaces"."parent_id" = "base_and_descendants"."id"))
                SELECT
                    id
                FROM
                    "base_and_descendants" AS "namespaces"))
        AND (members.access_level > 10)

And the corresponding EXPLAIN statement is https://explain.depesz.com/s/MWG5

Get shared group members

SELECT DISTINCT
    "members"."user_id"
FROM
    "members"
    LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "users"."state" = 'active'
    AND "members"."requested_at" IS NULL
    AND "members"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                    INNER JOIN "group_group_links" ON "namespaces"."id" = "group_group_links"."shared_group_id"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "group_group_links"."shared_with_group_id" = 9970)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_ancestors"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
        SELECT
            "namespaces"."id"
        FROM
            "base_and_ancestors" AS "namespaces")
    AND (members.access_level > 10)

And the corresponding EXPLAIN statement is https://explain.depesz.com/s/SX6h

Get invited group members to project

SELECT DISTINCT
    "members"."user_id"
FROM
    "members"
    LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "users"."state" = 'active'
    AND "members"."requested_at" IS NULL
    AND "members"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                    INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "project_group_links"."project_id" IN (
                        SELECT
                            "projects"."id"
                        FROM
                            "projects"
                            INNER JOIN routes rs ON rs.source_id = projects.id
                                AND rs.source_type = 'Project'
                        WHERE (rs.path LIKE 'gitlab-org/%')))
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_ancestors"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
        SELECT
            "namespaces"."id"
        FROM
            "base_and_ancestors" AS "namespaces")
    AND (members.access_level > 10)

And the corresponding EXPLAIN statement is https://explain.depesz.com/s/adP1

Conformity

• Changelog entry
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team

Closes #33142 (closed)