Skip to content

Expand User#belongs_to_paid_namespace? namespace search

Mohamed Moustafa requested to merge mtimoustafa/416651-fetch-all-belonging-namespaces into master

What does this MR do and why?

Resolves https://gitlab.com/gitlab-org/gitlab/-/issues/416651+.

The method User#belongs_to_paid_namespace? is intended to fetch all paid namespaces a user is a non-guest member of. However, it currently only accounts for direct memberships with root namespaces. It fails to include:

  1. Membership via a sub-group
  2. Membership via a project
  3. Membership via an invite to a sub-group via another Group
  4. Membership via an invite to a project via another group

This MR changes the method's behaviour to account for all the cases above.

A list of methods impacted by this change can be found here.

Screenshots or screen recordings

No visual changes.

How to set up and validate locally

  1. Start GDK.
  2. Pick a user (user A) with a paid root namespace containing some sub-groups and projects.
  3. Register a new user (user B).
  4. Log out as user B and log back in as user A.
  5. Using the Rails console, validate that user B returns false for belongs_to_paid_namespace?: 
    user_b = User.last
user_b.belongs_to_paid_namespace?
  6. For the targets listed below:
    1. Add user B to the target as a non-guest. (Guest membership should always return false).
    2. Validate using the Rails console that belongs_to_paid_namespace? returns the expected result.
    3. Remove user B from the target, or leave them in the target to validate behaviour for multiple memberships.

Targets for belongs_to_paid_namespace? == true:

  • Paid root group.
  • A sub-group under a paid root group.
  • A project under a paid root group.
  • A group linked to another group under a paid group.
  • A project linked to another group under a paid group.

Targets for belongs_to_paid_namespace? == false:

  • Root groups without a paid subscription.
  • Any namespaces or projects under or linked to a root group without a paid subscription.

SQL Queries

Before: find top-level paid namespaces user is a non-guest member of

There was one SQL query to find the top-level paid namespaces the user is a non-guest member of. It also has a variation where namespaces with trial subscriptions are excluded.

Default mode

SQL Query 
SELECT
    1 AS one
FROM ((
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'User'
            AND "namespaces"."owner_id" = 106)
    UNION (
        SELECT
            "namespaces"."id"
        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" = 106
            AND "members"."requested_at" IS NULL
            AND (access_level >= 10)
            AND "members"."access_level" IN (20, 30, 40, 50)
            AND "namespaces"."parent_id" IS NULL)) namespaces
    LEFT OUTER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."namespace_id" = "namespaces"."id"
WHERE
    "gitlab_subscriptions"."hosted_plan_id" IN (
        SELECT
            "plans"."id"
        FROM
            "plans"
        WHERE
            "plans"."name" IN ('bronze', 'silver', 'premium', 'gold', 'ultimate', 'ultimate_trial', 'premium_trial', 'opensource'))
LIMIT 1

Query plan on GitLab using the gitlab-qa user.

Excluding trials

SQL Query 
SELECT
    1 AS one
FROM ((
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'User'
            AND "namespaces"."owner_id" = 106)
    UNION (
        SELECT
            "namespaces"."id"
        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" = 106
            AND "members"."requested_at" IS NULL
            AND (access_level >= 10)
            AND "members"."access_level" IN (20, 30, 40, 50)
            AND "namespaces"."parent_id" IS NULL)) namespaces
    LEFT OUTER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."namespace_id" = "namespaces"."id"
WHERE
    "gitlab_subscriptions"."hosted_plan_id" IN (
        SELECT
            "plans"."id"
        FROM
            "plans"
        WHERE
            "plans"."name" IN ('bronze', 'silver', 'premium', 'gold', 'ultimate', 'ultimate_trial', 'premium_trial', 'opensource'))
    AND ("gitlab_subscriptions"."trial" = FALSE
        OR "gitlab_subscriptions"."trial" IS NULL
        OR "gitlab_subscriptions"."trial_ends_on" <= '2023-09-13')
LIMIT 1

Query plan on GitLab using the gitlab-qa user.

After: find top-level paid namespaces of all namespaces and projects user is a non-guest member of

The old query is preceded by three pre-existing queries, which fetch all namespace IDs the user has a non-guest membership to. The old query has also been altered to search for the top-level paid namespaces using just the fetched namespace IDs, instead of relying on nested queries.

The three pre-existing queries are leveraged from the billable_code_suggestions_root_group_ids method in the same User model. This MR pulls them out into a new shared method and does not alter the queries.

Query 1: fetch namespace IDs from project and project-group link memberships

SQL Query 
SELECT
    "projects"."namespace_id"
FROM
    "projects"
WHERE
    "projects"."id" IN (
        SELECT
            "project_authorizations"."project_id"
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 1614863
            AND (access_level > 10))

Query plan on GitLab using the gitlab-qa user.

Query 2: fetch namespace IDs from group and sub-group memberships

SQL Query 
SELECT
    "members"."source_id"
FROM
    "members"
    LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "members"."user_id" = 1614863
    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 > 10)

Query plan on GitLab using the gitlab-qa user.

Query 3: fetch namespace IDs from group-group link memberships

SQL Query 
SELECT
    "group_group_links"."shared_group_id"
FROM
    "group_group_links"
WHERE (group_access > 10)
AND "group_group_links"."shared_with_group_id" = 9970

Query plan on GitLab using the gitlab-org namespace.

Query 4: fetch top-level paid namespaces a user is a member of

Default mode
SQL Query 
SELECT
    1 AS one
FROM
    "namespaces"
    LEFT OUTER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."namespace_id" = "namespaces"."id"
    LEFT OUTER JOIN "plans" ON "plans"."id" = "gitlab_subscriptions"."hosted_plan_id"
WHERE ("namespaces"."id" = 9970
    OR "namespaces"."type" = 'User'
    AND "namespaces"."owner_id" = 1614863)
AND "gitlab_subscriptions"."hosted_plan_id" IN (
    SELECT
        "plans"."id"
    FROM
        "plans"
    WHERE
        "plans"."name" IN ('bronze', 'silver', 'premium', 'gold', 'ultimate', 'ultimate_trial', 'premium_trial', 'opensource'))
LIMIT 1

Query plan on GitLab using the gitlab-qa user and the gitlab-org namespace.

Excluding trials
SQL Query 
SELECT
    1 AS one
FROM
    "namespaces"
    LEFT OUTER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."namespace_id" = "namespaces"."id"
    LEFT OUTER JOIN "plans" ON "plans"."id" = "gitlab_subscriptions"."hosted_plan_id"
WHERE ("namespaces"."id" = 9970
    OR "namespaces"."type" = 'User'
    AND "namespaces"."owner_id" = 1614863)
AND "gitlab_subscriptions"."hosted_plan_id" IN (
    SELECT
        "plans"."id"
    FROM
        "plans"
    WHERE
        "plans"."name" IN ('bronze', 'silver', 'premium', 'gold', 'ultimate', 'ultimate_trial', 'premium_trial', 'opensource'))
AND ("gitlab_subscriptions"."trial" = FALSE
    OR "gitlab_subscriptions"."trial" IS NULL
    OR "gitlab_subscriptions"."trial_ends_on" <= '2023-09-14')
LIMIT 1

Query plan on GitLab using the gitlab-qa user and the gitlab-org namespace.

MR acceptance checklist

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

Edited by Mohamed Moustafa

Merge request reports