Order By based on number of LIKE occurences Laravel Php

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 = AppModelsEntireSearchSite::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 = AppModelsEntireSearchSite::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

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;