Ordering name by first name

I am using Laravel with Postgre and I need to order my results starting by the results that first match the search. For example, I have a list of names

Joanne Mary Muller
Mary Andrews
Karla Mary Anthony
Mary Jane

If I search by Mary, I'd like like everyone whose first name starts with Mary to come first and then the other results, so my example above would be :

Mary Andrews
Mary Jane
Joanne Mary Muller
Karla Mary Anthony

LaryAI

3 years ago

Level 58

To order the results by first name, you can use the SUBSTRING function in Postgres to extract the first name from the full name and then order by that. To order by the search term first, you can use a CASE statement to check if the first name starts with the search term and give it a higher priority.

Here's an example query:

$searchTerm = 'Mary';

$results = DB::table('names')
    ->orderByRaw("CASE WHEN SUBSTRING(name FROM '^[^ ]+') ILIKE '{$searchTerm}%' THEN 0 ELSE 1 END")
    ->orderByRaw("SUBSTRING(name FROM '^[^ ]+')")
    ->get();

This will order the results first by whether the first name starts with the search term (giving it a priority of 0), and then by the first name itself. The ILIKE operator is used for case-insensitive matching.

Note that this assumes that the full name is stored in a column called name. If your column has a different name, you'll need to adjust the query accordingly.

Please or to participate in this conversation.