Eloquent pivot table belongsToMany() with orderBy()

I'm trying to figure out how to sort videos based on the pivot favoritestable.

My relationship from within the Lecture model is as follow:

public function favorites()
{
    return $this->belongsToMany(User::class, 'favorites')
                ->withTimestamps();
}

Now, is there a way to specify the order in which you'd like the records to be returned - whether by the field in the pivot table or one of the fields on either of the models that share the record?

JarekTkaczyk

10 years ago

Level 53

@sebastiansulinski Your question is a bit broad, so let me split the answer:

You can order related models by pivot table's field:

1.1. You can add orderBy to the relation itself in order to get the related models sorted automatically (for example when you call $lecture->favorites, but I'd rather not do this, since if you need different order sometime, it will be impossible.

1.2. You can add orderBy on the fly, eg.:
```
$favorites = $lecture->favorites()->orderBy('something')->get();
// or while eager loading:
Lecture::with(['favorites' => function ($q) {
    $q->orderBy('something');
}])->find($id);
```

You can't order both models at once, like this:

$lectures = Lecture::with('favorites')->orderBy('something_on_pivot_table'); // won't work

because the tables are not joined. So, in order to sort by related model and/or pivot table, you need join:

Lecture::join('pivot_table', ...)->orderBy('something_on_pivot_table')->select('lectures.*')->get();

You still need to add the same code as before in order to sort related favorites if you want to eager load and sort them as well.

3 likes

sebastiansulinski

10 years ago

Level 3

Thanks @JarekTkaczyk - I'll give it a try - I think the join method is the one that will give me what I'm after, but I have a feeling that it might make the query pretty slow - will see.

Please or to participate in this conversation.