orWhere in Laravel misbehaving when fetching data from relationship-CodePudding

I have this controller method that am using to search users in a given user group using email and name. I have two groups customer and staff, when I search staff it returns customers too.

Here is the controller code

   public function index(Request $request, UserGroup $group) { //group=staff
        $users = $group->users();

        if ($request->has('search') && $request->search) {
            $users->where('name', 'LIKE', '%' . $request->search . '%')
                ->orWhere(function ($builder) use ($request) {
                    $builder->where('email', 'LIKE', '%' . $request->search . '%');
                });
        }

        return response()->json($users->get());

   }

Inside the UserGroup model here is the implementation of the code

class UserGroup extends Model{

    //...
   
    public function users(): BelongsToMany | User | Collection
    {
        return $this->belongsToMany(User::class, 'user_user_group');
    }

   //...

}

All I want is users with UserGroup staff and has name or email as typed in the search key. Instead, the system is returning even users who do not have the staff user group.

What am I doing wrong?

CodePudding user response：

You are doing it in the wrong way in writing the where clause. When you use orWhere outside, it tells laravel query to join the users table on the groups table and the first condition or the second condition. It should be join on group table and (the first condition or the second condition). It should wrap in the round brackets when it executes the query.

Your query should be:

    $users = $group->users();

    if ($request->has('search') && $request->search) {
        $users->where(function ($builder) use ($request) {
            $builder->orWhere('name', 'LIKE', '%' . $request->search . '%');
            $builder->orWhere('email', 'LIKE', '%' . $request->search . '%');
        });
    }

I hope this will help you. For more information, I would suggest to read the laravel official documentation. Laravel Query Builder

CodePudding user response：

You can also query this from the User::class perspective

User::query()
      ->where("name", "LIKE", "%{$request->search}%")
      ->orWhere("email", "LIKE", "%{$request->search}%")
      ->where(function(Builder $query) {
            $query->whereRelation('#group', '#groupColumn','staff')
      })->get();

This query will fetch the users that has the searched name or email with the group of staff.

I don't know the relationship of your usergroup and users just change the word with # to match the yours.

#group = relation of the UserGroup and User just like the users() to your UserGroup::class.

#groupColumn = column the staff belongs to.

Hope it helpful.