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 filter by service name and not filter by plan name
do you have some kind of debugger to show us what sql queries were executed?
or use DB::enableQueryLog() to turn on query log
DB::enableQueryLog();
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.'%');
});
}
dd(DB::getQueryLog());
and show us the dump.
or just $services->ddRawSql() if you're on L10 might work too
@s4muel i have this result
array:1 [
0 => array:3 [
"query" => "select * from services where exists (select * from services as laravel_reserved_0 inner join plan_services on laravel_reserved_0.id = plan_services.service_id where services.id = plan_services.service_id and json_unquote(json_extract(name, '$."en"')) Like ? and laravel_reserved_0.deleted_at is null) and services.deleted_at is null order by created_at desc"
"bindings" => array:1 [
0 => "%تسلا%"
]
"time" => 1.0
]
]
@luayjal although it shouldn't be like i am going to advise, but i am curious anyway: try specifying the table (plan_services.name->en) in the innermost where
if ($request->plan_name) {
$plan_name = $request->plan_name;
$services = $services->whereHas('plans', function ($q) use ($plan_name) {
$q->where('plan_services.name->en','like','%'.$plan_name.'%');
});
}
since you are using OR conditions in your query, try wrapping them in a single condition:
$services->where(function ($query) use ($request) {
$query->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%' ");
);
@krisi_gjika The problem is in this condition
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.'%');
});
}
@luayjal where is this name column coming from, services or plans?
Also try replacing your ->where('name->en','Like','%'.$plan_name.'%'); with ->whereRaw("CONVERT(JSON_EXTRACT(name, '$.en') using 'utf8') LIKE '%$plan_name%' ") like you have done for service_name
