In Laravel 7 How to orderBy Roles->name?

Question

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 .

score 0 · Accepted Answer · answered Jul 06 '23 at 19:58

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.

void · Answer 2 · 2020-06-02T15:24:34.330

-1

Try Like this:

$users = App\Models\User::with('roles')->get();
// order of the roles
$array = ['User','Admin','Doctor', 'Manager']; 
$sorted = $users->sortBy(function($model) use ($array) {
    return array_search($model['roles'], $array);
});

Hope it will help you!

edited Jun 02 '20 at 15:24

answered Jun 02 '20 at 12:34

void

915
8
20

what you want then? – void Jun 02 '20 at 12:51
Thanks for your answer, yes, it only works to order roles for the same user, but I want to sort all the users have user role then all users have a super role, then users have a role of doctor then users have admin role – Nouar Jun 02 '20 at 12:59
this is the result of your answer [Imgur](https://imgur.com/xIHwjXN) but i want to have order the table by Role column – Nouar Jun 02 '20 at 13:28
Ok, For the variable $array i used it like this $array = App\Models\Role::get('name')->toarray(); is this right ? What is $market ? – Nouar Jun 02 '20 at 15:15
sorry it will be `$user` that was mistake! – void Jun 02 '20 at 15:25

In Laravel 7 How to orderBy Roles->name?

2 Answers2