Unable to create a search filter for roles, which displays all the users with the selected role - Laravel

Question

I have a belongsToMany eloquent relationship between App/Models/User and App/Models/Role. The relationship between the user and the role is stored in role_user pivot table. I am trying to create an advanced search filter, which will display me the list of users under the specific role criteria.

I have already done this for email criteria, i.e. the concerned user will be displayed, if the email of the registered user matches the input. This was easy to achieve as the email column exists in the users table itself.

How can I achieve the same for the role criteria, which will relate to the user through a pivot table, and will display the end number of users with the selected role.

App/Models/User

public function roles() {
    return $this->belongsToMany(Role::class);
}

App/Models/Role

public function users() {
    return $this->belongsToMany(User::class);
}

App/Http/Livewire/UserController

public $filters = [
    'email' => null,
    'role' => ''
];

public function render() {
    $users = User::query() 
        -> when($this -> filters['email'], fn($query, $email) => $query -> where('email', $email))
        -> when($this -> filters['role'], fn($query, $role) => $query -> with('roles') -> where('id', $role))
        -> search('name', $this -> search)
        -> paginate(5);
    $roles = Role::all();
    return view('livewire.user-controller', ['users' => $users, 'roles' => $roles]);
}

resources/views/livewire/user-controller

<!-- Advanced Search -->
<div>
    @if ($showFilters)
    <div>
        <div>
            <x-input.group inline for="filter-email" label="Email">
                <x-input.text wire:model.lazy="filters.email" id="filter-email" placeholder="Enter Email" />
            </x-input.group>
        </div>
        <div>
            <x-input.group inline for="filter-role" label="Role">
                <x-input.select wire:model.lazy="filters.role" id="filter-role" placeholder="Select Role">
                    @foreach($roles as $role)
                    <option value="{{ $role->id }}">{{ $role->name }}</option>
                    @endforeach
                </x-input.select>
            </x-input.group>
        </div>
    </div>
    @endif
</div>

<!-- Users Table -->
<x-table>
    <x-slot name="head">
        <x-table.heading>
            <x-input.checkbox />
        </x-table.heading>
        <x-table.heading>ID</x-table.heading>
        <x-table.heading>Name</x-table.heading>
        <x-table.heading>Email</x-table.heading>
        <x-table.heading>Role</x-table.heading>
    </x-slot>

    <x-slot name="body">
        @foreach($users as $key => $user)
        <x-table.row wire:loading.class.delay="opacity-50">
            <x-table.cell wire:key="row-{{ $user->id }}">
                <x-input.checkbox wire:model="selected" value="{{ $user->id }}" />
            </x-table.cell>
            <x-table.cell>{{ $users->firstitem() + $key }}</x-table.cell>
            <x-table.cell>{{ $user->name }}</x-table.cell>
            <x-table.cell>{{ $user->email }}</x-table.cell>
            <x-table.cell>
                @foreach($user->roles as $role)
                {{ $role->name }}
                @endforeach
            </x-table.cell>
        </x-table.row>
        @endforeach
    </x-slot>
</x-table>

You can use whereHas with a callback function to filter query results based on the relationship. laravel.com/docs/8.x/… — miken32
@miken32 Please can you be more descriptive, can you assist me with the code, as I am stuck at the same place since 4 days now, I know it might be silly, but I'm very much new to Laravel. I appreciate your help! — ToxifiedHashkey

Donkarnash Donkarnash · Accepted Answer · 2020-12-01T22:53:25

To retrieve all users who have a role with an id of 5

//Say this role id (5) is received as a result of user selecting a role from select
$roleId = 5; 

$users = User::whereHas('roles', function($query) use($roleId) {
    return $query->where('id', $roleId);
});

Thanks for the answer, can you help me by getting it to work in the query format. User::query()->when($this -> filters['role'], fn($query, $role) => $query -> with('roles') ->where('id', $role)) as I need to bind it along with the state of the function to detect the request when($this -> filters['role']. Thank you for your time.

User::query()
  ->when(
    $this->filters['role'], 
    fn($query, $role) => $query->whereHas(
        'roles', 
        fn($query) => $query->where('id', $role)
    )
)

Unable to create a search filter for roles, which displays all the users with the selected role - Laravel

1 Answers