Laravel拼写容错搜索策略：基于语音编码的优化实践

<?php

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

class AddMetaphoneColumnsToProductsTable extends Migration
{
    public function up()
    {
        Schema::table('products', function (Blueprint $table) {
            $table->string('name_metaphone')->nullable()->after('name');
            $table->string('description_metaphone')->nullable()->after('description');
            // 可以根据需要为这些新列添加索引以提高查询性能
            $table->index('name_metaphone');
            $table->index('description_metaphone');
        });
    }

    public function down()
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropIndex(['name_metaphone']); // 先删除索引
            $table->dropIndex(['description_metaphone']);
            $table->dropColumn('name_metaphone');
            $table->dropColumn('description_metaphone');
        });
    }
}

<?php

namespace App\Models;

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

class Product extends Model
{
    use HasFactory;

    protected $fillable = [
        'name',
        'description',
        // ... 其他字段
    ];

    // 当设置 name 属性时，自动生成 name_metaphone
    public function setNameAttribute($value)
    {
        $this->attributes['name'] = $value;
        $this->attributes['name_metaphone'] = $value ? metaphone($value) : null;
    }

    // 当设置 description 属性时，自动生成 description_metaphone
    public function setDescriptionAttribute($value)
    {
        $this->attributes['description'] = $value;
        $this->attributes['description_metaphone'] = $value ? metaphone($value) : null;
    }

    // 关联关系 (如果存在)
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function store()
    {
        return $this->belongsTo(Store::class);
    }
}

<?php

namespace App\Console\Commands;

use App\Models\Product;
use Illuminate\Console\Command;

class GenerateProductMetaphones extends Command
{
    protected $signature = 'products:generate-metaphones';
    protected $description = 'Generate metaphone codes for existing product names and descriptions.';

    public function handle()
    {
        $this->info('Starting to generate metaphone codes for products...');

        Product::chunk(100, function ($products) {
            foreach ($products as $product) {
                $product->name_metaphone = $product->name ? metaphone($product->name) : null;
                $product->description_metaphone = $product->description ? metaphone($product->description) : null;
                $product->saveQuietly(); // 使用 saveQuietly 避免触发模型事件和额外的更新
            }
            $this->output->write('.'); // 进度指示
        });

        $this->info("\nMetaphone codes generation completed.");
        return 0;
    }
}

<?php

namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Request;

class ProductSearchController extends Controller
{
    public function search(Request $request)
    {
        $keywords = $request->input('keywords');
        $products = Product::with(['category', 'store']);

        if ($keywords) {
            // 对关键词进行语音编码
            $keywordMetaphone = metaphone($keywords);

            $products->where(function ($query) use ($keywords, $keywordMetaphone) {
                // 优先进行精确匹配或传统模糊匹配
                $query->where('name', 'LIKE', '%' . $keywords . "%")
                      ->orWhere('description', 'LIKE', '%' . $keywords . '%');

                // 如果关键词编码不为空，则进行语音编码匹配
                if ($keywordMetaphone) {
                    $query->orWhere('name_metaphone', $keywordMetaphone)
                          ->orWhere('description_metaphone', $keywordMetaphone);
                }
            });
        }

        $results = $products->get();

        return view('products.search_results', compact('results', 'keywords'));
    }
}