はじめに

本記事は以下の続編になります。
https://qiita.com/naoki-haba/items/ace7a5d1e0d9d72ed040

続編記事を書くことにした経緯

シンプルなテーブル構成の場合は以下の通りにすれば全文検索の準備は整います

DB::statement("ALTER TABLE shops ADD free_word TEXT as (concat(IFNULL(age, ''), ' ',IFNULL(name, ''), ' ',(case gender_id when 1 then '男性' when 2 then '女性' else '' end), ' ')) STORED");

しかし、複雑な要因（複数テーブルとの外部結合が必要な場合etc)の場合に、上記の記述をすることに苦労したので、今回は対処方法の選択肢の1つとしてご紹介させていただきます。

記事の流れ

1.既存のDDLからfree_wordカラムを削除する
2.ダミーデータを投入する
3.free_wordカラムを追加する
4.登録・更新イベントをディスパッチする処理を追加する
5.Artisanコマンドを作成する
6.作成したArtisanコマンドを実行。
　free_wordカラムにデータを投入する

事前準備

docker-compose up -d
docker-compose exec app bash
composer install
composer update
cp .env.example .env
php artisan key:generate
php artisan storage:link
chmod -R 777 storage bootstrap/cache

Laravel + Ngram + Observerを利用した全文検索機能の実装ハンズオン

1.既存のDDLから`free_word`カラムを削除する

変更用のmigrationファイルを生成します

php artisan make:migration change_free_word_to_shops --table=shops

migrationを定義

backend/database/migrations/2022_01_06_185439_change_free_word_to_shops.php

class ChangeFreeWordToShops extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('shops', function (Blueprint $table) {
            $table->dropColumn('free_word');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('shops', function (Blueprint $table) {
            DB::statement("ALTER TABLE shops ADD free_word TEXT as (concat(IFNULL(age, ''), ' ',IFNULL(name, ''), ' ',(case gender_id when 1 then '男性' when 2 then '女性' else '' end), ' ')) STORED");
        });
    }

migrationを実行する

php artisan migrate

DDLを確認し,shopsテーブルにfree_wordカラムがなければOKです

ngram-docker-laravel

docker-compose exec db bash

mysql -u root -p
Enter password: password

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel_local      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use laravel_local;

mysql> show tables;
+-------------------------+
| Tables_in_laravel_local |
+-------------------------+
| failed_jobs             |
| migrations              |
| password_resets         |
| personal_access_tokens  |
| shops                   |
| users                   |
+-------------------------+
6 rows in set (0.00 sec)

 DESC shops;
+------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| Field      | Type            | Null | Key | Default           | Extra                                         |
+------------+-----------------+------+-----+-------------------+-----------------------------------------------+
| id         | bigint unsigned | NO   | PRI | NULL              | auto_increment                                |
| name       | varchar(255)    | NO   |     | NULL              |                                               |
| age        | int unsigned    | NO   |     | NULL              |                                               |
| gender_id  | smallint        | NO   |     | NULL              |                                               |
| created_at | timestamp       | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at | timestamp       | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+-----------------+------+-----+-------------------+-----------------------------------------------+
6 rows in set (0.01 sec)

2.ダミーデータを投入する

実行するSeedファイル

backend/database/seeders/DummyShopsSeeder.php

<?php

namespace Database\Seeders;

use App\Models\Shop;
use Illuminate\Database\Seeder;

class DummyShopsSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $data = [
            [
                'name' => 'サンプル太郎',
                'age' => 25,
                'gender_id' => 1
            ],
            [
                'name' => 'サンプル花子',
                'age' => 30,
                'gender_id' => 2
            ],
            [
                'name' => 'サンプル二郎',
                'age' => 20,
                'gender_id' => 1
            ],
        ];

        (new Shop())->query()->insert($data);
    }
}

Seedファイルを実行

 php artisan db:seed --class=DummyShopsSeeder

Seed結果を確認し登録できていれば成功です

mysql> select * from shops;
+----+--------------------+-----+-----------+---------------------+---------------------+
| id | name               | age | gender_id | created_at          | updated_at          |
+----+--------------------+-----+-----------+---------------------+---------------------+
|  1 | サンプル太郎       |  25 |         1 | 2022-01-07 04:19:43 | 2022-01-07 04:19:43 |
|  2 | サンプル花子       |  30 |         2 | 2022-01-07 04:19:43 | 2022-01-07 04:19:43 |
|  3 | サンプル二郎       |  20 |         1 | 2022-01-07 04:19:43 | 2022-01-07 04:19:43 |
+----+--------------------+-----+-----------+---------------------+---------------------+
3 rows in set (0.01 sec)

3.`free_word`カラムを追加する

再度free_wordカラムを追加するmigrationを作成します

php artisan make:migration add_free_word_column_to_shops --table=shops

migrationファイルを定義します

backend/database/migrations/2022_01_07_162519_add_free_word_column_to_shops.php

class AddFreeWordColumnToShops extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('shops', function (Blueprint $table) {
            DB::statement("ALTER TABLE shops ADD free_word TEXT");
            DB::statement("ALTER TABLE shops ADD FULLTEXT index ftx_free_word (free_word) with parser ngram");
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('shops', function (Blueprint $table) {
            $table->dropColumn('free_word');
        });
    }
}

migrationを実行する

php artisan migrate

DDLを確認しshopsテーブルにfree_wordカラムが追加されていればOKです

ngram-docker-laravel

docker-compose exec db bash

mysql -u root -p
Enter password: password

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel_local      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use laravel_local;

mysql> show tables;
+-------------------------+
| Tables_in_laravel_local |
+-------------------------+
| failed_jobs             |
| migrations              |
| password_resets         |
| personal_access_tokens  |
| shops                   |
| users                   |
+-------------------------+
6 rows in set (0.00 sec)

mysql> select * from shops;
+----+--------------------+-----+-----------+---------------------+---------------------+-----------+
| id | name               | age | gender_id | created_at          | updated_at          | free_word |
+----+--------------------+-----+-----------+---------------------+---------------------+-----------+
|  1 | サンプル太郎       |  25 |         1 | 2022-01-08 01:54:17 | 2022-01-08 01:54:17 | NULL      |
|  2 | サンプル花子       |  30 |         2 | 2022-01-08 01:54:17 | 2022-01-08 01:54:17 | NULL      |
|  3 | サンプル二郎       |  20 |         1 | 2022-01-08 01:54:17 | 2022-01-08 01:54:17 | NULL      |
+----+--------------------+-----+-----------+---------------------+---------------------+-----------+

4.`登録`・`更新`イベントをディスパッチする処理を追加する

さて,ここまでで全文検索用のカラムの作成が完了しました。
ですが見ての通りfree_wordカラムはNULLなのでこれでは全文検索ができません。
そこで、登録・更新イベントをディスパッチして自動的にfree_wordカラムに追加する値を生成していきます

オブザーバーを作成します

php artisan make:observer ShopObserver --model=Shop

オブサーバーを定義

:backend/app/Observers/ShopObserver.php

<?php

namespace App\Observers;

use App\Models\Shop;

class ShopObserver
{
    /**
    * save()イベントを検知する
    * @param Shop $shop
    * @return void
    */
    public function saved(Shop $shop)
    {
        $collect = collect($shop);
        $id = $collect->get('id');
        $name = $collect->get('name');
        $age = $collect->get('age');
        $genderId = $collect->get('gender_id');

        if (!is_null($genderId)) {
            $gender = (int)$genderId === 1 ? '男性' : '女性';
        } else {
            $gender = null;
        }

        $freeWord = $age . ' ' . $id . ' ' . $name . ' ' . $gender;

        $data = [
            'id' => $id,
            'name' => $name,
            'age' => $age,
            'gender_id' => $genderId,
            'free_word' => $freeWord,
        ];

        (Shop::query()->where('id', $id))->update($data);
    }
}

オブサーバーを登録

backend/app/Providers/EventServiceProvider.php

class EventServiceProvider extends ServiceProvider
{
    /**
     * The event listener mappings for the application.
     *
     * @var array<class-string, array<int, class-string>>
     */
    protected $listen = [
        Registered::class => [
            SendEmailVerificationNotification::class,
        ],
    ];

    /**
     * Register any events for your application.
     *
     * @return void
     */
    public function boot()
    {
        Shop::observe(ShopObserver::class);
    }
}

5.`Artisan`コマンドを作成する

コマンド生成

php artisan make:command UpdateFreeWordByShop

コマンド定義

class UpdateFreeWordByShop extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'update:free-word-by-shop';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'shopsテーブルのfree_wordを登録するコマンド';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

     /**
     * Execute the console command.
     *
     * @return void
     */
    public function handle()
    {
        $updateTarget = Shop::query()->pluck('id');

        foreach ($updateTarget as $id) {
            $target = Shop::find($id);
            $result = $target->save();

            if (!$result) {
                echo "店ID:{$id}の登録中にエラーが発生しました。終了します\n";
                exit();
            }

            echo "{$id}完了\n";

        }

        echo "処理完了。終了します。\n";
        exit();
    }
}

6.作成した`Artisan`コマンドを実行して`free_word`カラムにデータを投入する

作成したコマンドが登録されていることを確認

php artisan

update
  update:free-word-by-shop  shopsテーブルのfree_wordを登録するコマンド

コマンドを実行する

php artisan update:free-word-by-shop
1完了
2完了
3完了
処理完了。終了します。

`free_word`にデータが登録されているかを確認

free_wordに値が登録されていれば成功です！

mysql> select * from shops;
+----+--------------------+-----+-----------+---------------------+---------------------+--------------------------------+
| id | name               | age | gender_id | created_at          | updated_at          | free_word                      |
+----+--------------------+-----+-----------+---------------------+---------------------+--------------------------------+
|  1 | サンプル太郎       |  25 |         1 | 2022-01-08 01:54:17 | 2022-01-07 19:25:39 | 25 1 サンプル太郎 男性         |
|  2 | サンプル花子       |  30 |         2 | 2022-01-08 01:54:17 | 2022-01-07 19:25:39 | 30 2 サンプル花子 女性         |
|  3 | サンプル二郎       |  20 |         1 | 2022-01-08 01:54:17 | 2022-01-07 19:25:39 | 20 3 サンプル二郎 男性         |
+----+--------------------+-----+-----------+---------------------+---------------------+--------------------------------+
3 rows in set (0.00 sec)

おわりに

読んでいただきありがとうございます。
今回の記事はいかがでしたか？
・こういう記事が読みたい
・こういうところが良かった
・こうした方が良いのではないか
などなど、率直なご意見を募集しております。