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-20 03:43:32Z

82

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, 2016 at 3:43

answered May 8, 2014 at 10:27

Jarek Tkaczyk

81.4k27 gold badges167 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gravy Over a year ago

Perfect - Also, without the select, the shop_products_options.id will overwrite the value of the products.id

Jarek Tkaczyk Over a year ago

@Gravy Exactly, joined table fields will override all the fields from the main table having the same name.

Mirage Over a year ago

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.

alexw Over a year ago

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.

Jarek Tkaczyk Over a year ago

@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.

|

fico7489 · Accepted Answer · 2017-12-17 19:08:34Z

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

Ramy Herrira · Accepted Answer · 2021-07-02 18:47:08Z

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 2, 2021 at 18:47

Ramy Herrira

62412 silver badges15 bronze badges

2 Comments

Asif Ali Over a year ago

Wow! Just Wow! Thank you @Ramy, you saved my week. I was finding that solution for about many days. Thank you again for sharing!

Ramy Herrira Over a year ago

Glad it helped !

saifulmasud · Accepted Answer · 2022-08-18 21:39:24Z

0

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

answered Aug 18, 2022 at 21:39

saifulmasud

72 bronze badges

Comments

MD40 · Accepted Answer · 2022-10-25 02:31:22Z

0

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

edited Oct 25, 2022 at 2:31

answered Oct 25, 2022 at 2:23

MD40

3253 silver badges15 bronze badges

Collectives™ on Stack Overflow

Laravel Eloquent sort by relation table column

5 Answers 5

6 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related