Laravel - search with multiple keywords against multiple columns with the search result to be ordered in relevance

Question

I am trying to implement a search in Laravel. it will have a search bar where 3 keywords will be given as input. There are 2 columns namely meta_name and meta_description in the database against which the search will be made. Suppose the search words are word1 word2 word3. Order of the search words matters. The rule is: rows that have both the 3 words in the 2 mentioned columns i.e., meta_name and meta_description will be shown in the first place. Then rows containing only word1 word2 (note that I mean only the first 2 words) in the mentioned columns will come in the second place. After that rows containing only word1 in the mentioned 2 columns will come in the 3rd place. When I say rows contain words such asword1 word2, it means word1 and word2 can be present in any or both of the 2 columns i.e., meta_name and meta_description. If a row is already shown , then it will not reappear in search results.

The search results will be shown in a load-on-scroll manner just like Facebook. It means whenever we go to the bottom of the page by scrolling downward, new results will be loaded i.e., appended to the previous results. When there is no data, a message will be shown that no more data is available .

To accomplish the purpose, I am following a tutorial from here. They have the source code in Github here.

The technique is, on page scroll, it is calculated whether the bottom of the page is reached. If so, then an ajax call is made which sends how many results have already been showed. Then in the controller (You will find it in getPosts method of the PostsController in the source code), the following code is executed :

$start = $request->get('start');//how many results have already been shown

$records = Post::select('*')
                ->skip($start)
                ->take($this->rowperpage) // $this->rowperpage= 4 e.g.
                ->get();

and the corresponding results are sent as ajax response to be appended to the already shown search results.

In my case, the challenge remains in finding the database rows in the appropriate order. Similar questions are out there in this site but that does not exactly address my issues. One such question is here. Taking help from there, I tried to solve the problem in the following way though it did not serve the purpose:

$posts = Posts::where([
    ['meta_name', 'Like', '%word1%'],
    ['meta_name', 'Like', '%word2%'],
    ['meta_name', 'Like', '%word3%'],
    ['meta_description', 'Like', '%word1%'],
    ['meta_description', 'Like', '%word2%'],
    ['meta_description', 'Like', '%word3%'],
    
])->get();

But obviously , it gets me nowhere. The above query is only for matching all three words in the columns meta_name and meta_description.

You can just clone the Github repo I gave above to run the Laravel code in your system. And for generating demo data , you can use the Laravel faker library. A tutorial on faker from here or any other place will be helpful.

How to solve the problem then?

Look into full text search from mysql. That is what you need. — marius-ciclistu, Jun 15 '23 at 21:17
https://laravel.com/docs/10.x/scout with Elastic search but I think it should work without it using raw match sql. — marius-ciclistu, Jun 15 '23 at 21:20
https://stackoverflow.com/questions/38488926/laravel-query-builder-search-match-against your 2 columns should have index of type full text search and your schema to be innodb.https://stackoverflow.com/questions/1381186/fulltext-search-with-innodb — marius-ciclistu, Jun 15 '23 at 21:23
@marius-ciclistu, how to maintain the order of the search results as I said in OP ? — Istiaque Ahmed, Jun 15 '23 at 21:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/254096/discussion-between-istiaque-ahmed-and-marius-ciclistu). — Istiaque Ahmed, Jun 15 '23 at 21:25
Read the documentation of full text search in mysql. It has some signs . For example + means all words. Default is with or but I think it is ordering the results close to what you want. — marius-ciclistu, Jun 15 '23 at 21:27
try separate queries for each set of words and merge them afterwards — Abdulla Nilam, Jun 16 '23 at 05:16
@AbdullaNilam, can I try by just using MYSQL ? Can you write the first 2 queries ? — Istiaque Ahmed, Jun 18 '23 at 14:22

score 0 · Answer 1 · answered Jun 19 '23 at 09:04

I'm not sure whether this has another method. But this is what I thought of it

$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'));

and finally union all

$posts = $all->union($twoWords)->union($oneWord)->get(); // check this first
# or
$posts = $all->union($twoWords)->union($oneWord)->skip($start)->take($this->rowperpage)->get();

Laravel - search with multiple keywords against multiple columns with the search result to be ordered in relevance

1 Answers1

Linked