Eloquent order by Many-To-Many relationship

Premise

While working on a project where I installed Spatie's Laravel-permission package I stumbled upon a problem when I had to put down a list of users orderd by their role.

This could have easily been solved by adopting one of these two approaches:

• add a role column to the User model
• add a role_id columen to the User model and have a separate Role model (one-to-many)

Both of these approaches would offer easy solutions when it comes to fetching all the users and ordering them by role.

Bonus: here you can find a very nice article written by Jonathan Reinink covering most use cases: Ordering database queries by relationship columns in Laravel

Back to me and my problems...in this project I neded all the additional features offered by Spatie's package, such as permissions and the possible relation between permissions and roles.

Important note: in this project users have only one role even though we use a many-to-many relationship.

Then how do we get to order an eloquent query with a many-to-many relationship ?

Not that obvious, but to avoid making multiple queries or additional sorting on the retrieved collection we just need to add two lines of codes and a pivot model.

First the full query example:

$employees = User::query()
	->role(['admin', 'employee'])
	->addSelect(['model_role_id' => ModelRole::select('role_id')->whereColumn('model_id', 'users.id')->take(1)])
	->orderBy(Role::select('name')->whereColumn('id', 'model_role_id')->take(1))
	->get();

Here we fetch all the admins and employees and order them by their role name.

Since it's a many-to-many relationship we can't do a simple:

$employees = User::query()
	->role(['admin', 'employee'])
	->orderBy('role.name')
	->get();

What we need to do first is to create a ModelRole pivot class and specify its table name:

use Illuminate\Database\Eloquent\Relations\Pivot;

class ModelRole extends Pivot
{
    protected $table = 'model_has_roles';
}

Then in our query we add this line:

$query->addSelect(['model_role_id' => ModelRole::select('role_id')->whereColumn('model_id', 'users.id')->take(1)])

Here we add a column model_role_id to our select clause, that reflects the role of our user, then used in this second line:

$query->orderBy(Role::select('name')->whereColumn('id', 'model_role_id')->take(1))

Here we order by the name of the role having as an id the model_role_id that we previously fetched. The ->take(1) method makes sure we receive a single instance of the corresponding role.

Voilà!