How to use orWhere in whereHas in Laravel

Question

I have three tables user, role, permission, user hasMany role, role hasMany permission, I want to find out user who has permission I gave, therefore I use whereHas

$user = User::whereHas('roles', function($q) {
    $q->whereHas('permissions', $function($q) {
        $q->where('name', $value);
    });
});

The result is correct, but if I want to search user by more conditions, I give the other permission value and use orWhere, it response all user who have any permissions, how to fix it?

$user = User::whereHas('roles', function($q) {
    $q->whereHas('permissions', $function($q) {
        $q->where('name', $value)->orWhere('name', $value2);
    });
});

Answer 1

I ran into the same problem: I wanted to implement a table search for companies. The search string shall be searched within the companies name and also in the associated contacts forename and lastname. My try was like this:

Company::where('name', 'like', '%'.$searchstring.'%')
         ->orWhereHas('contacts', function($q) use ($searchstring) { 
                                    $q->where('forename', 'like', '%'.$searchstring.'%')
                                    ->orWhere('lastname', 'like', '%'.$searchstring.'%');
                                   });

The result was similar to the one described by you: for certain searchstrings ALL companies were returned.

Only workaround I was able to figure out was separating where and orWhere of the orWhereHas-closure into TWO orWhereHas-closures. Like this:

Company::with('mainContact', 'contacts')
         ->where('name', 'like', '%'.$searchstring.'%')
         ->orWhereHas('contacts', function($q) use ($searchstring) { 
                                    $q->where('forename', 'like', '%'.$searchstring.'%');
                                  })
         ->orWhereHas('contacts', function($q) use ($searchstring) { 
                                    $q->where('lastname', 'like', '%'.$searchstring.'%');
                                  });

I am not sure if this generates a very expensive SQL-query, but it works. Any improvements of my snippet are very welcome.