cannot search column with alias

[abanghendri]

Hi, I have a query like this

 public function query(): Builder
    {

        return  User::query()->selectRaw('users.id as id, 
                                          users.name,
                                          users.NIP as NIP,
                                          users.email as email, 
                                          users.wa as wa, 
                                          users.created_at as created_at, 
                                          roles.name as role,
                                          role_groups.group')
                        ->join('model_has_roles','model_has_roles.model_id','=','users.id')
                        ->join('roles','roles.id','=','model_has_roles.role_id')
                        ->leftJoin('role_groups','roles.role_group_id','=','role_groups.id');
    }

and when I search something, I got this error:

Illuminate\Database\QueryException

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'role' in 'where clause' (SQL: select count(*) as aggregate from `users` inner join `model_has_roles` on `model_has_roles`.`model_id` = `users`.`id` cross join `roles` on `roles`.`id` = `model_has_roles`.`role_id` left join `role_groups` on `roles`.`role_group_id` = `role_groups`.`id` where (`users`.`name` like %sp% or `users`.`NIP` like %sp% or `users`.`email` like %sp% or `users`.`wa` like %sp% or `role` like %sp% or `group` like %sp%) and `users`.`deleted_at` is null) (View: E:\laragon\www\pln\resources\views\livewire\user\user.blade.php)

https://pln.test/user?filters%5Bsearch%5D=sp&sorts%5Brole%5D=asc 

This is my data looks like, and I want to search everything, all works except role, when I don't use searchable() on role column, it works fine

I know there's no 'role' column in those tables, but I have to search roles.name which has the same field name with users table

[rizkhal]

Can you paste your columns() here? It's because the role column does't have right name, you need to custom searcahble on roles column..

[SPQRBrutus]

That is all datatables problem with raw query. You need to do something like this:

Column::make('Role')
   ->searchable(function(Builder $query, $term) {
     return $query->orWhere('roles.name', 'like', '%' . trim($term) . '%');
   });

Just use custom search and add full field name. It should work.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.