[en] Knowledge Implement full text search in MariaDB/MySQL with Laravel

[mahuy.tu91]

TUTORIAL:
June 14, 2017 / Laravel, MariaDB, MySQL

MySQL provides a powerful mechanism for searching text across multiple columns. This feature is called Full Text Search and was reserved for tables of type MyISAM. Luckily enough, it has been added to tables of type InnoDB with the release of MySQL version 5.6+.

Full Text Search allows searching text efficiently accross multiple columns. It has several search modes that accept different matching patterns. When using it, a relevance score is calculated, and the results are automatically ordered by it.

Is it missing from Laravel?
Yes, Taylor Otwell, the creator of Laravel, wants to keep Eloquent as platform-independent as possible. And since Full Text Search is specific to MySQL, it was not included. In his own words: “Full text search is pretty vendor specific and not something that is currently supported by Laravel.” Read Thread
On this tutorial, I will cover how to easily implement full text searches.
Replacement for Laravel Scout / Algolia?
Laravel Scout is a powerful tool to do Full Search Text and Algolia is one of the engines supported. In my opinion, there are not as simple to set up as it seems, and for many projects, it seems a little overkill. Also, Algolia is a paid and external service which is not as convenient for many.
When to use Full Text Search?

• To search on multiple columns at once. i.e. matching users against first name, last name, email, and username.
• To sort results by relevance. i.e. closer matches should be at the top
• To improve the performance of queries using `LIKE ‘%term%’
• To apply advanced search patterns like skipping results that include a specific word.
• To use your own MySQL server

TUTORIAL: IMPLEMENTING FULL TEXT SEARCH IN LARAVEL 5
In this tutorial, we will use full text search to find users by matching any of the columns first name, last name, and email. We will develop a reusable trait to create a query scope that can be used along with query builders to fine-tune the search.
1) Adding the index on the migration
FULL TEXT is a type of index and it accepts one or more columns. Since Laravel does not ship with a method for that, we will need to use a raw statement.

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('email')->unique();
    });
 
    // Full Text Index
    DB::statement('ALTER TABLE users ADD FULLTEXT fulltext_index (first_name, last_name, email)');
}

Remember to use the Illuminate\Support\Facades\DB
2) RUN THE MIGRATIONS
php artisan migrate

Remember that you need MySQL 5.6+ if using InnoDB

3) Defining the searchable columns
Go to the User model and add the following code. It is required to put exactly the same columns that were defined in the index in step 1.

/**
     * The columns of the full text index
     */
    protected $searchable = [
        'first_name',
        'last_name',
        'email'
    ];

If these columns do not match the columns defined when creating the index, then MySQL will not find the index and it will throw the following error message: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list
4) Creating the trait
Instead of putting the search code on every model, we will use a trait to centralize it and avoid repetition.
Create the file FullTextSearch.php on the same folder as your models and paste the following code.





DEFAULT MODE

MATCH (first_name, last_name, email) AGAINST ('john')
This will match any column that has a word that is exctly ‘john’.

BOOLEAN MODE
There are several search modes, but we will use the boolean mode which allows the use of special operators to fine-tune the search. In particular, we will use the * as a wildcard. For a full list of operators visit the official documentation.

MATCH (first_name, last_name, email) AGAINST ('john*' IN BOOLEAN MODE)
This will match any column that has a word that starts with ‘john’ such as ‘johny’.

5) Include the trait
Back to the User model, simply include the trait.

class User extends Authenticatable
{
    use FullTextSearch;

6) Start searching
Using the search is really simple. Since we used a query scope it can be used alone, or in conjunction with other where clauses, order by, or even pagination.

// assuming 'arian' is the in column first_name, and 'acosta' in last_name
// all the following queries will work!
User::search('arian acosta')->get();
User::search('acosta arian')->get();
User::search('ari aco')->get();
User::search('arian')->get();
User::search('arian acosta somethingelse')->get();
 
// refining the query
User::search('arian')->where('is_active', '=', 1)->get();
User::search('arian')->latest()->get();
User::search('arian')->paginate();

7) Using on multiple models
Well, we already have the trait, all you need to do in order to use it with any other model is to:

1. Add the Full Text index
2. Run the migration
3. Define the searchable columns
4. Import the FullTextSearch trait

Conclusion
I hope this tutorial was useful. In my opinion, setting up this feature in Laravel with MySQL is very simple and powerful. It is definitely worth a try before going to the other more complex solutions.
PRO TIP: SORTING BY RELEVANCE SCORE
To sort the results by relevance. You may select the auto-generated relevance score as a column in MySQL like this:

<?php
 
namespace App;
 
trait FullTextSearch
{
    /**
     * Replaces spaces with full text search wildcards
     *
     * @param string $term
     * @return string
     */
    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;
    }
 
    /**
     * Scope a query that matches a full text search of term.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param string $term
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeSearch($query, $term)
    {
        $columns = implode(',',$this->searchable);
 
        $query->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)" , $this->fullTextWildcards($term));
 
        return $query;
    }
}

SELECT *,
MATCH(first_name, last_name, email) AGAINST ('term' IN BOOLEAN MODE) AS relevance_score
FROM users
WHERE MATCH(first_name, last_name, email) AGAINST ('term' IN BOOLEAN MODE);

Simply replace the scopeSearch method in the FullTextSearch trait:

/**
 * Scope a query that matches a full text search of term.
 * This version calculates and orders by relevance score.
 *
 * @param \Illuminate\Database\Eloquent\Builder $query
 * @param string $term
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeSearch($query, $term)
{
    $columns = implode(',',$this->searchable);
 
    $searchableTerm = $this->fullTextWildcards($term);
 
    return $query->selectRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE) AS relevance_score", [$searchableTerm])
        ->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)", $searchableTerm)
        ->orderByDesc('relevance_score');
}