Laravel 8 数据库多词模糊搜索优化指南

$query = Immovable::query()
    ->leftJoin('streets', 'streets.gus_id', '=', 'immovables.street_gus_id')
    ->select(
        'immovables.id',
        'immovables.street_gus_id',
        'immovables.building_number',
        'immovables.apartment_number',
        'streets.name as street_name'
    );

if (request()->has('search')) {
    $searchTerm = "%" . request()->search['value'] . "%";
    $query->where('streets.name', 'like', $searchTerm)
          ->orWhere('immovables.community', 'like', $searchTerm)
          // ... 其他 orWhere 条件
          ->orWhere('immovables.name', 'like', $searchTerm)
          ->orWhere('immovables.surname', 'like', $searchTerm);
}
$results = $query->get();

use Illuminate\Http\Request;
use App\Models\Immovable; // 假设您的模型名为 Immovable

class SearchController extends Controller
{
    public function search(Request $request)
    {
        $query = Immovable::query()
            ->leftJoin('streets', 'streets.gus_id', '=', 'immovables.street_gus_id')
            ->select(
                'immovables.id',
                'immovables.street_gus_id',
                'immovables.building_number',
                'immovables.apartment_number',
                'streets.name as street_name'
            );

        if ($request->has('search') && !empty($request->search['value'])) {
            // 将搜索值按空格拆分成多个单词
            $searches = explode(" ", $request->search['value']);

            // 使用一个闭包来分组所有的 OR 条件，确保逻辑清晰
            $query->where(function ($q) use ($searches) {
                foreach ($searches as $search) {
                    $searchTerm = "%" . $search . "%"; // 为每个单词构建模糊匹配字符串

                    // 对每个单词在所有相关字段中执行 OR 匹配
                    $q->orWhere('streets.name', 'like', $searchTerm);
                    $q->orWhere('immovables.community', 'like', $searchTerm);
                    $q->orWhere('immovables.city', 'like', $searchTerm);
                    $q->orWhere('immovables.building_number', 'like', $searchTerm);
                    $q->orWhere('immovables.granted_comments', 'like', $searchTerm);
                    $q->orWhere('immovables.inspections', 'like', $searchTerm);
                    $q->orWhere('immovables.oze_installations', 'like', $searchTerm);
                    $q->orWhere('immovables.pesel', 'like', $searchTerm);
                    $q->orWhere('immovables.name', 'like', $searchTerm);
                    $q->orWhere('immovables.surname', 'like', $searchTerm);
                    $q->orWhere('immovables.email1', 'like', $searchTerm);
                    $q->orWhere('immovables.email2', 'like', $searchTerm);
                    $q->orWhere('immovables.email3', 'like', $searchTerm);
                    $q->orWhere('immovables.phone1', 'like', $searchTerm);
                    $q->orWhere('immovables.phone2', 'like', $searchTerm);
                    $q->orWhere('immovables.phone3', 'like', $searchTerm);
                    $q->orWhere('immovables.description', 'like', $searchTerm);
                }
            });
        }

        $results = $query->get();

        return view('your.view', compact('results')); // 假设您将结果传递给视图
    }
}