Level 75
Jun 30, 2021
5
Level 2
How to filter users by user role
Hello Guy's!
I used package spatie/laravel-query-builder and I filtering and sorting query by default column by how I can filtering and sorting users by role name? This is not easy because role name is in colum roles but relation is stored in table model_has_roles. How make join for this ?
$allowedSortsName = [
'name', 'surname', 'email', 'short_description',
AllowedSort::custom('role.name', new GetUserRoleSortAction(), 'name')
];
$users = QueryBuilder::for(User::class)
->allowedFilters(['name', 'surname', 'email', 'short_description'])
->allowedSorts($allowedSortsName)
->get();
return UserResource::collection($users);
class GetUserRoleSortAction implements Sort
{
public function __invoke(Builder $query, $descending, string $property): Builder
{
$direction = $descending ? 'DESC' : 'ASC';
return $query
->select('users.*', DB::raw('model_has_roles.model_id as id'))
->join('model_has_roles', 'users.id', '=', 'model_has_roles.model_id')
->orderBy('roles.name', $direction);
}
}
Level 2
This work only for filter and If want user filter and sort need to make join tables
Level 75
And it's a problem to add join there?
QueryBuilder::for(User::join('table', 'etc'))
1 like
Level 3
You can try using
$query->whereHas('roles', function($q) {
$q->where('roles.name',$value);
})->get();
Ref: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence
1 like
Level 1
try this:
public function __invoke(Builder $query, $descending, string $property): Builder
{
$direction = $descending ? 'DESC' : 'ASC';
return $query
->select('users.*', 'roles.name')
->join('model_has_roles', 'users.id', '=', 'model_has_roles.model_id')
->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
->orderBy('roles.name', $direction);
}
Please or to participate in this conversation.