Order by relationship column

Question

I have the following query:

$items = UserItems::with('item')
        ->where('user_id','=',$this->id)
        ->where('quantity','>',0)
        ->get();

I need to order it by item.type so I tried:

$items = UserItems::with('item')
        ->where('user_id','=',$this->id)
        ->where('quantity','>',0)
        ->orderBy('item.type')
        ->get();

but I get Unknown column 'item.type' in 'order clause'

What I am missing?

Answer 1

join() worked fine thanks to @rypskar comment

$items = UserItems         ::where('user_id','=',$this->id)         ->where('quantity','>',0)         ->join('items', 'items.id', '=', 'user_items.item_id')         ->orderBy('items.type')         ->select('user_items.*') //see PS:         ->get(); 

PS: To avoid the id attribute (or any shared name attribute between the two tables) to overlap and resulting in the wrong value, you should specify the select limit with select('user_items.*').

Answer 2

Well, your eager loading is probably not building the query you're expecting, and you can check it by enabling the query log.

But I would probably just use a collection filter:

$items = UserItems::where('user_id','=',$this->id)         ->where('quantity','>',0)         ->get()         ->sortBy(function($useritem, $key) {           return $useritem->item->type;         }); 

Answer 3

I know it's an old question, but you can still use an "orderByRaw" without a join.

$items = UserItems
        ::where('user_id','=',$this->id)
        ->where('quantity','>',0)
        ->orderByRaw('(SELECT type FROM items WHERE items.id = user_items.item_id)')
        ->get();  

Answer 4

You can use withAggregate function to solve your problem

UserItems::withAggregate('item','type')
    ->where('user_id','=',$this->id)
    ->where('quantity','>',0)
    ->orderBy('item_type')
    ->get();