Laravel Eloquent sort by relation table column

Question

I tried to sort products from shop_products table by pinned column from shop_products_options table:

$products = Shop\Product::with(['options' => function ($query) {

    $query->orderBy('pinned', 'desc'); 

}])->paginate(5);

I set relation in Shop\Product model:

public function options()
{
    return $this->hasOne('Shop\Options');
}

But products aren't sorted. I get a query that only works with shop_products_options table.

SELECT * FROM `shop_products_options` WHERE `shop_products_options`.`product_id` in ('8', '9', '10', '11', '12') ORDER BY `pinned` DESC

How to fix it?

Jarek Tkaczyk · Accepted Answer · 2016-06-20T03:43:32.290

81

Eager loading uses separate queries so you need join for this:

$products = Shop\Product::join('shop_products_options as po', 'po.product_id', '=', 'products.id')
   ->orderBy('po.pinned', 'desc')
   ->select('products.*')       // just to avoid fetching anything from joined table
   ->with('options')         // if you need options data anyway
   ->paginate(5);

SELECT clause is there in order to not appending joined columns to your Product model.

edit: as per @alexw comment - you still can include columns from joined tables if you need them. You can add them to select or call addSelect/selectRaw etc.

edited Jun 20 '16 at 03:43

answered May 08 '14 at 10:27

Jarek Tkaczyk

78,987
25
159
157

Perfect - Also, without the select, the `shop_products_options.id` will overwrite the value of the `products.id` – Gravy Jan 16 '15 at 15:50
@Gravy Exactly, joined table fields will override all the fields from the main table having the same name. – Jarek Tkaczyk Jan 16 '15 at 16:43
Thanks Jarek, you've helped me solve to tackle a similar issue I was facing in which I needed to sort `Broadcasts` by their related `Media` objects (which could be Youtube or Vimeo videos) and I wanted to sort them by the amount of likes they had gotten. – Mirage Mar 18 '16 at 15:37
It's also worth noting that you can use `addSelect` instead of `select` if you have a more complex query, with columns from other tables that you *do* want to fetch. – alexw Jun 20 '16 at 02:22
@alexw sure, that's true. however the question is about ordering by relation, so more relevant here is how to avoid unexpected behaviour, that is including (and probably overlapping) columns from joined tables. – Jarek Tkaczyk Jun 20 '16 at 03:42
I think eloquent should evolve in the return from both methods(joins and eager). They return a totally different structure. Eagle returns the relations as attributes of the main model meanwhile the join approach brings joined tables attributes in the same level of the main table attributes. Right now you would need to manually structure that. – Murilo Mar 15 '17 at 17:47

fico7489 · Answer 2 · 2017-12-17T19:08:34.853

You can't sort by related table column without the manually joining related table. Jarek answer is correct but this could be really awkward :

1.The first problem is that you need to worry about select.

->select('products.*')

reason: without select() id from shop_products_options can be selected and hydrated into Product model.

2.The second problem is that you need to worry about groupBy.

->groupBy('products .id');

reason: if the relation is HasOne and there are more than one shop_products_options for the product, the query will return more rows for products.

3.The third problem is that you need to change all other where clauses from :

->where('date', $date)

to

->where('products .date', $date)

reason: products and shop_products_options can both have "date" attribute and in that case without selecting attribute with table "ambiguous column" error will be thrown.

4.The fourth problem is that you are using table names(not models) and this is also bad and awkward.

->where('products.date', $date)

5.The fifth problem is that you need to worry about soft deletes for joined tables. If the shop_products_options is using SoftDeletes trait you must add :

->where('shop_products_options .deleted_at', '=', null)

All above problems are very frustrating and joining tables with eloquent can introduce to your code many problems. I created a package which takes care of all above problems and you can sort by relationship attribute with elegant way, for your case it would be :

$products = Shop\Product::orderByJoin('options.pinned', 'desc')->paginate(5);

For more info see https://github.com/fico7489/laravel-eloquent-join

score 5 · Answer 3 · answered Jul 02 '21 at 18:47

5

There is another way for +6 Laravel versions with Subquery Ordering:

$products = Shop\Product::orderByDesc(
        Shop\Options::select('pinned')
            ->whereColumn('product_id', 'shop_products.id')
            ->orderByDesc('pinned')
            ->limit(1)
)->paginate(5);

answered Jul 02 '21 at 18:47

Ramy Herrira

574
10
13

Wow! Just Wow! Thank you @Ramy, you saved my week. I was finding that solution for about many days. Thank you again for sharing! – Asif Ali Aug 10 '21 at 18:55
1

Glad it helped ! – Ramy Herrira Aug 11 '21 at 07:37

score 0 · Answer 4 · answered Aug 18 '22 at 21:39

0

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

answered Aug 18 '22 at 21:39

saifulmasud

9
2

MD40 · Answer 5 · 2022-10-25T02:31:22.947

This might help others.

$products = Shop\Product::with(['options' => function ($query) {

    $query->orderBy('pinned', 'desc'); 

    }])->paginate(5);

This is only sorting the options table data due to the function only belongs to the options table data. To sort the Product table data need to add the code below. This will sort the options and products separately.

    $products = Shop\Product::with(['options' => function ($query) 
                {
                     $query->orderBy('pinned', 'desc'); 
                }])
                ->orderby('what ever...','desc')
                ->paginate(5);

Laravel Eloquent sort by relation table column

5 Answers5

Linked