How to Implement a Relevant Search in Laravel with Multiple Keywords?

Laravel Search with Multiple Keywords and Relevance Sorting

Introduction

This article addresses a common challenge in Laravel development: implementing a search function that incorporates multiple keywords against multiple database columns. The search results must be ordered based on relevance, considering the presence and order of the specified keywords.

Problem Statement

A user is attempting to implement a search engine in Laravel where multiple keywords are entered into a search bar, and the results are displayed in order of relevance. Two specific columns, meta_name and meta_description, are to be queried. The search criteria are as follows:

• Rows containing all three keywords in both columns should be prioritized.
• Rows containing the first two keywords only should be ranked second.
• Rows containing only the first keyword should be ranked third.

Solution

To achieve this ordering, three separate database queries are constructed:

$all = DB::table('posts')
    ->where('meta_name', 'like', ... /* %word1% %word2% %word3% */)
    ->orWhere('meta_description', 'like', ... /* %word1% %word2% %word3% */);

$twoWords = DB::table('posts')
    ->where('meta_name', 'like', ... /* %word1% %word2% */)
    ->orWhere('meta_description', 'like', ... /* %word1% %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'));

Union and Ordering

Finally, the three result sets are combined using union:

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

This process ensures that rows matching the specified criteria are retrieved in the desired order of relevance.

Pagination and Async Loading

To implement the load-on-scroll pagination, a skip and take method can be used to append new results as the user scrolls. When there is no more data to display, a message can be returned.

Conclusion

Using the provided solution, the search feature will retrieve relevant results based on multiple keywords and their order of appearance in the specified columns. The results will be displayed in a load-on-scroll manner, providing a seamless user experience.

The above is the detailed content of How to Implement a Relevant Search in Laravel with Multiple Keywords?. For more information, please follow other related articles on the PHP Chinese website!