Adjustments to add on eligible users API to return filtered results
What does this MR do and why?
Resolves Adjust eligible users list API to return filter... (#488507 - closed)
This MR implements ability to filter add on eligible users list based on the assigned seats for Gitlab Duo/Enterprise. The frontend functionality for this feature has been shipped through !166317 (merged) and this MR ties the whole functionality together.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Database
Self-managed:
User selection query (filter_by_assigned_seat: true
)
filter_by_assigned_seat: true
)Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99214
SELECT
"users".*
FROM
"users"
WHERE ("users"."state" IN ('active'))
AND "users"."user_type" IN (0, 6, 4, 13)
AND "users"."user_type" IN (0, 4, 5, 15, 17)
AND "users"."user_type" IN (0, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
AND "users"."id" IN (
SELECT
"subscription_user_add_on_assignments"."user_id"
FROM
"subscription_user_add_on_assignments"
INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
WHERE
"subscription_add_on_purchases"."id" = 15
AND (started_at IS NULL
OR started_at <= '2024-10-01')
AND ('2024-10-01' < expires_on))
User selection query(filter_by_assigned_seat: false
)
filter_by_assigned_seat: false
)Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99217
SELECT
"users".*
FROM
"users"
WHERE ("users"."state" IN ('active'))
AND "users"."user_type" IN (0, 6, 4, 13)
AND "users"."user_type" IN (0, 4, 5, 15, 17)
AND "users"."user_type" IN (0, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
AND "users"."id" NOT IN (
SELECT
"subscription_user_add_on_assignments"."user_id"
FROM
"subscription_user_add_on_assignments"
INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
WHERE
"subscription_add_on_purchases"."id" = 16
AND (started_at IS NULL
OR started_at <= '2024-10-01')
AND ('2024-10-01' < expires_on))
SaaS:
User selection query (filter_by_assigned_seat: true
)
filter_by_assigned_seat: true
)Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99208
SELECT
"users".*
FROM
"users"
WHERE
"users"."id" IN ( WITH "our_group_namespaces" AS (
SELECT
"namespaces"."id"
FROM
UNNEST(COALESCE((
SELECT
ids
FROM (
SELECT
"namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM "namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
SELECT
ids
FROM (
SELECT
ARRAY_AGG("namespaces"."id") AS ids
FROM (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces (id)),
"base_ancestors_cte" AS (
SELECT
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND ("namespaces"."id" IN (
SELECT
"group_group_links"."shared_with_group_id"
FROM
group_group_links
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
id
FROM
our_group_namespaces)
UNION
SELECT
"project_group_links"."group_id"
FROM
project_group_links
WHERE
"project_group_links"."project_id" IN (
SELECT
id
FROM
projects
WHERE
"projects"."namespace_id" IN (
SELECT
id
FROM
"our_group_namespaces"))))),
"our_namespace_bans" AS (
SELECT
"namespace_bans"."user_id"
FROM
"namespace_bans"
WHERE
"namespace_bans"."namespace_id" = 9970
)
SELECT
"members"."user_id"
FROM ((
SELECT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'ProjectMember'
AND "members"."source_type" = 'Project'
AND ("users"."state" IN ('active'))
AND "users"."user_type" IN (0, 4, 5, 15, 17)
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."user_id" NOT IN (
SELECT
"user_id"
FROM
"our_namespace_bans")
AND "members"."source_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"id"
FROM
"our_group_namespaces"))
/* allow_cross_joins_across_databases */)
UNION (
SELECT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND ("users"."state" IN ('active'))
AND "users"."user_type" IN (0, 4, 5, 15, 17)
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."user_id" NOT IN (
SELECT
"user_id"
FROM
"our_namespace_bans")
AND ("members"."source_id" IN (
SELECT
unnest("base_ancestors_cte"."traversal_ids")
FROM
base_ancestors_cte
UNION
SELECT
id
FROM
our_group_namespaces))
/* allow_cross_joins_across_databases */)) members)
AND "users"."id" IN (
SELECT
"subscription_user_add_on_assignments"."user_id"
FROM
"subscription_user_add_on_assignments"
INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
WHERE
"subscription_add_on_purchases"."id" = 13
AND (started_at IS NULL
OR started_at <= '2024-10-01')
AND ('2024-10-01' < expires_on))
/* allow_cross_joins_across_databases */
User selection query (filter_by_assigned_seat: false
)
filter_by_assigned_seat: false
)Explanation: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32126/commands/99222
SELECT
"users".*
FROM
"users"
WHERE
"users"."id" IN ( WITH "our_group_namespaces" AS (
SELECT
"namespaces"."id"
FROM
UNNEST(COALESCE((
SELECT
ids
FROM (
SELECT
"namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM "namespace_descendants"
WHERE
"namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
SELECT
ids
FROM (
SELECT
ARRAY_AGG("namespaces"."id") AS ids
FROM (
SELECT
namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces (id)),
"base_ancestors_cte" AS (
SELECT
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND ("namespaces"."id" IN (
SELECT
"group_group_links"."shared_with_group_id"
FROM
group_group_links
WHERE
"group_group_links"."shared_group_id" IN (
SELECT
id
FROM
our_group_namespaces)
UNION
SELECT
"project_group_links"."group_id"
FROM
project_group_links
WHERE
"project_group_links"."project_id" IN (
SELECT
id
FROM
projects
WHERE
"projects"."namespace_id" IN (
SELECT
id
FROM
"our_group_namespaces"))))),
"our_namespace_bans" AS (
SELECT
"namespace_bans"."user_id"
FROM
"namespace_bans"
WHERE
"namespace_bans"."namespace_id" = 9970
)
SELECT
"members"."user_id"
FROM ((
SELECT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'ProjectMember'
AND "members"."source_type" = 'Project'
AND ("users"."state" IN ('active'))
AND "users"."user_type" IN (0, 4, 5, 15, 17)
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."user_id" NOT IN (
SELECT
"user_id"
FROM
"our_namespace_bans")
AND "members"."source_id" IN (
SELECT
"projects"."id"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"id"
FROM
"our_group_namespaces"))
/* allow_cross_joins_across_databases */)
UNION (
SELECT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND ("users"."state" IN ('active'))
AND "users"."user_type" IN (0, 4, 5, 15, 17)
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND "members"."user_id" NOT IN (
SELECT
"user_id"
FROM
"our_namespace_bans")
AND ("members"."source_id" IN (
SELECT
unnest("base_ancestors_cte"."traversal_ids")
FROM
base_ancestors_cte
UNION
SELECT
id
FROM
our_group_namespaces))
/* allow_cross_joins_across_databases */)) members)
AND "users"."id" NOT IN (
SELECT
"subscription_user_add_on_assignments"."user_id"
FROM
"subscription_user_add_on_assignments"
INNER JOIN "subscription_add_on_purchases" ON "subscription_add_on_purchases"."id" = "subscription_user_add_on_assignments"."add_on_purchase_id"
WHERE
"subscription_add_on_purchases"."id" = 14
AND (started_at IS NULL
OR started_at <= '2024-10-01')
AND ('2024-10-01' < expires_on))
/* allow_cross_joins_across_databases */
GraphQL query
query getAddOnEligibleUsers($fullPath: ID!, $addOnType: GitlabSubscriptionsAddOnType!, $addOnPurchaseIds: [GitlabSubscriptionsAddOnPurchaseID!]!, $sort: GitlabSubscriptionsUserSort!, $filterByAssignedSeat: String, $first: Int, $last: Int, $after: String, $before: String) {
namespace(fullPath: $fullPath) {
id
addOnEligibleUsers(
addOnType: $addOnType
addOnPurchaseIds: $addOnPurchaseIds
sort: $sort
first: $first
last: $last
after: $after
before: $before
filterByAssignedSeat: $filterByAssignedSeat
) {
nodes {
id
username
name
publicEmail
avatarUrl
webUrl
lastActivityOn
addOnAssignments(addOnPurchaseIds: $addOnPurchaseIds) {
nodes {
addOnPurchase {
name
}
}
}
}
}
}
}
# Variables
{
"fullPath": "twitter",
"addOnType": "CODE_SUGGESTIONS",
"addOnPurchaseIds": [
"gid://gitlab/GitlabSubscriptions::AddOnPurchase/11"
],
"sort": "ID_ASC",
"first": 20,
"last": null,
"after": null,
"before": null,
"filterByAssignedSeat": "true"
}
How to set up and validate locally
- Needs both
gdk
and CustomersDot running forSaaS/SM
(setup guide) - In Rails console, enable Code Suggestions
Feature.enable(:hamilton_seat_management)
- In Rails console, enable the filtering FF
Feature.enable(:enable_add_on_users_filtering)
To assign Code Suggestions seats to your group:
SaaS:
- In Rails console,
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
- In Rails console,
GitlabSubscriptions::AddOnPurchase.create!(add_on: add_on, namespace: Namespace.find(<you-name-space-id>), expires_on: 21.month.from_now, quantity: 5, purchase_xid: 'A-S0002')
- Visit any group or project member pages such as
http://localhost:3000/groups/flightjs/-/usage_quotas#code-suggestions-usage-tab
- Click the search bar to select the
Assinged seat
filter, and running the search button should return expected results.
Self-managed:
- In Rails console,
add_on = GitlabSubscriptions::AddOn.find_or_create_by!(name: "code_suggestions") {|e| e.description = "Test"}
- In Rails console,
GitlabSubscriptions::AddOnPurchase.create!(add_on: add_on, expires_on: 21.month.from_now, quantity: 5, purchase_xid: 'A-S0002')
- Visit admin section's Duo area
http://localhost:3000/admin/code_suggestions
- Click the search bar to select the
Assinged seat
filter, and running the search button should return expected results.
Related to #488507 (closed)