The Clearest Guide to Full-Text Search Migrations in Laravel

This is Akagishi, a backend engineer.

I recently implemented full-text search for the first time, and I found that the articles I found online as reference were honestly kind of confusing not very beginner-friendly. So in this post, I'll explain how to implement it in a way that even beginners can pick up quickly and easily!

Environment

Laravel 8.x MySQL Docker

The Documentation Trap: A Setup That Doesn't Actually Work

Being the model beginner that I am, when I looked up how to write full-text search in the documentation, here's what it explained:

$table->fulltext('body'); | Add a fulltext index (MySQL/PostgreSQL)

https://readouble.com/laravel/8.x/ja/migrations.html

I thought, "wow, that's easy," and figured I was basically done, but with the setup written above, you run into the problem that partial-match records can't be retrieved. Goddammit!!! To retrieve records via partial match, you need to use the ngram parser or MeCab.

On top of that, by default it's configured to only search on words of 3 characters or more. That's inconvenient for Japanese. To make sure it can handle all sorts of compound words, you also need to change the configuration so it can search on words of 2 characters or more.

I'll go into detail in the next section.

The Final Version | How to Match Partially on Words of 2+ Characters

Configuring It to Search on 2+ Characters

Since we're using Docker, add the following to the MySQL config file (xxx.cnf):

[mysqld]
innodb_ft_min_token_size = 2

Using the ngram Parser in a Migration

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatexxxTable extends Migration
{
    public function up()
    {
        Schema::create('xxx', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('hogehoge');
            $table->timestamps();
    });

    \DB::statement('ALTER TABLE テーブル名 ADD FULLTEXT INDEX インデックス名 (`該当カラム名`) with parser ngram');
}

    public function down()
    {
        Schema::dropIfExists('xxx');
    }
}

That's it. Now you're a full-text search master too.

See you again sometime. Take care, uhuhuhu~