php - Laravel eager loading sort by relationship -

i have relationships (that can explain correctly) , need sort output distant relation.

i have pivot table contains details many of relations, including that want 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 eager loading relationship so;

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

so, think want (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 little this;

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

the positions table contains sort_id

hopefully enough information, please request more if needed. many thanks.

okay you're trying single user players (where users btm players) populated , ordered position (where pivot bt position).

in case not able use laravel's inbuilt relationship methods without modification, because laravel wasn't built hand relationships on pivot table of other relationships. luckily, orm flexible enough allow want 'manual' join.

so answer question directly, here's kind of code require (you 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, appears me that's not great code few reasons:

1. you're manually getting authorised user (when auth::user() so convenient)
2. you're having take implementation-specific logic model (the fact pivot table called league_player_user , put it... wherever (your controller?)
3. this affect 1 single query - if happened user other way (e.g. auth::user() or user::find(1) or whatever) won't have players ordered correctly

as such, might suggest purposes of making query more simple don't eager load players. such, you'll need upfront is:

$user = auth::user(); 

now, on relationship (the players() method in user class) 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, time call $user->players, them ordered correctly.

i must add doing way may not allow eager load players, laravel's eager loading (i.e. using ->with() in orm chain) due way laravel eager loading queries - 1 main query (i.e. users) , 1 relationship (i.e. players) query results, won't work out special ordering system possibly. you'll have see if care eager loading players. in case above (where you're getting single authorised user), eager loading not important in opinion.

---

edit add clarification regarding eager loading:

my reasoning behind suggesting eager loading may not work eager loading in laravel done kinda this: have categories , products: category hm product, product bt category. category use $category = category::find(1) , laravel turns query bit this: select * `categories` `id` = '1'. if call $category->products laravel issue select * `products` `category_id` = '1'. that's sensible. if had following code it'd worse:

<?php $categories = category::all(); ?>  <ul>     @foreach ($categories $category)         <li>category {{{ $category->name }}} contains {{{ $category->products->count() }}} products.</li>     @endforeach </ul> 

in case you'd have following queries:

select * `categories`; select * `products` `category_id` = '1'; select * `products` `category_id` = '2'; select * `products` `category_id` = '3'; ... many categories had 

however, if change first line this:

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

now you'd have 2 queries:

select * `categories`; select * `products` `category_id` in ('1', '2', '3', ...); 

laravel then, after calling second query, create various collections based on category ids knows have.

however, that's simplistic relationship case. in case, products() method not return $this->hasmany('product'); includes pivot , manual join, etc., , it's possible that second query, eager loading one, won't able cope , ordering properly.

as said, don't know how works, it's bit of red flag me. means give go , see - may find works you.