How to Implement a Relevance-Ordered Search Across Multiple Columns in Laravel?

Laravel: Search with Multiple Keywords Against Multiple Columns, Ordered by Relevance

Problem:

Implement a search in Laravel that retrieves data from multiple columns based on three input keywords. The search results should be ordered by relevance, with rows containing all three keywords ranked highest, followed by rows containing only the first two keywords, and finally rows containing only the first keyword.

Solution:

To find rows in the desired order, we start by querying for posts matching all three keywords in either meta_name or meta_description columns. This query goes into the $all variable.

Next, we query for posts matching only the first two keywords in the same columns. This query goes into the $twoWords variable, and we exclude the IDs of posts already found in $all.

Finally, we query for posts matching only the first keyword in either column, again excluding IDs found in $all and $twoWords. This query goes into the $oneWord variable.

To get the final ordered search results, we union all three query results ($all, $twoWords, $oneWord) and assign it to the $posts variable.

The code for retrieving the search results is as follows:

$word1 = 'word1';
$word2 = 'word2';
$word3 = 'word3';

$all = DB::table('posts')
    ->where('meta_name', 'like', "%{$word1}%")
    ->where('meta_name', 'like', "%{$word2}%")
    ->where('meta_name', 'like', "%{$word3}%")
    ->orWhere(function($query) use ($word1, $word2, $word3) {
        $query->where('meta_description', 'like', "%{$word1}%")
              ->where('meta_description', 'like', "%{$word2}%")
              ->where('meta_description', 'like', "%{$word3}%");
    });

$twoWords = DB::table('posts')
    ->where('meta_name', 'like', "%{$word1}%")
    ->where('meta_name', 'like', "%{$word2}%")
    ->orWhere(function($query) use ($word1, $word2) {
        $query->where('meta_description', 'like', "%{$word1}%")
              ->where('meta_description', 'like', "%{$word2}%");
    })
    ->whereNotIn('id', $all->pluck('id'));

$oneWord = DB::table('posts')
    ->where('meta_name', 'like', "%{$word1}%")
    ->orWhere('meta_description', 'like', "%{$word1}%")
    ->whereNotIn('id', $all->pluck('id'))
    ->whereNotIn('id', $twoWords->pluck('id'));

$posts = $all->union($twoWords)->union($oneWord)->get();

The search results are now ordered as follows:

1. Rows containing all three keywords in meta_name or meta_description
2. Rows containing only the first two keywords in meta_name or meta_description
3. Rows containing only the first keyword in meta_name or meta_description

The above is the detailed content of How to Implement a Relevance-Ordered Search Across Multiple Columns in Laravel?. For more information, please follow other related articles on the PHP Chinese website!