Refactor member role query to consider hierarchy
What does this MR do and why?
Refactor member role query to consider hierarchy
This change updates the query to get the users from member role to consider the hierarchy of membership.
EE: true Changelog: fixed
Database
SQL Query
SELECT
"users"."id",
"users"."email",
"users"."encrypted_password",
"users"."reset_password_token",
"users"."reset_password_sent_at",
"users"."remember_created_at",
"users"."sign_in_count",
"users"."current_sign_in_at",
"users"."last_sign_in_at",
"users"."current_sign_in_ip",
"users"."last_sign_in_ip",
"users"."created_at",
"users"."updated_at",
"users"."name",
"users"."admin",
"users"."projects_limit",
"users"."failed_attempts",
"users"."locked_at",
"users"."username",
"users"."can_create_group",
"users"."can_create_team",
"users"."state",
"users"."color_scheme_id",
"users"."password_expires_at",
"users"."created_by_id",
"users"."last_credential_check_at",
"users"."avatar",
"users"."confirmation_token",
"users"."confirmed_at",
"users"."confirmation_sent_at",
"users"."unconfirmed_email",
"users"."hide_no_ssh_key",
"users"."admin_email_unsubscribed_at",
"users"."notification_email",
"users"."hide_no_password",
"users"."password_automatically_set",
"users"."encrypted_otp_secret",
"users"."encrypted_otp_secret_iv",
"users"."encrypted_otp_secret_salt",
"users"."otp_required_for_login",
"users"."otp_backup_codes",
"users"."public_email",
"users"."dashboard",
"users"."project_view",
"users"."consumed_timestep",
"users"."layout",
"users"."hide_project_limit",
"users"."note",
"users"."unlock_token",
"users"."otp_grace_period_started_at",
"users"."external",
"users"."incoming_email_token",
"users"."auditor",
"users"."require_two_factor_authentication_from_group",
"users"."two_factor_grace_period",
"users"."last_activity_on",
"users"."notified_of_own_activity",
"users"."preferred_language",
"users"."theme_id",
"users"."accepted_term_id",
"users"."feed_token",
"users"."private_profile",
"users"."roadmap_layout",
"users"."include_private_contributions",
"users"."commit_email",
"users"."group_view",
"users"."managing_group_id",
"users"."first_name",
"users"."last_name",
"users"."static_object_token",
"users"."user_type",
"users"."static_object_token_encrypted",
"users"."otp_secret_expires_at",
"users"."onboarding_in_progress",
"users"."color_mode_id",
"users"."composite_identity_enforced",
"users"."organization_id"
FROM
"users"
INNER JOIN
"project_authorizations"
ON "users"."id" = "project_authorizations"."user_id"
INNER JOIN
(
SELECT
"members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id",
"members"."expiry_notified_at",
"members"."request_accepted_at"
FROM
(SELECT
DISTINCT
ON (user_id) member_union.id,
member_union.access_level,
member_union.source_id,
member_union.source_type,
member_union.user_id,
member_union.notification_level,
member_union.type,
member_union.created_at,
member_union.updated_at,
member_union.created_by_id,
member_union.invite_email,
member_union.invite_token,
member_union.invite_accepted_at,
member_union.requested_at,
member_union.expires_at,
member_union.ldap,
member_union.override,
member_union.state,
member_union.invite_email_success,
member_union.member_namespace_id,
member_union.member_role_id,
member_union.expiry_notified_at,
member_union.request_accepted_at
FROM
((SELECT
"members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id",
"members"."expiry_notified_at",
"members"."request_accepted_at"
FROM
"members"
WHERE
"members"."type" = 'ProjectMember'
AND "members"."source_id" = 85
AND "members"."source_type" = 'Project'
AND "members"."requested_at" IS NULL)
UNION
ALL (
SELECT
"members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id",
"members"."expiry_notified_at",
"members"."request_accepted_at"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 345
)
) AS member_union
ORDER BY
user_id,
CASE
WHEN type = 'ProjectMember' THEN 1
WHEN type = 'GroupMember' THEN 2
ELSE 3
END ) AS members) AS applicable_members
ON applicable_members.user_id = users.id
WHERE
"project_authorizations"."project_id" = 85
AND "applicable_members"."member_role_id" = 1References
Screenshots or screen recordings
| Before | After |
|---|---|
How to set up and validate locally
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #550784 (closed)
Edited by Sashi Kumar Kumaresan