در قسمت قبل به شما وعده داده بودم که درمورد مرتب سازی نتیجه ها صحبت می کنیم. اما هنگام کار با برنامه به دو مورد جذاب بر خوردم و تصمیم گرفتم طی دو قسمت آن ها را با شما درمیان بگذارم. در حال حاضر جست و جوی سایت ایراد کوچکی دارد. اگر عبارت tim oreilly را جست و جو کنید هیچ نتیجه ای پیدا نمی شود. این در حالی است که اگر عبارت را به صورت tim o’reilly را جست و جو کنید یک نتیجه یافت می شود! این خوب نیست. کاربر های ما باید نام دقیق را وارد کنند تا نتیجه پیدا شود. نمونه های دیگری هم می توان برای این مسئله بیان کرد. اگر عبارت van gogh را جست و جو کنید نتیجه ای یافت نمی شود اما اگر vangogh را جست و جو کنید نتیجه پیدا می شود.
برای از بین بردن این مشکل من عبارت جست و جو شده و ستون مورد جست و جو را نرمال سازی می کنم. این کار توسط عبارت های با قاعده امکان پذیر است. باید توسط یک عبارت با قاعده کاراکتر هایی که alpha numeric نیستند را از عبارت جست و جو و ستون مورد جست و جو حذف کنیم.
ابتدا عبارت جست و جو را به این صورت نرمال سازی می کنیم:
$term = preg_replace('/[^A-Za-z0-9]/', '', $term) . '%';از تابع preg_replace استفاده کردیم تا به صورت خودکار تمام کاراکتر هایی که بین A تا Z بزرگ و کوچک یا عدد نیستند را حذف کنیم. اکنون باید ستون های دیتابیس را هم به شیوه ای بروز کنیم که نرمال سازی شوند در غیر این صورت نتیجه مطلوب بدست نمی آید. این کار را با نوشتن عبارت با قاعده در کوئری می توان انجام داد. با ستون نام شروع می کنیم:
->whereRaw("regexp_replace(first_name, '[^A-Za-z0-9]', '') like ?", [$term]) به جای where از whereRaw استفاده می کنیم تا بتوانیم کوئری مورد نظر را بنویسیم. سپس از تابع regexp_replace استفاده می کنیم تا کاراکتر های غیر alpha numeric را حذف کنیم. توجه داشته باشید با این که کوئری raw نوشته ایم اما هنوز عبارت جست و جو را به عنوان یک پارامتر bind شده می فرستیم تا از مشکلات SQL Injection احتمالی جلوگیری شود. دو ستون دیگر هم مانند همین کوئری بروز می کنیم. نتیجه نهایی مانند زیر می شود:
public function scopeSearch($query, string $terms = null)
{
collect(str_getcsv($terms, ' ', '"'))->filter()->each(function ($term) use ($query) {
$term = preg_replace('/[^A-Za-z0-9]/', '', $term) . '%';
$query->whereIn('id', function ($query) use ($term) {
$query->select('id')
->from(function ($query) use ($term) {
$query->select('id')
->from('users')
->whereRaw("regexp_replace(first_name, '[^A-Za-z0-9]', '') like ?", [$term])
->orwhereRaw("regexp_replace(last_name, '[^A-Za-z0-9]', '') like ?", [$term])
->union(
$query->newQuery()
->select('users.id')
->from('users')
->join('companies', 'companies.id', '=', 'users.company_id')
->whereRaw("regexp_replace(companies.name, '[^A-Za-z0-9]', '') like ?", [$term])
);
}, 'matches');
});
});
}اکنون اگر tim oreilly را هم جست و جو کنید، نتیجه نمایش داده می شود. اما اگر به زمان اجرای کوئری ها نگاه کنید متوجه می شوید که به بیش از 600 میلی ثانیه رسیده است.
با اضافه شدن این عبارت با قاعده به کوئری، MySQL دیگر نمی تواند از ایندکس ها استفاده کند. این اتفاق کاملا منطقی است. هر بار که عملیاتی را در کوئری هنگام خوانده داده ها می نویسیم، دیتابیس توانایی استفاده از ایندکس را از دست می دهد. می توانیم برای هر عبارت یا عملیات خاص یک ایندکس ایجاد کنیم. این کار را امتحان می کنیم.
به مایگریشن ها می رویم و یک ستون مجازی با عبارتی دقیقا مشابه چیزی که در کوئری نوشتیم، اضافه می کنیم و آن را ایندکس می کنیم. همچنین از آن جایی که ایندکس عادی ستون ها استفاده ای برای ما ندارد آن ها را حذف می کنیم. حذف ایندکس هایی که استفاده نمی کنیم کار خوبی است زیرا هر ایندکس فضای اضافی اشغال می کند و هنگام نوشتن داده ها، دیتابیس را درگیر می کند. بنابراین اگر ایندکسی را به هیچ شکلی نیاز ندارید آن را حذف کنید.
مایگریشن های ما به شکل زیر در می آیند:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateCompaniesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('companies', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('name_normalized')->virtualAs("regexp_replace(name, '[^A-Za-z0-9]', '')")->index();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('companies');
}
}
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->foreignId('company_id')->constrained('companies');
$table->string('first_name');
$table->string('first_name_normalized')->virtualAs("regexp_replace(first_name, '[^A-Za-z0-9]', '')")->index();
$table->string('last_name');
$table->string('last_name_normalized')->virtualAs("regexp_replace(last_name, '[^A-Za-z0-9]', '')")->index();
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}اکنون migration ها و seeder پروژه را دوباره اجرا می کنیم.
php artisan migrate:fresh --seedدر پایان اسکوپ جست و جو را تغییر می دهیم تا از ستون های مجازی ای که ایجاد کردیم استفاده کند.
public function scopeSearch($query, string $terms = null)
{
collect(str_getcsv($terms, ' ', '"'))->filter()->each(function ($term) use ($query) {
$term = preg_replace('/[^A-Za-z0-9]/', '', $term) . '%';
$query->whereIn('id', function ($query) use ($term) {
$query->select('id')
->from(function ($query) use ($term) {
$query->select('id')
->from('users')
->where('first_name_normalized', 'like', $term)
->orWhere('last_name_normalized', 'like', $term)
->union(
$query->newQuery()
->select('users.id')
->from('users')
->join('companies', 'companies.id', '=', 'users.company_id')
->where('companies.name_normalized', 'like', $term)
);
}, 'matches');
});
});
}اکنون اگر جست و جو را دوباره انجام دهید متوجه کاهش زمان اجرای کوئری خواهید شد.
استفاده از عبارت های با قاعده برای بهبود تجربه کاربر هنگام جست و جو راه کار مناسبی است. با استفاده از ایندکس و ستون مجازی می توان این کار را بدون از بین بردن بهره وری کوئری ها انجام داد. امیدوارم قسمت هایی که درمورد جست و جو منتشر شد برای شما مفید بوده باشد.
