CODEWITHSUNDEEP
sqllaravelsearchlaravel-5
Atanu
Atanu

Reputation: 337

advanced search query laravel

In my website i need to provide a search option where user can search for different product. Now i want if search key match with product display it and along with display some suggestion contain with that search keyword separated by space.

list($type,$search) = explode(":", $key);
$searchValues = preg_split('/\s+/', $search, -1, PREG_SPLIT_NO_EMPTY);

ProductDetail::join('product_brands','product_details.brand_id','product_brands.id')
              ->select('brand_name as manufacturer','product_availability as isInStock','product_details.*','product_price as displayPrice')
              ->where(function ($q) use ($searchValues,$search) {
                    $q->where('product_name', 'like', "%{$search}%");
                    foreach ($searchValues as $value) {
                      $q->orWhere('product_name', 'like', "%{$value}%");
                    }
                })->paginate(5);

Now problem with this result is, it does't display according to best/first match. so user can't find what they are looking for in the list of other suggestions.

So how to get best matched order?

View Live Demo

Upvotes: 1

Views: 1143

Answers (1)

Jeff
Jeff

Reputation: 25221

What you need is some sort of weighted search. If you take a look at this package you can see some example MySQL queries it generates: https://github.com/nicolaslopezj/searchable

Scroll to the "result" section where it shows the resulting queries it produces. That will help you weight your results on best match. You can also look into Laravel Scout if you have deeper search needs.

This is the example query I was referring to, where this package was used to search users like Sed neque labore :

select `users`.*, 

-- If third parameter is set as true, it will check if the column starts with the search
-- if then it adds relevance * 30
-- this ensures that relevant results will be at top
(case when first_name LIKE 'Sed neque labore%' then 300 else 0 end) + 

-- For each column you specify makes 3 "ifs" containing 
-- each word of the search input and adds relevace to 
-- the row

-- The first checks if the column is equal to the word,
-- if then it adds relevance * 15
(case when first_name LIKE 'Sed' || first_name LIKE 'neque' || first_name LIKE 'labore' then 150 else 0 end) + 

-- The second checks if the column starts with the word,
-- if then it adds relevance * 5
(case when first_name LIKE 'Sed%' || first_name LIKE 'neque%' || first_name LIKE 'labore%' then 50 else 0 end) + 

-- The third checks if the column contains the word, 
-- if then it adds relevance * 1
(case when first_name LIKE '%Sed%' || first_name LIKE '%neque%' || first_name LIKE '%labore%' then 10 else 0 end) + 

-- Repeats with each column
(case when last_name LIKE 'Sed' || last_name LIKE 'neque' || last_name LIKE 'labore' then 150 else 0 end) + 
(case when last_name LIKE 'Sed%' || last_name LIKE 'neque%' || last_name LIKE 'labore%' then 50 else 0 end) +
(case when last_name LIKE '%Sed%' || last_name LIKE '%neque%' || last_name LIKE '%labore%' then 10 else 0 end) + 

(case when bio LIKE 'Sed' || bio LIKE 'neque' || bio LIKE 'labore' then 30 else 0 end) + 
(case when bio LIKE 'Sed%' || bio LIKE 'neque%' || bio LIKE 'labore%' then 10 else 0 end) + 
(case when bio LIKE '%Sed%' || bio LIKE '%neque%' || bio LIKE '%labore%' then 2 else 0 end) + 

(case when email LIKE 'Sed' || email LIKE 'neque' || email LIKE 'labore' then 75 else 0 end) + 
(case when email LIKE 'Sed%' || email LIKE 'neque%' || email LIKE 'labore%' then 25 else 0 end) + 
(case when email LIKE '%Sed%' || email LIKE '%neque%' || email LIKE '%labore%' then 5 else 0 end) 

as relevance 
from `users` 
group by `id` 

-- Selects only the rows that have more than
-- the sum of all attributes relevances and divided by 4
-- Ej: (20 + 5 + 2) / 4 = 6.75
having relevance > 6.75 

-- Orders the results by relevance
order by `relevance` desc

Upvotes: 3

Related Questions