Laravel eager loading sort by relationship

Question

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

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

The pivotal Table structure looks a little like this;

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

The Positions table contains the sort_id

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

Answer 1

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

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

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

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

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:

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

Now you'd only have two queries:

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

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

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.