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

New SQL queries

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

Raw SQL

SELECT "users".* FROM "users" WHERE ("users"."public_email" = 'search' 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=4.27..4.27 rows=1 width=2435) (actual time=0.147..0.148 rows=0 loops=1)
   Sort Key: (CASE WHEN (lower((public_email)::text) = 'search'::text) THEN 0 WHEN (lower((username)::text) = 'search'::text) THEN 1 WHEN (lower((name)::text) = 'search'::text) THEN 2 ELSE 3 END), name, id
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on users  (cost=0.00..4.26 rows=1 width=2435) (actual time=0.126..0.127 rows=0 loops=1)
         Filter: (((public_email)::text = 'search'::text) OR ((name)::text ~~* '%search%'::text) OR ((username)::text ~~* '%search%'::text))
         Rows Removed by Filter: 71
 Planning Time: 3.048 ms
 Execution Time: 0.217 ms
(8 rows)
(13 rows)

User.search("search", with_private_emails: true, partial_email_query: 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)