How to implement a search function in Laravel that prioritizes relevance when searching across multiple columns with multiple keywords?

Laravel: Search Using Multiple Keywords Across Multiple Columns with Relevance-Ordered Results

In Laravel, implementing a search function that finds data based on multiple keywords in multiple columns can be challenging, especially when the order of relevance is involved. This comprehensive guide addresses this problem in detail, providing a solution that follows a structured approach.

Database Structure

The database table involves two columns: meta_name and meta_description, where the search keywords will be matched.

Search Criteria

The search has specific criteria, prioritizing results based on the presence of keywords in both columns. Rows containing all three keywords get the highest priority, followed by rows with only the first two keywords, and lastly, rows with just the first keyword.

Pagination

The search results will be loaded dynamically using pagination, with new results being appended when the user scrolls to the bottom of the page.

Source Code

The following code snippet retrieves the search results in the desired order:

<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>

Final Query

To combine the results, we use the unionAll method:

<code class="php">$posts = $all->unionAll($twoWords)->unionAll($oneWord)->get(); // check this first
# or
$posts = $all->unionAll($twoWords)->unionAll($oneWord)->skip($start)->take($this->rowperpage)->get();</code>

This code fetches the results in the specified order, prioritizing relevance. The skip() and take() methods are used for pagination, where $start represents the number of results already displayed. Finally, the get() method retrieves the records.

The above is the detailed content of How to implement a search function in Laravel that prioritizes relevance when searching across multiple columns with multiple keywords?. For more information, please follow other related articles on the PHP Chinese website!