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

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.

Advanced Search - Role Criteria

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>

tykus

5 years ago

Best Answer

Level 104

Use a whereHas Builder method on the roles relation - I have expanded so you can see clearly where the inner short Closure is located.

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

Aside 1: you do not pass $email and $role into the short closure as you have done, it is already available from the outer scope.

Aside 2: do you really write your code with spaces between keywords and object operator?

2 likes

ToxifiedM

5 years ago

Level 1

Thanks a lot, its working perfectly. Actually I was following the livewire screencast, episodes from data table series. As I'm new to Laravel, I'm just trying to learn stuff. :) Thanks alot for your time!

Please or to participate in this conversation.