Laravel 5 Full Text Search

Question

I have a db table with fields first_name and last_name. I need to check if the credit card holder name given by Asiapay is part of my traveling party (records in my db table). Issue is that credit card holder given is full name (single field)

So far my options are:

• using fulltext search
• using https://github.com/atomescrochus/laravel-string-similarities but not sure what percentage to consider correct

I need some suggestions for other libraries and strategies regarding string comparison

PS: I just cannot compare concatenated first_name and last_name vs holder_name since there are cases that names in traveling documents are different than the ones in a person's credit card (eg. "John Foo Doe" in passport vs "John Doe" in credit card)

Answer 1

Here is how you should implement a FULL TEXT search

First create a full text search on your table.

// Full Text Index
DB::statement('ALTER TABLE contacts ADD FULLTEXT fulltext_index (firstName, lastName, email)');

Then in your model

$columns = 'firstName,lastName,email';
    $contacts = Contact::where('customer_id', $request->user()->customer_id)
        ->select(['id', 'firstName', 'lastName', 'email', 'phone', 'mobile', 'lastContacted', 'assignedTo', 'createdBy'])
        ->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)", $this->fullTextWildcards($q))
        ->paginate(10);

Here is the function fullTextWildcards which replaces spaces with full text search wildcards.

protected function fullTextWildcards($term)
{
    // removing symbols used by MySQL
    $reservedSymbols = ['-', '+', '<', '>', '@', '(', ')', '~'];
    $term = str_replace($reservedSymbols, '', $term);

    $words = explode(' ', $term);

    foreach ($words as $key => $word) {
        /*
         * applying + operator (required word) only big words
         * because smaller ones are not indexed by mysql
         */
        if (strlen($word) >= 3) {
            $words[$key] = '*' . $word . '*';
        }
    }

    $searchTerm = implode(' ', $words);

    return $searchTerm;
}

Answer 2

check this package. has many options to improve searches with fulltext

https://github.com/ProVisionBG/searchable