Skip to content

Prioritize exact user matches when searching in users API

Stan Hu requested to merge sh-fix-users-search-sorting into master

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

  1. Create two users that have a similar prefix in the username, such as a-user and a-user2.
  2. In master, attempt to invite a-user to a project.
  3. Observe that a-user2 will come up first. Later id values get priority:

image

  1. With this branch, a-user should come up first:

image

MR acceptance checklist

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

Edited by Stan Hu

Merge request reports