Laravel渴望按关系排序 [英] Laravel eager loading sort by relationship

问题描述

我有一些关系(希望可以正确解释)，并且需要按照本质上是远距离的关系对输出进行排序.

I have some relationships (that i can hopefully explain correctly) and need to sort the output by what is essentially a distant relation.

我有一个数据透视表，其中包含许多关系的详细信息，包括我要排序的关系.

I have a pivot table that contains details for many of the relations, including that that i want to sort by.

-User.php

public function players()
{
    return $this->belongsToMany('App\Models\Player', 'league_player_user')->withPivot('position_id');
}

-Player.php

--Player.php

public function position()
{
    return $this->belongsToMany('App\Models\Position', 'league_player_user');
}

我会渴望像这样加载关系；

I will be eager loading the relationship like so;

$user = User::with('players')->where('id', Auth::user()->id)->first();

所以，我想我想做这样的事情(不起作用).

So, i think i want to do something like this (does not work).

$user = User::with(['players' => function($q){
    $q->orderBy('position.sort_id', 'asc');
}])->where('id', Auth::user()->id)->first();

关键的Table结构看起来像这样；

The pivotal Table structure looks a little like this;

league_player_user.
...league_id
...player_id
...user_id
...position_id

职位"表包含sort_id

The Positions table contains the sort_id

希望这是足够的信息，请根据需要提供更多信息.非常感谢.

Hopefully this is enough information, please request more if needed. Many thanks.

推荐答案

好，因此，您尝试获取一个用户，但其玩家(用户bTM玩家)已按位置(枢纽bT位置)进行了填充和排序

Okay so you're trying to get a single user but with their players (where users bTM players) already populated and ordered by position (where pivot bT position).

在这种情况下，您将无法使用Laravel的内置关系方法而无需进行修改，因为Laravel并不是为处理其他关系的数据透视表上的关系而构建的.幸运的是，ORM足够灵活，可以让您通过手动"联接来做自己想做的事情.

In this case you will not be able to use Laravel's inbuilt relationship methods without modification, because Laravel just wasn't built to hand relationships that are on the pivot table of other relationships. Luckily, the ORM is flexible enough to allow you to do what you want with a 'manual' join.

因此，要直接回答您的问题，这是您需要的那种代码(您真的很亲密！):

So to answer your question directly, here's the kind of code you require (you were really close!):

$user = User::with(['players' => function ($q) {
    $q->join('position', 'league_player_user.position_id', '=', 'position.id')
      ->orderBy('position.sort_id', 'asc');
}])->where('id', Auth::user()->id)->first();

但是，由于某些原因，在我看来这不是很好的代码:

However, it appears to me that that's not great code for a few reasons:

1. 您正在手动获取授权用户(在方便方便的情况下)
2. 实际上，您必须从模型中获取特定于实现的逻辑(事实是，枢轴表称为league_player_user，并将其放置在...无论位于何处(您的控制器?)
3. 这只会影响单个查询-如果您碰巧以其他方式(例如Auth::user()或User::find(1)或其他方式)获得了User，您将无法正确订购玩家

1. You're manually getting the authorised user (when Auth::user() is so convenient)
2. You're actually having to take implementation-specific logic from the model (the fact that the pivot table is called league_player_user and put it... well wherever this is (your controller?)
3. This will only affect this one single query - if you happened to get a User some other way (e.g. Auth::user() or User::find(1) or whatever) you won't have the players ordered correctly

这样，我可能建议您为使查询更简单而不要急于加载玩家.因此，您需要做的所有事情是:

As such, might I suggest that for the purposes of making your query more simple you don't eager load the players. As such, all you'll need to do upfront is:

$user = Auth::user();

现在，在关系上(User类中的players()方法)，您可以执行特殊的订购工作:

Now, on the relationship (the players() method in your User class) you do the special ordering work:

public function players()
{
    return $this->belongsToMany('App\Models\Player', 'league_player_user')
                ->withPivot('position_id')
                ->join('position', 'league_player_user.position_id', '=', 'position.id')
                ->orderBy('position.sort_id');
}

这样，您每次致电$user->players时，都可以正确订购它们.

This way, any time you call $user->players, you will get them ordered correctly.

我必须补充一点，由于Laravel渴望加载查询的方式，这种方式可能不允许您渴望加载玩家，因为Laravel渴望加载(即在ORM链中使用->with())主要查询(即用户)和关系(即玩家)查询，但是它执行此查询以获取所有结果，因此可能无法在没有特殊订购系统的情况下使用.您必须查看您是否真的很想加载玩家.在上述情况下(您获得了单个授权用户)，我认为急切加载并不那么重要.

I must just add that doing it this way may not allow you to eager load the players, as Laravel's eager loading (i.e. using ->with() in the ORM chain) due to the way that Laravel does the eager loading queries - one for main query (i.e. users) and one for the relationship (i.e. players) but it does this query to get all results, so won't work with out special ordering system possibly. You'll have to see if you really care about eager loading the players. In your case above (where you're getting the single authorised user), eager loading is not as important in my opinion.

编辑以添加有关急切加载的说明:

Edit to add clarification regarding eager loading:

我暗示过急加载可能不起作用的原因是，在Laravel中进行过急加载有点像这样:说您有类别和产品:Category hM Product，Product bT Category.要获取类别，请使用$category = Category::find(1)，Laravel会将其转换为如下查询:SELECT * FROM `categories` WHERE `id` = '1'.如果您随后致电$category->products，Laravel将发出SELECT * FROM `products` WHERE `category_id` = '1'.这很明智.但是，如果您有以下代码，那就更糟了:

My reasoning behind suggesting that eager loading may not work is that eager loading in Laravel is done kinda like this: say you have categories and products: Category hM Product, Product bT Category. To get a category you use $category = Category::find(1) and Laravel turns that into a query a bit like this: SELECT * FROM `categories` WHERE `id` = '1'. If you then call $category->products Laravel will issue SELECT * FROM `products` WHERE `category_id` = '1'. That's sensible. But if you had the following code it'd be worse:

<?php $categories = Category::all(); ?>

<ul>
    @foreach ($categories as $category)
        <li>Category {{{ $category->name }}} contains {{{ $category->products->count() }}} products.</li>
    @endforeach
</ul>

在这种情况下，您将有以下查询:

In that case you'd have the following queries:

SELECT * FROM `categories`;
SELECT * FROM `products` WHERE `category_id` = '1';
SELECT * FROM `products` WHERE `category_id` = '2';
SELECT * FROM `products` WHERE `category_id` = '3';
... as many categories as you had

但是，如果要将第一行更改为此:

However, if you were to change that first line to this:

<?php $categories = Category::with('products')->get(); ?>

现在您只有两个查询:

SELECT * FROM `categories`;
SELECT * FROM `products` WHERE `category_id` IN ('1', '2', '3', ...);

然后，Laravel在调用第二个查询后，将根据它知道的类别ID为您创建各种集合.

Laravel then, after calling the second query, will create the various collections for you based on the category IDs it knows you have.

但是，这是简单的关系案例.在您的情况下，products()方法不仅是return $this->hasMany('Product');，还包括数据透视表和手动联接等，并且第二个查询(渴望加载一个)可能无法应付并正确地进行排序.

However, that's the simplistic relationship case. In your case, the products() method is not just return $this->hasMany('Product'); but it includes a pivot and a manual join, etc., and it's possible that that second query, which is the eager loading one, just won't be able to cope and do that ordering properly.

正如我所说，我不确定这是如何工作的，对我来说只是一个危险信号.一定要去看看它所得到的-您可能会发现它对您有用.

As I said, I don't know for certain how this works, it's just a bit of a red flag for me. By all means give it a go and see what you get - you may find that it works for you.

这篇关于Laravel渴望按关系排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！