Laravel & MySQL Fulltext Search

Let's suppose that we have a products table with the following structure

table structure

Schema::create('products', function (Blueprint $table) {
     $table->id();
     $table->string('name');
     $table->string('slug');
     $table->string('description', 250)->nullable();
     $table->text('content')->nullable();
     $table->timestamps();
});

fulltext index

The next thing we need to create is a fulltext search index. I want to be able to search by name description and content information.

//Create fulltext index
DB::connection('mysql')->statement('
    CREATE fulltext INDEX products_fulltext_index
        ON products ( name, description, content)
        WITH parser ngram
');

//Drop fulltext index
DB::connection('mysql')->statement('
    ALTER TABLE products DROP INDEX products_fulltext_index;
');

sql query

Now we can try out this query in our database.

SELECT
	id,
	name,
	description,
	content,
	match(name, description, content) against ('victor yoalli' IN boolean mode) as score
FROM
	products
WHERE
	match(name, description, content) against ('victor yoalli' IN boolean mode);

We copy the same where statement to the select and use an alias of score that way we can know the ranking. By the default al records are ordered from the highest ranking to the lowest.

laravel implementation

I created a scope on my model to be able to use it.

class Product extends Model
{
    protected $guarded = [];

    public function scopeSearch($query, $keywords)
    {
        return $query->when(
            $keywords,
            function ($query, $keywords) {
                $query->selectRaw('*, match(name, description, content) against (? IN boolean mode) as score', [$keywords])
                ->whereRaw('match(name, description, content) against (? IN boolean mode)', [$keywords]);
            },
            function ($query) {
                $query->latest();
            }
        );
    }
}

The implementation would be something like this.

Product::search('doyo')->paginate();

That's it!!