CODEWITHSUNDEEP
laravel-4eloquentquery-builderkeyword-search
chipit24
chipit24

Reputation: 6987

Laravel 4 (Eloquent ORM / Query Builder) how to sort keyword search based on most matches

I have the following keyword search implemented in Laravel 4:

// Search by keyword(s)
if(Input::get('keyword'))
    {
    $search = Input::get('keyword');
    $searchTerms = explode(' ', $search);

    $fields = array(
        'resources.name',
        'resources.description',
        'resources.website',
        'resources.additional_info');

    $query->where(function($query) use ($searchTerms, $fields) {
        foreach ($searchTerms as $term)
        {
            foreach ($fields as $field)
            {
                $query->orWhere($field, 'LIKE', '%'. $term .'%');
            }
        }
    });
}

The problem: Let's say I have two resources in my table, one with the name foo, one with the name bar, and one with the name foo bar. They were created and put into my table in that order. Now, if I search for "foo bar", the first two results I get will be foo and bar (because they are fist in my table to match either foo or bar), and my third result will be foo bar. How can I make this search so it orders the results based on the number of keywords which are matched? So foo bar will be the first result.

Upvotes: 0

Views: 1317

Answers (2)

chipit24
chipit24

Reputation: 6987

I ended up using the following solution for my search:

if(Input::get('keyword')) {
    $search = Input::get('keyword');
    $query->whereRaw("MATCH(resources.name, resources.description, resources.website, resources.additional_info) AGAINST(? IN BOOLEAN MODE)", 
        array($search)
    );
}

I came upon this solution by reading through the following pages:

How to sort MYSQL fulltext search results by relevancy

http://creative-punch.net/2013/12/implementing-laravel-4-full-text-search/

I upgraded to MySQL 5.6 so I could add fulltext indexes to my InnoDB tables. I had to add the fulltext indexes to all the fields I wanted to run MATCH on, in the order that I specified in the whereRaw statement.

Upvotes: 0

Moshe Katz
Moshe Katz

Reputation: 16873

You should implement this using a FULLTEXT search. You don't specify which database engine you're using, so I'll give a MySQL example.

First, you need to enable full-text indexing on the fields you want to search.

ALTER TABLE resources ADD FULLTEXT search (name,description,website,additional_info);

Then, you need to end up with something like the following in your query:

SELECT
    *, MATCH(name,description,website,additional_info) AGAINST(SEARCH_TERMS) AS score
WHERE
    MATCH(name,description,website,additional_info) AGAINST(SEARCH_TERMS)

I would do that in the Laravel Query Builder using something like this (untested):

if(Input::get('keyword'))
{
    $search = Input::get('keyword');
    $searchTerms = explode(' ', $search);

    $fields = array(
        '`resources.name`',
        '`resources.description`',
        '`resources.website`',
        '`resources.additional_info`'
    );

    $fields_str = implode(',', $fields);
    $query->whereRaw("match ($fields_str) against (? in boolean mode)")
    $query->addSelect(DB::raw("match ($fields_str) against (? in boolean mode)")
    // Need to bind twice because there are two question marks...
    $query->setBindings(array($searchTerms, $searchTerms))
}

Depending on your search, you may or may not actually need in boolean mode, or you may want to specify a different mode. See the MySQL manual for details.

NOTE: Most relational database engines aren't really that good at search; it's just not what they were originally designed to do. If you find that your search needs are getting more complicated than this simple example, you should look into running a search server like Lucene, Sphinx, Elasticsearch, or Solr

(For more info, see http://blog.oneiroi.co.uk/mysql/php/mysql-full-text-search-with-percentage-scoring/ and http://www.mullie.eu/mysql-as-a-search-engine/)

Upvotes: 1

Related Questions