Prioritize exact user matches when searching in users API
What does this MR do and why?
Previously if the search
parameter were specified, User#search
would sort by exact matches of name, username, and e-mails first.
However, in the GET /api/v4/users
endpoint, the API helper would
drop all that work on the floor and reorder the list by descending ID
values. That happened because while the sort parameters of order_by
and sort
are optional, they receive default values from Grape of
id
and desc
. As a result, the reorder_users
would always perform
the reorder, even if no sort parameters were explicitly provided.
To fix this issue, we ignore the sort parameters if the search
parameter is used.
Relates to #215499 (closed)
SQL queries
Before
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"."role",
"users"."user_type",
"users"."static_object_token_encrypted",
"users"."otp_secret_expires_at",
"users"."onboarding_in_progress"
FROM "users"
WHERE "users"."state" NOT IN ('blocked',
'banned',
'ldap_blocked')
AND ("users"."public_email" = 'a-user'
OR "users"."name" ILIKE '%a-user%'
OR "users"."username" ILIKE '%a-user%')
ORDER BY "users"."id" DESC
LIMIT 20
OFFSET 0
Query plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/23626/commands/75938
After
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"."role",
"users"."user_type",
"users"."static_object_token_encrypted",
"users"."otp_secret_expires_at",
"users"."onboarding_in_progress"
FROM "users"
WHERE "users"."state" NOT IN ('blocked',
'banned',
'ldap_blocked')
AND ("users"."public_email" = 'a-user'
OR "users"."name" ILIKE '%a-user%'
OR "users"."username" ILIKE '%a-user%')
ORDER BY CASE
WHEN LOWER(users.name) = 'a-user' THEN 0
WHEN LOWER(users.username) = 'a-user' THEN 1
WHEN LOWER(users.public_email) = 'a-user' THEN 2
ELSE 3
END ASC, "users"."name" ASC,
"users"."id" ASC
LIMIT 20
OFFSET 0
Query plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/23626/commands/75939
How to set up and validate locally
- Create two users that have a similar prefix in the username, such as
a-user
anda-user2
. - In
master
, attempt to invitea-user
to a project. - Observe that
a-user2
will come up first. Laterid
values get priority:
- With this branch,
a-user
should come up first:
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.