Order By based on number of LIKE occurences Laravel Php

Question

Currently I have a simple search engine on my site

I'm trying to match the keyword in all fields.

Here's the simplified working codes

$keyword_search = "Hello World";
$delimeter = ' '; //or your separator
$keywords = explode($delimeter, $keyword_search); 

$base_query = \App\Models\EntireSearchSite::where('id','!=',NULL);

foreach($keywords as $keyword){
    $base_query->where(function($query) use ($keyword){
            $query->where('part_number', 'like', '%' . $keyword . '%') 
                ->orWhere('part_name', 'like', '%' . $keyword . '%')
                ->orWhere('category', 'like', '%' . $keyword . '%')
                ->orWhere('description', 'like', '%' . $keyword . '%');
    });
    
}

$data = $base_query->paginate(20);
return $data;

What I'm trying to do is to match all keyword, count all matched keyword (for every occurences) and do Order By Desc from highest occurences to lowest.

If I enter "Hello World"

it will find all the data that will match to "Hello World"

I got part_number, part_name, category, description fields

it will sum the total number of occurrences on all fields and arrange the ORDER BY from highest to lowest (DESC)

EXAMPLE:

DATA #1

number of matched keyword in part_number is 0

number of matched keyword in part_name is 2

number of matched keyword in category is 0

number of matched keyword in description is 0

TOTAL = 2

DATA #2

number of matched keyword in part_number is 0

number of matched keyword in part_name is 0

number of matched keyword in category is 2

number of matched keyword in description is 10

TOTAL = 12

DATA #3

number of matched keyword in part_number is 0

number of matched keyword in part_name is 0

number of matched keyword in category is 0

number of matched keyword in description is 5

TOTAL = 5

and order by desc should be something like this

DATA#2

DATA#3

DATA#1

UPDATE*

Tried the answer from @Anas

$delimeter = ' '; //or your separator
$keywords = explode($delimeter, $keyword_search); 

$base_query = \App\Models\EntireSearchSite::where('id','!=',NULL);

foreach($keywords as $keyword){
    $base_query->where(function($query) use ($keyword){
            $query->where('part_number', 'like', '%' . $keyword . '%') 
                ->orWhere('part_name', 'like', '%' . $keyword . '%')
                ->orWhere('category', 'like', '%' . $keyword . '%')
                ->orWhere('description', 'like', '%' . $keyword . '%');
    });
}

foreach($keywords as $keyword){
            $base_query->selectRaw(function($querySelect) use ($keyword){
                $querySelect->selectRaw('
                    Round ((Char_length(Concat(
                        part_number, 
                        part_name,
                        category,
                        description

                    )) - Char_length(REPLACE ( Concat(
                        part_number, 
                        part_name,
                        category,
                        description
                    ), "'.$keyword.'", ""))) / Char_length("'.$keyword.'"))  AS count 
                ');

            });
}        

$data = $base_query->orderBy('count','DESC')->paginate(20);
return $data;

But I'm getting undefined column count

Update

Applied answer by @Donkarnash

user_1220364 · Answer

Since using the selectRaw in a loop, the alias of the count operation should not be count (repeating for all iterations of loop). Rather it should be 'count'.$keyword to make it a unique alias.

Similarly the orderBy() should also be defined within the loop so as to orderBy the 'count'.$keyword columns.

$base_query = \App\Models\EntireSearchSite::where('id','!=',NULL);

foreach($keywords as $keyword){
    $base_query->where(function($query) use ($keyword){
        $query->where('part_number', 'like', '%' . $keyword . '%') 
            ->orWhere('part_name', 'like', '%' . $keyword . '%')
            ->orWhere('category', 'like', '%' . $keyword . '%')
            ->orWhere('description', 'like', '%' . $keyword . '%');
    });
}

foreach($keywords as $keyword) {
    $base_query->selectRaw('
        Round (
            (Char_length(Concat(email, name)) - Char_length(REPLACE ( Concat(email,name), "' . $keyword .'", ""))
        ) / Char_length("' . $keyword .'")
        ) AS count' .  $keyword
    )
    ->orderBy("count$keyword", "desc");
}

$data = $base_query->paginate(20);
return $data;

user_9972285 · Answer

Try to concat related column in one string then get total number of occurrences by this query sample. also you can use in select DB:raw() Laravel Documentation

SELECT email, 
       name, 
       Round ((Char_length(Concat(email, name)) - Char_length(REPLACE ( Concat(email,name), "anas", ""))) / Char_length("anas")) AS count 
FROM   users
Having count >0 
ORDER  BY count DESC;

Result of query

For sperate Keyword you can sum new Round function with different keyword check this query

SELECT email, 
       name, 
       Round ((Char_length(Concat(email, name)) - Char_length(REPLACE ( Concat(email,name), "first_keyword", ""))) / Char_length("first_keyword")) 
       + Round ((Char_length(Concat(email, name)) - Char_length(REPLACE ( Concat(email,name), "second_keyword", ""))) / Char_length("second_keyword"))  AS count 
FROM   users
Having count >0 
ORDER  BY count DESC;

To make it case insensitive you can convert keywords and columns to lower case Ex:

 SELECT email,
        name,
    Round ((lower(Char_length(Concat(email, name))) - Char_length(REPLACE ( lower(Concat(email,name)), lower("aasd"), ""))) / Char_length(lower("aasd"))) AS count 
    FROM   users 
    Having count >0 ORDER  BY count DESC;

user_10417341 · Answer

You can select all related raws and then sort them in your controller. Please note that complex queries take a lot of time to complete. In my opinion, you can select your data and then count the match keywords by using strpos function.

foreach($i=0;$ipartition, $keyword) !== false) {
    $counter = $counter+1; 
  .
  .
  .
  .

  }
 }
  $final[$i] = [
  'data'=>$data[$i],
  'priority'=>$counter
];
}
then you can sort it by ```arsort()``` based on priority

Order By based on number of LIKE occurences Laravel Php

3 Answers