4

I am having trouble handling multiple keywords and querying the database based on relevance. I want to search each row and if there is more than 1 keyword per row matching based on my selected columns, to sort these entries first.

I do have something working, but it just pulls all entries with a keyword present in a column in no specific order or relevance.

Take this working example:

$search_terms = array('York', 'North Yorkshire');

$properties = Property::where(function ($q) use ($search_terms) {
            foreach ($search_terms as $value) {
                $q->orWhere('address1', 'like', "%{$value}%");
                $q->orWhere('address2', 'like', "%{$value}%");
                $q->orWhere('postcode', 'like', "%{$value}%");
                $q->orWhere('city_town', 'like', "%{$value}%");
                $q->orWhere('county', 'like', "%{$value}%");
            }
        })->paginate(25);

This works and pulls back all entries with the keywords present in any of my selected columns. In this instance York from the city_town column and North Yorkshire from the county column.

I need the the query to check each individual row for these keywords and bring back entries where ALL of these keywords are present, followed by where one or more is present afterwards (my example does this now).

Many thanks in advance to anyone who can help.

Samsquanch
  • 8,866
  • 12
  • 50
  • 89
Nick Howarth
  • 511
  • 4
  • 21

2 Answers2

11

Okay maybe some SQL magician could give you a better SQL solution. But until then...

This is how I would do it with Laravel collections (sorting with php):

$search_terms = array('York', 'North Yorkshire');

$properties = Property::where(function ($q) use ($search_terms) {
            foreach ($search_terms as $value) {
                $q->orWhere('address1', 'like', "%{$value}%");
                $q->orWhere('address2', 'like', "%{$value}%");
                $q->orWhere('postcode', 'like', "%{$value}%");
                $q->orWhere('city_town', 'like', "%{$value}%");
                $q->orWhere('county', 'like', "%{$value}%");
            }
        })->paginate(25);

$props = ['address1', 'address2', 'postcode', 'city_town', 'county'];

$properties = $properties->sortByDesc(function($i, $k) use ($search_terms, $props) {
    // The bigger the weight, the higher the record
    $weight = 0;
    // Iterate through search terms
    foreach($search_terms as $searchTerm) {
        // Iterate through properties (address1, address2...)
        foreach($props as $prop) { 
            // Use strpos instead of %value% (cause php)
            if(strpos($i->{$prop}, $searchTerm) !== false)
                $weight += 1; // Increase weight if the search term is found
        }
    }

    return $weight;
});

$properties = $properties->values()->all();
DevK
  • 9,597
  • 2
  • 26
  • 48
  • Well done sir! impressive. I want to use the paginate() method with this new and lovely system you have devised, it doesn't seem to be working, guessing it is because we have modified the initial query and the method is no longer accessible in our new array? – Nick Howarth Mar 08 '17 at 09:35
  • I just ended up creating manual pagination following a few threads I found online. [example here](http://stackoverflow.com/questions/30477915/laravel-pagination-not-working-with-array-instead-of-collection?answertab=votes#tab-top) Thanks again! – Nick Howarth Mar 08 '17 at 09:46
  • Thank you :) I think you could just use [`->getCollection()`](https://laravel.com/api/5.0/Illuminate/Pagination/LengthAwarePaginator.html#method_getCollection) on the pagination results to get the collection. – DevK Mar 08 '17 at 10:16
  • No problem :D I will keep that pagination function for now as I think it will come in very handy! I need to develop this search even more! I can take this method and adapt nicely, as bedrooms, bathrooms and more will be thrown in the mixer! – Nick Howarth Mar 08 '17 at 11:36
  • there is a bug. strpos is case sensitive, and `like %` is not. That means you remove a lot of matches with this sortbydesc. Use `stripos` instead – Toskan Mar 26 '22 at 18:49
  • This solution is great! But the disadvantage of this answer is its only sorting per 25 records its not using the whole table. – aceraven777 Jun 24 '22 at 01:08
  • @DevK, may I ask you to take a look at a Laravel search related question here : https://stackoverflow.com/questions/76485513/laravel-search-with-multiple-keywords-against-multiple-columns-with-the-search ? – Istiaque Ahmed Jun 15 '23 at 21:08
1

I definitely hope you solved your problem in 1 year, but i think i faced the same kind of problem and here is how i handled it. (i think devk solution is a bit too greedy in term of performance). I'm using Laravel 5.6 btw

I get some expertises in string format in my database, here is an example of which value an expertise could have :

user->expertises = "SAP, ERP, team management"

I want to put some expertises in my search field and get the most relevant result :

$input = "SAP, nagement" // Expertises searched, volontary 'nagement' uncompleted word
$keywords = explode(", ", $input); // Array of keyword

$raw = "(";
foreach($keywords as $key) {
    $raw .= "(user.expertises LIKE '%".$key."%')+";
}
$raw = substr($raw, 0, -1); // Remove the last "+"
$raw .= ") as weight";
// End result :
// ((user.expertises LIKE '%SAP%')+(user.expertises LIKE '%nagement%')) 
// as weight

// It will do smtg like '(0+1+1) as weight' for example, for each user


$request = User::select('*')->addSelect(DB::raw($raw));
return $request->orderBy('weight', 'DESC')->paginate(10);

My example user will have 2 weight. And will be in the top of result (because weight is between 0 and 2 here, depending on the number of keywords)

TiDJ
  • 433
  • 1
  • 4
  • 6
  • creative solution, something similar at the package laravel/eloquent – manuel-84 Oct 30 '19 at 20:58
  • may I ask you to have a look at a Laravel search related question here: https://stackoverflow.com/questions/76485513/laravel-search-with-multiple-keywords-against-multiple-columns-with-the-search ? – Istiaque Ahmed Jun 15 '23 at 21:08