Enable user searching by partial email for admins

What does this MR do and why?

This MR enables searching users by partial email match for admin accounts

Screenshots or screen recordings

Before	After

How to set up and validate locally

1. Go to Admin -> Users
2. Fill in search field with partially matching email address
3. Search for users

Users with partially matching emails should be found

Related to #20381 (closed)

New SQL queries

User.search("search", with_private_emails: true)

Raw SQL

SELECT "users".* FROM "users" WHERE ("users"."email" = 'search' OR "users"."id" = (SELECT "emails"."user_id" FROM "emails" WHERE "emails"."email" = 'search' AND "emails"."confirmed_at" IS NOT NULL LIMIT 1) OR "users"."name" ILIKE '%search%' OR "users"."username" ILIKE '%search%') ORDER BY CASE
  WHEN LOWER(users.public_email) = 'search' THEN 0
  WHEN LOWER(users.username) = 'search' THEN 1
  WHEN LOWER(users.name) = 'search' THEN 2
  ELSE 3
END
 ASC, "users"."name" ASC, "users"."id" ASC

Query plan

 Sort  (cost=6.31..6.31 rows=2 width=2435) (actual time=0.537..0.538 rows=0 loops=1)
   Sort Key: (CASE WHEN (lower((users.public_email)::text) = 'search'::text) THEN 0 WHEN (lower((users.username)::text) = 'search'::text) THEN 1 WHEN (lower((users.name)::text) = 'search'::text) THEN 2 ELSE 3 END), users.name, users.id
   Sort Method: quicksort  Memory: 25kB
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..1.85 rows=1 width=4) (actual time=0.051..0.051 rows=0 loops=1)
           ->  Seq Scan on emails  (cost=0.00..1.85 rows=1 width=4) (actual time=0.048..0.049 rows=0 loops=1)
                 Filter: ((confirmed_at IS NOT NULL) AND ((email)::text = 'search'::text))
                 Rows Removed by Filter: 81
   ->  Seq Scan on users  (cost=0.00..4.45 rows=2 width=2435) (actual time=0.506..0.506 rows=0 loops=1)
         Filter: (((email)::text = 'search'::text) OR (id = $0) OR ((name)::text ~~* '%search%'::text) OR ((username)::text ~~* '%search%'::text))
         Rows Removed by Filter: 71
 Planning Time: 5.458 ms
 Execution Time: 0.683 ms
(13 rows)

added typefeature label

Hey @zzaakiirr!

Thank you for your contribution to GitLab. Please refer to the contribution documentation for an overview of the process.

When you're ready for a first review, post @gitlab-bot ready. If you know a relevant reviewer(s) (for example, someone that was involved in a related issue), you can also assign them directly with @gitlab-bot ready @user1 @user2.

At any time, if you need help, feel free to post @gitlab-bot help or initiate a mentor session on Discord. Read more on how to get help.

You can comment @gitlab-bot label <label1> <label2> to add labels to your MR. Please see the list of allowed labels in the label command documentation.

This message was generated automatically. You're welcome to improve it.

added Community contribution workflowin dev labels

assigned to @zzaakiirr

@csouthard @kniechajewicz @s_awezec this merge request touches files that could potentially affect user growth or subscription cost management.

This message was generated automatically. You're welcome to improve it.

added linked-issue label

	5 Warnings
	bd0934a4: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
	be39b7cd: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	19f5d82c: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	This merge request has more than 20 commits which may cause issues in some of the jobs. If you see errors like missing commits, please consider squashing some commits so it is within 20 commits.
	Labels missing: please ask a reviewer or maintainer to add databasereview pending, database to this merge request.

	1 Message
	This merge request adds or changes files that require a review from the Database team.

This merge request requires a database review. To make sure these changes are reviewed, take the following steps:

1. Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
2. Prepare your MR for database review according to the docs.
3. Assign and mention the database reviewer suggested by Reviewer Roulette.

The following files require a review from the Database team:

• app/finders/users_finder.rb

Reviewer roulette

Category	Reviewer	Maintainer
backend	@arpitgogia (UTC+5.5)	@aakriti.gupta (UTC+2)
database	@bhrai (UTC+2)	@a_akgun (UTC+3)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

@gitlab-bot backend

added 1 commit

• 2ba2b98f - Enable user searching by partial email for admins

Compare with previous version

@gitlab-bot ready

added workflowready for review label and removed workflowin dev label

Hi Coach @jrushford, this Community contribution is ready for review or needs your coaching.

• Do you have capacity and domain expertise to review this? If not, find one or more reviewers and assign to them.
• If you've reviewed it, add the workflowin dev label if these changes need more work before the next review.

This message was generated automatically. You're welcome to improve it.

requested review from @jrushford

mentioned in issue gitlab-org/quality/triage-reports#16803 (closed)

changed milestone to %Backlog

added Category:Organization Category:User Management Seeking community contributions admin dashboard backend customer customer+ devopsdata stores grouptenant scale sectioncore platform workflowready for development labels and removed workflowready for review label

removed Seeking community contributions label

removed workflowready for development label

removed customer+ label

removed customer label

mentioned in issue #20381 (closed)

changed milestone to %16.11

added idle label

mentioned in issue gitlab-org/quality/triage-reports#17264 (closed)

changed milestone to %17.0

added workflowready for review label

@jrushford, this Community contribution is ready for review.

• Do you have capacity and domain expertise to review this? If not, find one or more reviewers and assign to them.
• If you've reviewed it, add the workflowin dev label if these changes need more work before the next review.

This message was generated automatically. You're welcome to improve it.

removed idle label

requested review from @thutterer and @mhamda and removed review request for @jrushford

removed review request for @mhamda

requested review from @tigerwnz and removed review request for @thutterer

changed the description

added 5712 commits

• 2ba2b98f...5d00d195 - 5710 commits from branch gitlab-org:master
• 3f98e949 - Merge branch 'master' of https://gitlab.com/gitlab-community/gitlab into...
• bd0934a4 - Review fixes

Compare with previous version

approved this merge request

added pipeline:mr-approved label

@mhamda, thanks for approving this merge request.

This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break master, please start a new pipeline before merging.

Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.

@zzaakiirr Could you also check whether we need to update the documentation? If you are implementing this for the API as well, we will also need to update the API docs.

/cc @lciutacu

mentioned in merge request gitlab-com/www-gitlab-com!134143 (merged)

added 1 commit

• 09774697 - Update documentation

Compare with previous version

	41 Warnings
	This MR has a Changelog commit for EE, but no code changes in ee/. Consider removing the EE: true trailer from your commits.
	1fbe742b: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
	6d415596: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
	dfaa9c6c: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	4811a6bb: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	09774697: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
	3e0d9d8a: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	74813123: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	ef49728c: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	34ef10ad: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	6ea79991: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	6c2c0ecf: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	b345a89c: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	ea11fbab: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	4de00aee: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	0e330c12: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	5e09abc8: The commit subject may not be longer than 72 characters. For more information, take a look at our Commit message guidelines.
	5e09abc8: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	004738b9: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	17c326fb: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	167548d2: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	4785df3d: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	5331bef7: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	bd0934a4: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
	6b14a094: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	bb368753: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	03803b2b: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	01355b58: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	47bae85c: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	42bc2f27: The commit subject must contain at least 3 words. For more information, take a look at our Commit message guidelines.
	42bc2f27: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	a74d6f62: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	cd6acb5c: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	b83bc3c6: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	b7f163ae: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	c13eb5b8: The commit body should not contain more than 72 characters per line. For more information, take a look at our Commit message guidelines.
	be39b7cd: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	6ce3562d: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	216f57e8: Commits that change 30 or more lines across at least 3 files should describe these changes in the commit body. For more information, take a look at our Commit message guidelines.
	This merge request has more than 20 commits which may cause issues in some of the jobs. If you see errors like missing commits, please consider squashing some commits so it is within 20 commits.
	Labels missing: please ask a reviewer or maintainer to add documentation to this merge request.

	1 Message
	This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge.

Documentation review

The following files require a review from a technical writer:

• doc/administration/admin_area.md (Link to current live version)

The review does not need to block merging this merge request. See the:

• Metadata for the *.md files that you've changed. The first few lines of each *.md file identify the stage and group most closely associated with your docs change.
• The Technical Writer assigned for that stage and group.
• Documentation workflows for information on when to assign a merge request for review.

Reviewer roulette

Category	Reviewer	Maintainer
backend	@atiwari71 (UTC+5.5)	@cablett (UTC+12)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

added 2184 commits

• 09774697...4f28f9c1 - 2183 commits from branch gitlab-org:master
• b16007fd - Merge branch 'master' of https://gitlab.com/gitlab-community/gitlab into...

Compare with previous version

reset approvals from @mhamda by pushing to the branch

mentioned in merge request !152249 (merged)

added groupauthentication label and removed grouptenant scale label

removed pipeline:mr-approved label

added pipelinetier-1 label

@zzaakiirr @tigerwnz I am moving this MR across to groupauthentication, who own the user entity. groupauthentication are taking over the related issue as well. We discussed that we definitely cannot add these indexes on Gitlab.com due to performance concerns (Saturation of lock_manager lwlocks (gitlab-com/gl-infra&1129)), so I wanted to check with you @zzaakiirr whether you would be open to adapting this MR and make it only available to self-managed? This should be achievable as we have existing examples of indexes for self-managed instances only, Tiger can probably point you to the right place for that. Let me know what you think. Please loop in @hsutor, the PM for groupauthentication, in case you have any questions.

/cc @adil.farrukh

removed sectioncore platform label

removed devopsdata stores label