php - 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 = ShopProduct::with(['options' => function ($query) {

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

}])->paginate(5);

I set relation in ShopProduct model:

public function options()
{
    return $this->hasOne('ShopOptions');
}

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?

Answer 1

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

$products = ShopProduct::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.