Order by best match in eloquent

Question

I have some results which I get from the eloquent query, and I want to order them by best match. How can I do it in laravel eloquent?

here I found some solution in SQL, but I could not use it in the eloquent builder.

    SELECT TOP 5 * 
    FROM Products
    WHERE ProductCode LIKE '%200%'
    ORDER BY CHARINDEX('200', ProductCode, 1), ProductCode

https://bytutorial.com/blogs/tsql/how-to-order-the-sql-query-result-by-best-match-keyword-search

user_2867881 · Answer

Yes as suggested, you can use orderByRaw() So your query would be transformed Like:

$result = Product::where("ProductCode", "LIKE", '%200%')
                ->orderByRaw('CHARINDEX('200', ProductCode, 1) DESC, ProductCode ASC')
                ->get();

Here, it supposed, the model name is Product for table products.

user_3927361 · Answer

We can use raw query to order by best match. Its given me best match results.

DB::select(
    "SELECT *
       FROM  table_name
       WHERE column_name LIKE '%".$search_word."%' 
       ORDER BY
        CASE
        WHEN column_name LIKE '".$search_word."' THEN 1
        WHEN column_name LIKE '".$search_word."%' THEN 2
        WHEN column_name LIKE '%".$search_word."%' THEN 3
        WHEN column_name LIKE '%".$search_word."' THEN 4
        ELSE 5
    END")

The matched result will be sorted by the below order.

First Case: Exact match with search word.
Second Case: Starting with search word.
Third Case: Search word in any position.
Fourth Case: Ending with search word.
ELSE Case: Search word does not contains.

Order by best match in eloquent

2 Answers