はじめに
本記事は以下の続編になります。
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)
おわりに
読んでいただきありがとうございます。
今回の記事はいかがでしたか？
・こういう記事が読みたい
・こういうところが良かった
・こうした方が良いのではないか
などなど、率直なご意見を募集しております。