Level 14
This doesn't seem to be working
$users = User::with('status')->orderBy('status.descriptoin', 'asc')->get();
Apr 18, 2019
5
Level 14
How to sort by a related table's column?
I have two tables (users and statuses) that are related and I would like to sort them alphabetically by the user's status. I can't just sort them by users.status column because then it won't be in alphabetical order.
How could I achieve this if I'm able to get users with statuses like this?
$users = User::with('status')->get();
// This doesn't seem to be working
$users = User::with('status')->orderBy('status.descriptoin', 'asc')->get();
Users table
+----+------------+--------+
| id | username | status |
+----+------------+--------+
| 1 | john123 | 5 |
| 2 | billy_joe | 1 |
| 3 | martin_lee | 5 |
| 4 | two_cents | 2 |
| 5 | Lucy | 3 |
| 6 | Bill2 | 4 |
+----+------------+--------+
Statuses table
+----+-------------+
| id | description |
+----+-------------+
| 1 | VIP |
| 2 | Junior |
| 3 | Senior |
| 4 | Banned |
| 5 | New member |
+----+-------------+
Desired result (users sorted alphabetically by status)
+---------+------------+------------+
| user_id | username | status |
+---------+------------+------------+
| 6 | Bill2 | Banned |
| 4 | two_cents | Junior |
| 1 | john123 | New member |
| 3 | martin_lee | New member |
| 5 | Lucy | Senior |
| 2 | billy_joe | VIP |
+---------+------------+------------+
Level 67
Go the other way. Get statuses with user, and order by status.
Level 14
@CRONIX - That won't work because I mainly need users and I'm eager loading several related tables not just statuses.
Level 4
Try
$users = User::with('status')
->join('status', 'status.id', '=', 'users.status_id')
->orderBy('status.description')
->get();
https://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship
Let me know if this or any variation of this works. I can't test this right now.
Level 14
@MKBAT - Thanks, that did the trick! It turned out easier than I thought. I just needed left join in case status_id was null.
$users = User::with('status')
->leftJoin('status', 'status.id', '=', 'users.status_id')
->orderBy('status.description')
->get();
+---------+-------------+-----------------+
| user_id | username | status |
+---------+-------------+-----------------+
| 6 | hangman123 | Administrator |
| 10 | prince23 | Administrator |
| 4 | z-baller | Junior user |
| 3 | MartinZ | Moderator |
| 1 | john_ivy | New user |
| 2 | billy298 | New user |
| 5 | willybilly | New user |
| 7 | bookerdude | New user |
| 8 | anotheruser | Senior user |
| 9 | mominator2 | Senior user |
| 11 | Jacky_Blue | Winner of Month |
+---------+-------------+-----------------+
1 like
Please or to participate in this conversation.