0

How can I order a result set by something on its relationship?

I am trying to get the Eloquent equivalent of this:

SELECT * FROM users INNER JOIN roles ON users.role_id = roles.id ORDER BY roles.label DESC
Here is what I am trying (based on the documentation):

$order = 'desc';
$users = User::with(['role' => function ($q) use ($order) {
            $q->orderBy('label', $order);
        }])->paginate(10);

but it doesn't order them properly. What am I doing wrong?

EDIT: Not sure if showing the model relationship is relevant but here it is:

 public function role()
    {
        return $this->belongsTo(Role::class);
    }
Yasin Patel
  • 5,624
  • 8
  • 31
  • 53
Milan R Dhameliya
  • 89
  • 1
  • 1
  • 8
  • 1
    Does this answer your question? [Order by relationship column](https://stackoverflow.com/questions/38261546/order-by-relationship-column) – Adam Nov 13 '19 at 09:18
  • @Adam no man!.....I am not want to join my role table......i have to use in with()......so that above ans is not helpful for me – Milan R Dhameliya Nov 14 '19 at 04:10
  • But the answer that you checked is identical to https://stackoverflow.com/a/38262311/2311074 – Adam Nov 14 '19 at 08:56

1 Answers1

1

You should do it with join

User::with('role')->join('role', 'role.id', '=', 'users.id')->orderBy('role.label', $order)->paginate(10);
Poldo
  • 1,924
  • 1
  • 11
  • 27