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?

Is your table named item or items? My guess is that your relationship is called item but the table is items so it should be `orderBy('items.type')` — rypskar, Jul 08 '16 at 07:55
I tried both `Column not found: 1054 Unknown column 'items.type' in 'order clause'` — TheUnreal, Jul 08 '16 at 07:58
Look at your query log, your main query and relationship are separate from each other, they don't cross over. You would need to do some type of join to be able to order by another table like that more than likely. — lagbox, Jul 08 '16 at 08:04
It is probably because `with()` are using eager loading, which turns this into two queries. Try to use `join()` instead of `with()` — rypskar, Jul 08 '16 at 08:05

score 64 · Accepted Answer · edited Nov 27 '19 at 15:30

64

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.*').

edited Nov 27 '19 at 15:30

N69S

16,110
3
22
36

answered Jul 08 '16 at 08:36

TheUnreal

23,434
46
157
277

6

Note that if you want the joined model to be returned as part of the results you can still use `with` as normal. In this example, `->with('item')`. – Daniel Buckmaster Mar 17 '18 at 01:52
This will not work with morph relationships :( @Repox answer is better – Aleksandrs Dec 15 '19 at 07:10
@Aleksandrs the answer of Repox doesn't work on pagination – Angus Simons Nov 18 '22 at 10:05

Repox · Answer 2 · 2019-01-23T08:47:54.580

21

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

edited Jan 23 '19 at 08:47

answered Jul 08 '16 at 08:08

Repox

15,015
8
54
79

For somes reasons, the returned data is an object and not an array of objects, like returned and the orginal query – TheUnreal Jul 08 '16 at 08:22
This is what I needed, order the collection through the id of the relationship! – Eleazar Resendez May 16 '18 at 14:32
2

@TheUnreal you need to call ->values() after ->sortBy() – rambii May 21 '18 at 14:40
3

Shorthand for sorting for a value would be ->sortBy('item.type') – rambii May 21 '18 at 14:40
1

This sorts the fetched collection items on memory. If you want sort operation on your DB table, you should call sortBy before get() method. – Onur Demir Mar 19 '19 at 08:50
20

For those who use `skip` and `limit` for **pagination**, this will lead to wrong results. – molerat Apr 09 '19 at 06:54
I like this solution, but if the query return a lot of rows will impact so much the performance? – Marc Pont Jul 21 '20 at 13:46
@Onur Demir sortBy is a Collection method, you can't call it on QueryBuilder. – jartaud Jul 26 '22 at 21:07
@jartaud I meant orderBy, sorry. – Onur Demir Aug 01 '22 at 13:25
SortBy operate in Collection returned by the get method. It's not an efficient solution. I think is better the solution with the join and then an orderBy or with the aggregate method "withAggregate('item','type')" and then use the orderBy on "item_type" – jiappo Jun 13 '23 at 14:52

score 15 · Answer 3 · edited Jul 07 '23 at 03:33

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.

score 6 · Answer 4 · answered Mar 23 '22 at 10:47

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

score 2 · Answer 5 · answered Mar 12 '22 at 06:13

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

score 1 · Answer 6 · answered Feb 21 '20 at 14:13

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

note that this will return everything as a collection then sort it which might be an issue if you have a large dataset — ctf0, May 25 '20 at 12:55
Also serverside pagination will not work as this approach sorts only loaded items (per page in case of pagination).. — Dirk Jan, Apr 19 '23 at 09:42

score 1 · Answer 7 · answered Jan 17 '23 at 20:26

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

score 0 · Answer 8 · answered Aug 18 '22 at 21:43

0

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

answered Aug 18 '22 at 21:43

saifulmasud

9
2

1

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? That's especially important here as there are several established answers that have been validated by the community. – Jeremy Caney Aug 19 '22 at 00:15

score 0 · Answer 9 · answered Jun 01 '23 at 13:50

$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

Order by relationship column

9 Answers9

Linked