Laravel order user by role

I have table users and pivot table role_user, not every user has a role. I need to make a query and get all users, and order them by roles, which join statement should I use then, how would that query look like then?
I need some similar query to this, but so that users with that have roles come first and not the ones that don’t have roles like now:

$users = DB::table('users')
                     ->leftJoin('role_user', '', '=', 'role_user.user_id')


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The following code should do the trick:

$users = DB::table('users')
  ->leftJoin('role_user', '', '=', 'role_user.user_id')
  ->orderBy(DB::raw('role_id IS NULL'))

This way you’ll first sort by role_id IS NULL, that will be 0 if user has role_id set and 1 if user has no role, so users with role set will go first. Then within each group users will be ordered by role_id.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x