Laravel Eloquent 按关系表列排序

2021-12-26 00:00:00 orm php laravel eloquent

我尝试通过 shop_products_options 表中的 pinned 列对 shop_products 表中的产品进行排序:

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

我在 ShopProduct 模型中设置了关系:

I set relation in ShopProduct model:

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

但是产品没有排序.我得到一个仅适用于 shop_products_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

如何解决?

推荐答案

急切加载使用单独的查询，因此您需要加入:

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

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

根据@alexw 评论 - 如果需要，您仍然可以包含连接表中的列.您可以将它们添加到 select 或调用 addSelect/selectRaw 等

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.

相关文章