How to Search for Multiple Keywords Across Multiple Columns with Relevance-Based Ordering in Laravel?

Laravel: Searching Multiple Keywords Against Multiple Columns with Relevance-Based Ordering

In Laravel, implementing a search that considers multiple keywords against multiple columns while maintaining relevance can be challenging. This article addresses such a problem, where the search results are ordered based on the frequency of the keywords' occurrence in the specified columns.

Scenario:

The search bar requires three keywords as input, and the search criteria is:

1. Rows containing all three keywords in both columns (meta_name and meta_description) come first.
2. Rows containing only the first two keywords in the mentioned columns come second.
3. Rows containing only the first keyword in the mentioned columns come third.

Database Query:

To achieve the desired result, the database query should combine multiple OR conditions and utilize the whereNotIn function to exclude rows that have already been fetched in previous conditions.

<code class="php">$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'));</code>

Union and Ordering:

Finally, the $all, $twoWords, and $oneWord results are merged using the union function to obtain the ordered search results.

<code class="php">$posts = $all->union($twoWords)->union($oneWord)->get(); // check this first

# or

$posts = $all->union($twoWords)->union($oneWord)->skip($start)->take($this->rowperpage)->get();</code>

This approach ensures that the search results are ordered as per the specified criteria, starting with rows containing all three keywords in the specified columns.

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