Question 245030 in Laravel Question posted in
The official Laravel Documentation can be found here.

laravel search in other table on json column by relation

i try make code for search and this is my code:

$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();

but when I send plan_name in request the code filters by service name and not by plan name

i tried when is send the plan name in the request The code filter data by plan name but it does not work

Tags:

2

Answers


  1. 0 
    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.'%');
    });
}

    FYI: You can write this code better than this. This is just fixing your code only.

    Login or Signup to reply.
  2. 0

    In several places you have used ->whereRaw("... LIKE '%$request->service_name%' ") this must be replace with ->whereRaw("... LIKE '%{$request->service_name}%' "), notice the {...} around {$request->...}. What you were doing it converts the whole $request to string but you only want a particular param like service_name.

    By the way I have to point out this implementation is extremely bad and should be prohibited because you are exposing your system to SQL injection, meaning an attacker could easily read/destroy your database. You should see Laravel docs on how to setup query binding, it is pretty easy.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search