search in a table and its relations in laravel-CodePudding

I need to search for the code in the appointment table OR the patient name which is the appointment's relation. here is the code I reached so far but it is not working:

$lab = Lab::select('id', 'code')
->Where('code', 'like', "%{$search_query}%")
->with(['patient' => function ($q) {
    $q->select('id', 'avatar', DB::raw('CONCAT(first_Name, " ", second_Name) AS name')
    ->where('name', 'like', "%{$search_query}%")
    ->orWhereRaw("concat(first_name, ' ', second_name) like '%$search_query%' ")
);}])
->limit(5)
->get();

CodePudding user response：

add use ($search_query) to your ::with() portion:

$lab = Lab::with(['patient' => function ($q) use ($search_query) {
        $q->select('id', 'avatar', DB::raw('CONCAT(first_Name, " ", second_Name) AS name')
            ->where('name', 'like', "%{$search_query}%")
            ->orWhereRaw("concat(first_name, ' ', second_name) like '%$search_query%' ")
        );
    }])
        ->select('id', 'code')
        ->Where('code', 'like', "%{$search_query}%")
        ->limit(5)
        ->get();

CodePudding user response：

You can use whereHas to make conditions on the Laravel relations like below:

$lab = Lab::select('id', 'code')
        ->Where('code', 'like', "%{$search_query}%")
        ->whereHas('patient', function ($q) use ($search_query){
            $q->where('patients.name', 'like', "%{$search_query}%");
        }) 
        ->limit(5)
        ->get();