I have User Model with Roles relationships like so :
public function users(){
return $this->belongsToMany(User::class)->withTimestamps();
}
And Role Model with user relationships :
public function roles(){
return $this->belongsToMany(Role::class)->withTimestamps();
}
And of course Pivot Table "role_user" contains id, user_id, role_id and timestamps
I try to get users ordered By roles name like so :
$users = App\Models\User::with('roles')->orderBy('roles.name', 'desc')->get();
but i have this error :
Illuminate\Database\QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles.name' in 'order clause' (SQL: select * from `users` where `users`.`deleted_at` is null order by `roles`.`name` desc)
http://localhost:8000/users
Please, some helps .
Older question I stumbled upon, but be aware that when using ->with(), the Query Builder doesn't perform any kind of join ... query, so roles.name is indeed not available as you currently have your query written.
If you want to order by roles.name in SQL, then you need to manually join roles to users (via the role_user pivot table), then do orderBy('roles.name'):
$users = App\Models\User::select('users.*')
->with('roles')
->join('role_user', 'role_user.user_id', '=', 'users.id')
->join('roles', 'role_user.role_id', '=', 'roles.id')
->orderBy('roles.name', 'DESC')
->get();
Note: I still used ->with('roles') so that $user->roles is still available as a Collection.
Documentation can be found here:
https://laravel.com/docs/7.x/queries#joins (your version)
https://laravel.com/docs/10.x/queries#joins (current version)
Additionally, while sortBy() is another valid option (as illustrated by the other answer here), using Model::get()->sortBy(...) for larger datasets is a potential memory/timeout issue, as it requires loading every single record from your database table into PHP's memory, then using PHP logic for sorting.