Extend GroupsFinder with organization_id param
What does this MR do and why?
Extend GroupsFinder with organization_id param
As the existing GroupsFinder provides us with all the existing functionality, therefore the new Organizations::GroupsFinder is removed.
Query plans
Before with Organizations::GroupsFinder.new(organization: organization, current_user: user)
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24621/commands/78316
Raw query
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;
After with GroupsFinder.new(current_user: user, { organization: organization })
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24621/commands/78310
Raw query
SELECT "namespaces".*
FROM ((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)
UNION
(WITH "descendants_base_cte" AS MATERIALIZED (SELECT "namespaces"."id", "namespaces"."traversal_ids"
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)),
"superset" AS (SELECT d1.traversal_ids
FROM descendants_base_cte d1
WHERE NOT EXISTS (SELECT 1
FROM descendants_base_cte d2
WHERE d2.id = ANY (d1.traversal_ids)
AND d2.id <> d1.id))
SELECT DISTINCT "namespaces".*
FROM "superset",
"namespaces"
WHERE "namespaces"."type" = 'Group'
AND next_traversal_ids_sibling("superset"."traversal_ids") > "namespaces"."traversal_ids"
AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
AND "namespaces"."organization_id" = 1)) namespaces
WHERE "namespaces"."type" = 'Group'
ORDER BY LOWER("namespaces"."name") ASC, "namespaces"."id" DESC
LIMIT 101How to set up and validate locally
Follow the steps here how-to-set-up-and-validate-locally.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #431669 (closed)
Edited by Doug Stull