Multiple search clause for many-to-many relationships in Laravel

Good day. I have this stuff that has been a little bit problematic for me in my application. I have a Staff model which has a many to many relationship with a Company model. Hence, I have a company_staff table.So, the companies have a unique identifier which is the company code. The Staff model has information such as first name, lastname, and email etc. Now, I want to perform a search operation using either the firstname, lastname or email, but fetching data for only the company with the unique code (i.e search only within that company). This is the query I came up with below:

$users = Staff::where('first_name', 'like', '%' . $request->search_term . '%')
            ->orWhere('last_name', 'like', '%' . $request->search_term . '%')
            ->orWhere('email', 'like', '%' . $request->search_term . '%')
            ->whereHas('coporate', function ($q) use ($company_code) {
                $q->where('company_code', $company_code);
            })->get();

I get results but this only returns all the result that match from my “staff” table, behaving as if the “company_code” part of the query does not exist at all. However, if I leave the query as :

$users = Staff::where('first_name', 'like', '%' . $request->search_term . '%')
            ->whereHas('coporate', function ($q) use ($company_code) {
                $q->where('company_code', $company_code);
            })->get();

I get the desired result. But, I want to be able to search with both last_name and email as well. Please, how do I go about this?

Answer

Try with this code

$users = Staff::where(function ($query) use($request) {
            $query->where('first_name', 'like', '%' . $request->search_term . '%')
                ->orWhere('last_name', 'like', '%' . $request->search_term . '%')
            ->orWhere('email', 'like', '%' . $request->search_term . '%');
        })->whereHas('coporate', function ($q) use ($company_code) {
                $q->where('company_code', $company_code);
            })->get();

For more : https://laravel.com/docs/6.x/queries#parameter-grouping

Advertisement

Answer