Skip to content

Switch queries generated on Project Members API from polymorphic to `member_namespace_id` queries

Manoj M J requested to merge 344817-project-namespace-members-update-rest-api into master

What does this MR do and why?

For #344817 (comment 1227049218):

This MR changes the queries used to fetch project members in the following endpoints:

  • GET /projects/:id/members/
  • GET /projects/:id/members/:user_id
  • PUT /projects/:id/members/:user_id
  • DELETE /projects/:id/members/:user_id
  • GET /projects/:id/invitations
  • PUT /projects/:id/invitations/:email
  • DELETE /projects/:id/invitations/:email

such that it now uses the (members.member_namespace_id=project.project_namespace_id) relation on the members table to fetch members related to a project, rather than fetching using the polymorphic association (members.source_type=Project, members.source_id=project.id)

Previously

  • For
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" IS NOT NULL ORDER BY "members"."id" ASC LIMIT 20 OFFSET 0
  • For GET /projects/:id/members/:user_id 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = 12 LIMIT 1
  • For PUT /projects/:id/members/:user_id 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = 12 LIMIT 1
  • For DELETE /projects/:id/members/:user_id 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = 12 LIMIT 1
  • For GET /projects/:id/invitations 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NOT NULL ORDER BY "members"."id" ASC LIMIT 20 OFFSET 0
  • For PUT /projects/:id/invitations/:email 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NOT NULL AND "members"."invite_email" = 'manoj3@yopmail.com' ORDER BY "members"."id" ASC LIMIT 1
  • For DELETE /projects/:id/invitations/:email 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 2 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NOT NULL AND "members"."invite_email" = 'manoj3@yopmail.com' ORDER BY "members"."id" ASC LIMIT 1

Now

  • For /projects/:id/members/ 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."user_id" IS NOT NULL ORDER BY "members"."id" ASC LIMIT 20 OFFSET 0
  • For /projects/:id/members/:user_id 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."user_id" = 12 LIMIT 1
  • PUT /projects/:id/members/:user_id 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."user_id" = 5 LIMIT 1
  • DELETE /projects/:id/members/:user_id 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."user_id" = 5 LIMIT 1
  • GET /projects/:id/invitations 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NOT NULL ORDER BY "members"."id" ASC LIMIT 20 OFFSET 0
  • PUT /projects/:id/invitations/:email 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NOT NULL AND "members"."invite_email" = 'manoj1@yopmail.com' ORDER BY "members"."id" ASC LIMIT 1
  • For DELETE /projects/:id/invitations/:email 
SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 24 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NOT NULL AND "members"."invite_email" = 'manoj2@yopmail.com' ORDER BY "members"."id" ASC LIMIT 1

Details

  • The change is behind the feature flag project_members_index_by_project_namespace, which is same as the feature flag already introduced in !105781 (merged) for the same purpose, but in a different endpoint. It has not been enabled anywhere yet, so it is safe to put these changes also behind the same feature flag as the functionality is similar.
  • The queries generated via the new association are already tested and reviewed in !105781 (merged), so it is safe to use.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

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

Related to #344817 (closed)

Edited by Manoj M J

Merge request reports