Enable user searching by partial email for admins
requested to merge gitlab-community/gitlab:20381-enable-user-searching-by-partial-email-for-admins into master
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
- Go to Admin -> Users
- Fill in search field with partially matching email address
- 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)
Edited by Zakir Dzhamaliddinov