'How to bring the exact match first and then the partial match after a search

I have the following query which brings results after searching in a search box. It is working fine, except that it will not bring back the more specific results first. For example, if I search "Learn Laravel", it will bring first the results including "learn" only and "laravel" only, and after that it will bring the result that has both. I'm trying to let the query bring first the exact result, and then bring results that include one of the two words.

$searchValues2 = preg_split('/\s+/', $query, -1, PREG_SPLIT_NO_EMPTY); 
                  $posts = DB::table('gl as g')  
                    ->join('subcat as a', 'a.id', '=' , 'g.subcat')
                    ->join('steps as s', 's.goal_id','=', 'g.id')
                    ->join('users as u', 'g.user_id', '=' , 'u.id')
                    ->join('country as c', 'c.id', '=' , 'g.country')

                     ->where(function ($q2) use ($searchValues2) {
                      foreach ($searchValues2 as $value2) {
                        $q2->orWhere(DB::raw("g.title"), 'LIKE', "%{$value2}%");
                        $q2->orWhere(DB::raw("s.step"), 'LIKE', "%{$value2}%");
                      }
                    })

                    ->where('g.draft_flag', '!=', 1) 
                 ->orderby('g.created_at', 'desc') 
                 ->take(40)
                 ->groupby('g.id')
                 ->select('g.title as title', 'a.subcat as categ', 'g.id as id', 'u.id as u_id', 
                 'u.first_name as f_name', 'u.last_name as l_name', 'a.subcat as subcat','g.tlike as 
                  tlike','u.avatar as avatar','c.country as country')
                  ->get();
phpmysqllaravel


Solution 1:[1]

You can't do it with LIKE syntax. There is some tricks that can be used to give score to LIKE syntax but in your case I think they will not work. You may try to sort the collection after depending on matches count, but better approach will be to use FULL TEXT SEARCH.

First you should add full text search index.

ALTER TABLE gl ADD FULLTEXT title_fulltext (title)

Then: ->select(['....', DB::raw('MATCH (title) AGAINST (\''.$query.'\')')]) ->whereRaw('MATCH (title) AGAINST (?)' , array($query))

Solution 2:[2]

I added the full text search to the table I want to search through. Instructions here:

https://laravel-news.com/laravel-8-79-0

To get the full text search and then a like search, I this by doing two searches with a rank and then a union with order by rank.

$query1=Product::query();
$query1->whereFulltext(['name'],request()->search,['language'=>'english'])  
    ->Live()
    ->select('*')
    ->addSelect(DB::raw('1 as rank'));


$query2=Product::query();
$query2
    ->where('name', 'ilike', '%'.request()->search.'%')
    ->orWhere('description', 'ilike', '%'.request()->search.'%')
    ->Live()
    ->select('*')
    ->addSelect(DB::raw('2 as rank'));

products=$query1->union($query2)
                    ->orderBy('rank','asc')
                    ->limit(10)
                    ->get();

$products->unique('id')->values(); //to get rid of duplicates`

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 katsarov
Solution 2

Related Questions