orderby related column

[constantinosergiou]

 $users = User::filtering($request)->select('*')->with('Role')->access($request)
            ->selectRaw('(SELECT roles.name FROM roles, role_users
                           WHERE users.id = role_users.user_id
                            AND role_users.role_id = roles.id LIMIT 1)
                           as rolename')
                           ->groupBy('id');

                $query = Search::new()
                 ->add($users,  ['first_name', 'last_name','role.slug'])
                ->paginate(\Request::get('per_page') ?: 30)->search($request['query']);

all orderBy working from the same table but when i try with 'rolename' i get this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rolename' in 'order clause' (SQL: select users.id as 0_user_key, users.updated_at as 0_user_order from `users` where `users`.`deleted_at` is null group by `id` order by `rolename` asc, COALESCE(0_user_order) asc limit 60 offset 0)

basically if you return $users with these return $users->get(); before the $query the sorting work perfect in all cases... but when goes to the $query is not working the 'rolename' @pascalbaljet could you please give a suggestion on this

[pascalbaljet]

I know this issue is a bit old, but could you share more details? Like the table layouts and the exact code that throws the exception?

[constantinosergiou]

@pascalbaljet sorry i fix it by doing it that:

            $users = $User::filtering($request)->select('*')
                        ->with('Role')
                        ->selectRaw('(SELECT roles.name FROM roles, role_users
                                       WHERE users.id = role_users.user_id
                                        AND role_users.role_id = roles.id LIMIT 1)
                                       as rolename')->groupBy('id')
                        ->when($orderBy, function($query) use($orderBy, $orderDirection){
                         if($orderBy == 'rolename') {
                             $query->orderByRaw('(SELECT roles.name FROM roles, role_users
                             WHERE users.id = role_users.user_id
                              AND role_users.role_id = roles.id LIMIT 1)'.$orderDirection);
                         }
                         else {
                             $query->orderBy($orderBy, $orderDirection);
                         }
                     });

                           $query = Search::new()
                           ->add( $users,  ['first_name', 'last_name','role.name','username'])
                          ->paginate(\Request::get('per_page') ?: 30)->search($request['query']);