透過關聯，在其他表中搜尋Laravel的JSON列

Question

我嘗試編寫搜尋程式碼，這是我的程式碼：

$services = Service::query()->with('plans')->latest();


    if ($request->service_name) {
               $services = $services->whereRaw("CONVERT(JSON_EXTRACT(name, '$.ar') using 'utf8') LIKE '%$request->service_name%' ")
                ->orWhereRaw("CONVERT(JSON_EXTRACT(name, '$.en') using 'utf8') LIKE '%$request->service_name%' ")
                ->orWhereRaw("CONVERT(JSON_EXTRACT(name, '$.he') using 'utf8') LIKE '%$request->service_name%' ");
    }

        if ($request->plan_name) {
            $plan_name = $request->plan_name;
            $services = $services->whereHas('plans', function ($q) use ($plan_name) {
                $q->where('name->en','Like','%'.$plan_name.'%');
            });
       }

        return $services->get();

但是當我在請求中發送 plan_name 時，代碼會按服務名稱而不是計劃名稱進行過濾

我嘗試過何時在請求中發送計劃名稱代碼按計劃名稱過濾數據，但它不起作用

Answer 1

if ($request->service_name && !$request->plan_name) { // When only service name provided
    $services = $services->whereRaw("CONVERT(JSON_EXTRACT(name, '$.ar') using 'utf8') LIKE  '%$request->service_name%' ")
            ->orWhereRaw("CONVERT(JSON_EXTRACT(name, '$.en') using 'utf8') LIKE  '%$request->service_name%' ")
            ->orWhereRaw("CONVERT(JSON_EXTRACT(name, '$.he') using 'utf8') LIKE  '%$request->service_name%' ");
}

if ($request->plan_name && !$request->service_name) { // When only plan name provided
    $plan_name = $request->plan_name;
    $services = $services->whereHas('plans', function ($q) use ($plan_name) {
        $q->where('name->en','Like','%'.$plan_name.'%');
    });
}