php - Laravel Eloquent query unexpected result -

i've found query result unexpected.

it's laravel 5.2

we have following entity:

user method:

public function roles() : belongstomany {     return $this->belongstomany(role::class)->withpivot('timestamp'); } 

each user can have many roles, have role entity (but doesn't matter in question) , pivot table user_role timestamp field (and ids of course), because hold information time, when user achieved specific role.

i want users theirs last assigned role

when create query (in user context in repository):

$this->with(['roles' => function($query) {     $query->orderby('timestamp', 'desc'); }])->all(); 

the result contain users roles entities inside ordered timestamp - it's ok. want retrieve one last role inside each user entity not all ordered.

so...

$this->with(['roles' => function($query) {     $query->orderby('timestamp', 'desc')->limit(1); }])->all(); 

and retrieve users user achieved role last time contains it! other users have roles field containing empty array.

why ordering performed on each users relation separately, when added limit behaved global limit all.

it drives me crazy...

thanks advices.

edit

i've created lastroles() method roles ordered desc. all, retrieving one impossible.

public function lastroles() : belongstomany {     return $this->belongstomany(roles::class)->withpivot('timestamp')->latest('timestamp'); } 

and testing:

$users = (new user())->with('lastroles')->get(); 

but must iterate on users , invoke lastroles() on each one:

foreach ($users $user) {     var_dump($user->lastroles()->get()->first()->name); } 

then retrieve names of latest roles assigned each user.

so... there no way in one query? way?

for work, need helper function:

public function latestrole() {  return $this->hasone(role::class)->withpivot('timestamp')->orderby('timestamp', 'desc'); } 

and then:

$this->with('latestrole')->get(); 

credits awesome article.