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

You can use the withAggregate('relationship', 'column') function.

It creates an 'item_type' attribute to the UserItems instances which you can then target with orderBy('item_type').
It can also be used with pagination like limit(), skip() etc. as it's not using sortBy() after the DB call.

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

This way you can still use your relationships in the eloquent query and order the collection without making joins or orderByRaw() calls.

The resulting query will be something like:

select `users`.*, (select type from `items` where `items`.`user_id` = `users`.`id` limit 1) as `item_type` from `users` where `users`.`id` = 123 AND `users`.`quantity` > 0 order by item_type asc)

Answer 2

$items = UserItems::with('item')
        ->where('user_id','=',$this->id)
        ->where('quantity','>',0)
        ->with(['item' => function($q) {
          $q->orderBy('type', 'asc');
        ]})
        ->get();

Pass an array of relationships to the 'with' method where:

• the array key is a relationship name, in this case is 'item'
• the array value is a closure that adds additional constraints to the eager loading query

Answer 3

you can simply do it by

UserItems::with('item')
  ->where('user_id','=',$this->id)
  ->where('quantity','>',0)
  ->orderBy(
      Item::select('type') 
      ->whereColumn('items.useritem_id','useritems.id')
      ->take(1),'desc'
    )    
  ->get();

Answer 4

$users
->whereRole($role)       
->join('address', 'users.id', '=', 'address.user_id')  
->orderByRaw("address.email $sortType")    
->select('users.*')

Answer 5

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 6

For a one to many relationship, there is an easier way. Let's say an order has many payments and we want to sort orders by the latest payment date. Payments table has a field called order_id which is FK.

We can write it like below

$orders = Order->orderByDesc(Payment::select('payments.date')->whereColumn('payments.order_id', 'orders.id')->latest()->take(1))->get()

SQL Equivalent of this code:

select * from orders order by (
    select date
    from payments
    where order_id = payments.id
    order by date desc
    limit 1
) desc

You can adapt it according to your example. If I understood right, order's equivalent is user and payment's equivalent is item in your situation.

Further reading

https://reinink.ca/articles/ordering-database-queries-by-relationship-columns-in-laravel

Answer 7

I found another way of sorting a dataset using a field from a related model, you can get a function in the model that gets a unique relation to the related table(ex: table room related to room category, and the room is related to a category by category id, you can have a function like 'room_category' which returns the related category based on the category id of the Room Model) and after that the code will be the following:

Room::with('room_category')->all()->sortBy('room_category.name',SORT_REGULAR,false);

This will get you the rooms sorted by category name

I did this on a project where i had a DataTable with Server side processing and i had a case where it was required to sort by a field of a related entity, i did it like this and it works. More easier, more proper to MVC standards.

In your case it will be in a similar fashion:

User::with('item')->where('quantity','>',0)->get()->sortBy('item.type',SORT_REGULAR,false);

Answer 8

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 9

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;
        });