【実践】Laravel+MySQLの全文検索機能実践

# はじめに


`LIKE`検索は全ての行に対して条件に合致するかを判定するのでデータが増えるにつれ検索速度が遅くなる場合があるようです。

この記事では、MySQLの全文検索機能を導入した方法についてハンズオン形式で紹介させていただきます。

https://dev.mysql.com/doc/refman/8.0/ja/fulltext-search-ngram.html

完成系のリポジトリ
@ucan-labさんの記事を参考にLAMP環境を構築いたしました🙇‍♂️

https://qiita.com/ucan-lab/items/56c9dc3cf2e6762672f4

https://github.com/nao-haba-dev/ngram-docker-laravel

続編

https://qiita.com/naoki-haba/items/f93ea46e92433c4f1152

# 事前準備
- GitHub・Dockerが利用できる準備をお願いします

https://github.com

https://qiita.com/ucan-lab/items/aadbedcacbc2ac86a2b3

- MAC

https://docs.docker.com/desktop/mac/install/

- Windows

https://docs.docker.com/desktop/windows/install/


## リポジトリをクローン

```bash
git clone https://github.com/nao-haba-dev/ngram-docker-laravel.git
```

## セットアップ

```bash
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
```

http://localhost:8080/

トップページが表示されていればセットアップ完了です
![スクリーンショット 2022-01-01 22.15.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/555632/33e6454c-a23a-d0ee-d719-c36f3fd37688.png)

# 今回のハンズオンのゴール
- 全文検索用のDDLを作成
- 検索ロジックを実装
- 全文検索を実践

# 【実践】Laravel+MySQLで全文検索機能を導入してみる

## bootstrapのスカフォールドを生成

https://readouble.com/laravel/8.x/ja/frontend.html?header=%25E3%2582%25A4%25E3%2583%25B3%25E3%2583%2588%25E3%2583%25AD%25E3%2583%2580%25E3%2582%25AF%25E3%2582%25B7%25E3%2583%25A7%25E3%2583%25B3

```bash:root@xxxx:/work#
composer require laravel/ui
php artisan ui bootstrap
npm install
npm run dev
```

## 全文検索用のModelとmigrationを生成
https://readouble.com/laravel/8.x/ja/eloquent.html

```bash:root@xxxx:/work#
php artisan make:model Shop --migration
```

## Model定義

```php:backend/app/Models/Shop.php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Shop extends Model
{
    use HasFactory;

    /**
     * モデルに関連付けるテーブル
     *
     * @var string
     */
    protected $table = 'shops';

    /**
     * 複数代入可能な属性
     *
     * @var array
     */
    protected $fillable = ['name', 'age', 'gender_id'];
}
```

## migration定義

```php:backend/database/migrations/2022_01_01_132402_create_shops_table.php
<?php

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

class CreateShopsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('shops', function (Blueprint $table) {
            $table->bigIncrements('id')->comment('店ID');
            $table->string('name', 255)->comment('従業員');
            $table->unsignedInteger('age')->comment('年齢');
            $table->smallInteger('gender_id')->comment('性別');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
        });
        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");
        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::dropIfExists('shops');
    }
}

```

### migration定義の補足

LaravelのクエリビルダでSQL文を直接実行します

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

```php:backend/database/migrations/2022_01_01_132402_create_shops_table.php
DB::statement()
```

`ALTER TABLE shops ADD free_word TEXT`:TEXT型のカラムを追加　
`as`:`as`以降の値を追加して生成します

https://dev.mysql.com/doc/refman/8.0/ja/alter-table-generated-columns.html

```php:backend/database/migrations/2022_01_01_132402_create_shops_table.php
"ALTER TABLE shops ADD free_word TEXT as xxx STORED
```

`concat`:複数の文字列を連結します
`IFNULL`:1番目に指定した引数の値が NULL だった場合に 2番目の引数の値を返します

```php:backend/database/migrations/2022_01_01_132402_create_shops_table.php
(concat(IFNULL(age,''),' ')
```

`case`:各 WHEN 句内の when_value 式のいずれかに等しくなるまで、それらの式と比較されます
`when`:条件
`then`:条件に一致した場合の処理

```php:backend/database/migrations/2022_01_01_132402_create_shops_table.php
(case gender_id when 1 then '男性' when 2 then '女性' else '' end), ' '
```

## migration

```bash:root@27c72a17e24e:/work#
php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (60.12ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (42.83ms)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (63.83ms)
Migrating: 2019_12_14_000001_create_personal_access_tokens_table
Migrated:  2019_12_14_000001_create_personal_access_tokens_table (84.70ms)
Migrating: 2022_01_01_132402_create_shops_table
Migrated:  2022_01_01_132402_create_shops_table (249.35ms)
```

## seederを定義

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

```php: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);
    }
}
```

## Seeding
```bash:root@27c72a17e24e:/work#
php artisan db:seed --class=DummyShopsSeeder
Database seeding completed successfully.
```

## Seeder結果

### dbコンテナに入る

```bash:root@27c72a17e24e:/work#
docker-compose exec db bash 
```

### mySQL接続

```bash:bash-4.4#
bash-4.4# mysql -u root -p
# password と入力してEnter
Enter password: 
```

## 登録情報を参照

```bash:mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel_local      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

use laravel_local;

 show tables;
+-------------------------+
| Tables_in_laravel_local |
+-------------------------+
| failed_jobs             |
| migrations              |
| password_resets         |
| personal_access_tokens  |
| shops                   |
| users                   |
+-------------------------+

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

## Routeを定義

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

```php:backend/routes/web.php
<?php

use Illuminate\Support\Facades\Route;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Route::get('/admin', [\App\Http\Controllers\ShopController::class, 'index']);
```

## Controllerを定義

- 今回は`Controller`にビジネスロジックを定義していますが、`useCase層`等に分離させた方がいいです


https://zenn.dev/mpyw/articles/ce7d09eb6d8117

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

```php:backend/app/Http/Controllers/ShopController.php
<?php

namespace App\Http\Controllers;

use App\Models\Shop;
use Illuminate\Contracts\Foundation\Application;
use Illuminate\Contracts\View\Factory;
use Illuminate\Contracts\View\View;
use Illuminate\Http\Request;

class ShopController extends Controller
{
    /**
     * 検索
     * @param Request $request
     * @return Application|Factory|View
     */
    public function index(Request $request)
    {
        $query = Shop::query();
        $freeWord = $request->input('free_word');

        if ($freeWord) {
            $query->whereRaw("match(`free_word`) against (? IN BOOLEAN MODE)", $freeWord);
        }

        $shops = $query
            ->select(['id', 'name', 'age', 'gender_id'])
            ->paginate(20, ['*'], 'page', 1);

        $param = collect($request->input());
        $shops->appends($request->input());

        return view('index', ['shops' => $shops, 'parameters' => $param]);
    }
}
```

## Controllerを定義:補足

`whereRaw`:クエリビルダに直接SQL文を指定します(必ずプリペアドステートメント機能を利用してください）


https://e-words.jp/w/%E3%83%97%E3%83%AA%E3%83%9A%E3%82%A2%E3%83%89%E3%82%B9%E3%83%86%E3%83%BC%E3%83%88%E3%83%A1%E3%83%B3%E3%83%88.html

:::note alert
rawメソッドはクエリを文字列として挿入するため、SQLインジェクションの脆弱性を生まないように十分気をつけてください。
:::

https://readouble.com/laravel/7.x/ja/queries.html

https://dev.mysql.com/doc/refman/8.0/ja/fulltext-boolean.html

```php:backend/app/Http/Controllers/ShopController.php
$query->whereRaw("match(`free_word`) against (? IN BOOLEAN MODE)", $freeWord);
```

## Viewの定義

```php:backend/resources/views/index.blade.php
<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>Laravel</title>
    <link href="{{ asset('css/app.css') }}" rel="stylesheet">
    <script src="{{ asset('js/app.js') }}"></script>
</head>
<body>
<div class="container">
    <div class="card center-block">
        <div class="card-header">
            Featured
        </div>
        <div class="card-body">
            <form action="/admin" method="Get">
                <div class="mb-2">
                    <label for="free_word" class="form-label">全文検索</label>
                    <input type="text" class="form-control" name="free_word" id="free_word"
                           value="{{$parameters->get('free_word')}}">
                </div>
                <input type="submit" class="btn btn-primary" value="検索">
                <a href="/admin" class="btn btn-secondary">検索条件クリア</a>
            </form>
        </div>
    </div>
    <br>
    <div>
        <div>検索結果は{{$shops->total()}}件です。</div>
        <div>
            <table class="table">
                <thead>
                <tr>
                    <th>ID</th>
                    <th>名前</th>
                    <th>年齢</th>
                    <th>性別</th>
                </tr>
                </thead>
                <tbody>
                @foreach($shops as $shop)
                    <tr>
                        <td>{{$shop->id}}</td>
                        <td>{{$shop->name}}</td>
                        <td>{{$shop->age}}</td>
                        <td>{{$shop->gender_id ===1 ? '男性':'女性'}}</td>
                    </tr>
                @endForeach
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>
```

`男性 2`で検索すると完全一致でないものも検索してくれます!
また`Index`を利用して検索してくれるので`LIKE`検索に比べてパフォーマンスが高いようです

https://tech.bita.jp/article/4

![スクリーンショット 2022-01-02 1.30.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/555632/d5b6fab1-c207-2fa8-b3a8-a649a0fd5770.png)

# おわりに
今回はMySQLの全文検索機能+Laravelについて紹介させていただきました。

Laravelのクエリビルダーを使用するとSQLを書かなくてもいい感じにやってくれる反面、特殊な対応が必要な場合は`SQL`の知識・経験が必要なので日々勉強の毎日です。。。
この記事が誰かのお役に立てば幸いです