Fix `Users#solo_owned_organizations` returning incorrect results when user owns multiple organizations
What does this MR do and why?
Fixes a bug with Users#solo_owned_organizations
returning an empty result when the user is the sole owner of multiple organizations.
Explanation
WITH "ownerships" AS (SELECT "organization_users".* FROM "organization_users" WHERE "organization_users"."user_id" = 1 AND "organization_users"."access_level" = 50)
SELECT "organizations".*
FROM "organizations"
INNER JOIN ownerships ON ownerships.organization_id = organizations.id
WHERE (EXISTS (SELECT 1
FROM "organization_users"
INNER JOIN "ownerships" ON ownerships.organization_id = organization_users.organization_id
WHERE "organization_users"."access_level" = 50
HAVING (count(organization_users.user_id) = 1)))
This is the query generated by the current method. The bug comes from the organization_users
subquery which tries to "count the owners of the organizations owned by the user, and filter them to 1". Though the problem comes from:
- The HAVING clause count(organization_users.user_id) = 1 was being applied globally across all organizations, rather than per organization. It counts how many organizations is owned by the user, instead of which organization is solo-owned by the user.
-
Solution: Add a
group by
clause
-
Solution: Add a
- There's no WHERE clause linking organizations.id to organization_users.organization_id in the subquery. This missing correlation means the WHERE EXISTS filter isn't being applied correctly.
-
Solution: Add a
organization_user.organization_id = organization.id
filter in the subquery. Remove the innerjoin with ownerships.
-
Solution: Add a
Updated Query (w/ Plan)
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32887/commands/101357
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32928/commands/101545
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
How to set up and validate locally
- Create multiple organizations and assign the user as the sole owner
user = User.first
Organizations::Organization.create!(name: 'foo', path: 'foo').add_owner(user)
Organizations::Organization.create!(name: 'bar', path: 'bar').add_owner(user)
- Call
#solo_owned_organizations
to get the created organizations.
user.solo_owned_organizations.pluck(:name)
# Should return ["foo", "bar", ...]
Related to #500240 (closed)
Edited by Shane Maglangit