php Laravel Eloquent 按关系表列排序

Laravel Eloquent sort by relation table column

提问by Sacha

I tried to sort products from shop_productstable by pinnedcolumn from shop_products_optionstable:

我试图从shop_products表格中按pinned列对shop_products_options表格中的产品进行排序：

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

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

}])->paginate(5);

I set relation in Shop\Product model:

我在 Shop\Product 模型中设置了关系：

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

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

但产品没有排序。我得到一个仅适用于shop_products_options表的查询。

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?

如何解决？

回答by Jarek Tkaczyk

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

SELECTclause is there in order to not appending joined columns to your Productmodel.

SELECT子句是为了不将连接的列附加到您的Product模型中。

---

---

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

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

回答by fico7489

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

如果没有手动加入相关表，则无法按相关表列进行排序。Jarek 的回答是正确的，但这可能真的很尴尬：

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

1.第一个问题是你需要担心选择。

->select('products.*')

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

原因：如果没有 select() id from shop_products_options 可以选择并水合到产品模型中。

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

2.第二个问题是你需要担心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.

原因：如果关系是 HasOne 并且产品有多个 shop_products_options，查询将返回更多行产品。

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

3.第三个问题是您需要更改所有其他where子句：

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

原因： products 和 shop_products_options 都可以有“date”属性，在这种情况下，如果没有选择表“模糊列”的属性，将会抛出错误。

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

4.第四个问题是你使用的是表名（不是模型），这也很糟糕和尴尬。

->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 :

5.第五个问题是你需要担心连接表的软删除。如果 shop_products_options 使用 SoftDeletes 特性，您必须添加：

->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 :

以上所有问题都非常令人沮丧，使用 eloquent 连接表可能会给您的代码带来许多问题。我创建了一个包来处理上述所有问题，您可以以优雅的方式按关系属性进行排序，对于您的情况，它将是：

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

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

有关更多信息，请参阅https://github.com/fico7489/laravel-eloquent-join