Why doesn't my Laravel conditional whereHas query work?

Question

I am trying to create a conditional User search function. There are three filters that a user could select to search on:

user name
company name associated with user
role name associated with user

In the following example I have put all the filters in the array $searchFilters for simplicity.

I only want Laravel to search the filters that are present in the array.

The code

$q = request()->q;
$searchWildcard = '%' . $q . '%';
$searchFilters = ['name', 'roles', 'company'];

$users = User::when(in_array('roles', $searchFilters), function ($query) use ($searchWildcard) {
      $query->whereHas('roles', function($query) use ($searchWildcard) {
         $query->where('name', 'LIKE', $searchWildcard);
      });
   })->when(in_array('company', $searchFilters), function($query) use ($searchWildcard) {
      $query->whereHas('company', function($query) use ($searchWildcard) {
         $query->orWhere('name', 'LIKE', $searchWildcard);
       });
   })->when(in_array('name', $searchFilters), function($query) use ($searchWildcard) {
      $query->orWhere('name', 'LIKE', $searchWildcard);
});

The first part of the query works: It retrieves the roles based on the search query by the user, but it doesn't work for the company name or user name.

Edit

It only doesn't work for the company name.

Rwd · Accepted Answer · 2019-12-23 17:27:26Z

2

You orWhere condition is inside another where so I imagine it won't work as expected.

I would suggest changing your whereHas to orWhereHas and then changing the orWhere inside it to where:

$users = User::when(in_array('roles', $searchFilters), function ($query) use ($searchWildcard) {
    $query->whereHas('roles', function ($query) use ($searchWildcard) {
        $query->where('name', 'LIKE', $searchWildcard);
    });
})->when(in_array('company', $searchFilters), function ($query) use ($searchWildcard) {
    $query->orWhereHas('company', function ($query) use ($searchWildcard) {
        $query->where('name', 'LIKE', $searchWildcard);
    });
})->when(in_array('name', $searchFilters), function ($query) use ($searchWildcard) {
    $query->orWhere('name', 'LIKE', $searchWildcard);
});

answered Dec 23, 2019 at 17:27

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rwd Over a year ago

@BvdL Glad I could help!

Collectives™ on Stack Overflow

Why doesn't my Laravel conditional whereHas query work?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related