[Bug]: Duplicate users in search

[L-e-x-o-n]

Describe the Bug

Admin user search can return duplicate users for specific conditions search terms (described in detail below).

From index/2 inTeiserverWeb.Admin.UserController

    users =
      Account.list_users(
        search: [
          basic_search: Map.get(params, "s", "") |> String.trim()
        ],
        order_by: "Newest first",
        limit: 50
      )

    # Sometimes we get a lot of matches so it can be good to put in place exact matches too
    exact_match =
      if Enum.count(users) > 20 && params["s"] do
        Account.list_users(search: [name_lower: params["s"]])
      else
        []
      end

    users = (exact_match ++ users) |> Enum.reject(&(&1 == nil))
    user_stats = for user <- users, do: Account.get_user_stat_data(user.id)

Basic search is returning a list of users whose name or email contains the search term. Exact search returns a list of users whose names are an exact match to the search term (case insensitive).

In the final user list, exact match search is added first so that people can find users faster if they have their exact name (and there are more than 20 results from basic search).

Reproduce the bug

To reproduce this bug the search term must be an exact (case insensitive) match to an user name while also contained in more than 20 other user names or emails.

Screenshots

Additional context

Exact search is helpful because it shows the user you are most likely searching for (by knowing their exact name) first, a fix should remove the duplicate user while keeping the exact matched user at the top.

[NortySpock]

Would it make sense to push this down to a single SQL query, and ask the database to give you a sorted list? Naively, the way it's written, it appears the database would have to serve two requests, rather than one.

Sorry, SQL is my mother tongue, so that's what I reach for first.