Tuesday, March 7, 2017

How to get and order the most relevant entries from the database with multiple keywords Laravel 5

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.



via Nick Howarth

Advertisement