Laravel Eloquent 按关系表列排序

发布于05月08日

我试着从shop_products_options个表中的shop_products个表中按pinned列对产品进行排序:

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

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

}])->paginate(5);

我在Shop\Product Model中设置关系:

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

但产品没有分类.我得到一个只适用于shop_products_options个表的查询.

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

如何修复它？

推荐答案

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子句是为了不将连接的列附加到Product模型.

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.