John Davidson

Message:

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?

---

My Answer:
You can achieve this by using a closure inside the whereHas method to group your OR conditions for the first_name, last_name, and email fields. Here's how you can modify your query:

php
$users = Staff::whereHas('coporate', function ($q) use ($company_code) {
$q->where('company_code', $company_code);
})
->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 . '%');
})
->get();


This way, the whereHas method will filter the results based on the company_code, and the closure inside the where method will handle the OR conditions for the first_name, last_name, and email fields.

I hope this helps! Let me know if you have any further questions.