0

I have this query that returns the Products that contains the search word. How can i sort them based on the number of occurrences of the search word ?

$products = Product::where('name','LIKE','%'.$search.'%')
                ->orWhere('name', 'like', '%' . str_replace(' ', '-', $search) . '%')
                ->orWhere('features','LIKE','%'.$search.'%')->get();

I have the SQL query but i don't know how can i combine it with the query above :

SELECT *,
(LENGTH(`name`) - LENGTH(REPLACE(`name`, $search, ''))) / LENGTH('test') `appears_in_text`,

(LENGTH(`features`) - LENGTH(REPLACE(`features`, $search, ''))) / LENGTH($search) `appears_in_subject`,

(LENGTH(CONCAT(`name`,' ',`features`)) - LENGTH(REPLACE(CONCAT(`name`,' ',`features`), $search, ''))) / LENGTH($search) `occurences`
 FROM 
`products`
 WHERE (name LIKE $search OR features LIKE $search)
ORDER BY `occurences`  DESC
Abdo Rabah
  • 1,670
  • 2
  • 15
  • 31

1 Answers1

-1

One thing you could to is to sort them afterwards. This can be done by rating each product by how many words from the search string are present.

    $searchWords = array_filter(explode(' ', trim($search)));
    $matchPoint = 100/sizeof($searchWords);


    foreach ($products as $product) {

            $match = 0;

            foreach ($searchWords as $word) {

                if(str_contains(strtolower($product->name), strtolower($word)) ) {

                    $match +=  $matchPoint;
                }
            }

            $product->match = $match;

        }

//then sort by highest rated

$sortedProducts = $products->sort(function ($a, $b) {

            if ($a->match > $b->match) {
                return -1;
            }
            if ($a->match < $b->match) {
                return 1;
            }
            return 0;
        });

return $sortedProducts->values();

I know it's proberly not the most efficient but it'll get the job done.

Pelleoe
  • 1
  • 1