many-to-many relationship: order by on pivot table not working

Question

I have these relationship between school and associate models:

// School model
public function associates()
{
    return $this->belongsToMany('Associate', 'school_associate', 'school_id', 'associate_id')
        ->withPivot('start_date', 'end_date');
}

// Associate model
public function schools()
{
    return $this->belongsToMany('School', 'school_associate', 'associate_id', 'school_id')
        ->withPivot('start_date', 'end_date');
}

I need to get all associates of one school ordered by start_date.

This is what I tried without success (in this try I am searching in all schools):

dd(\App\Associate::with(['schools' => function ($q) {
    $q->orderBy('pivot_start_date', 'desc');
}])->toSql());

And I get this sql (notice no order by clause):

select * from `associate`

I tried to edit the relationship like this:

// Associate model
public function schools()
{
    return $this->belongsToMany('School', 'school_associate', 'associate_id', 'school_id')
        ->withPivot('start_date', 'end_date')
        ->orderBy('pivot_start_date', 'desc'); // also tried without "pivot_"
}

And according to this post, I also tried :

// Associate model
public function schools()
{
    return $this->belongsToMany('School', 'school_associate', 'associate_id', 'school_id')
        ->withPivot('start_date', 'end_date')
        ->orderBy('school_associate.start_date', 'desc');
}

But I always get the same query and the results are not ordered.

Answer 1

I solved using query builder in this way.
This function is in Associate model:

public function scopeLast($query, $school_ids = [])
{
    $query->join('school_associate', "{$this->table}.{$this->primaryKey}", '=', 'school_associate.associate_id')
        ->join('school', 'school.school_id', '=', 'school_associate.school_id')
        ->whereIn('school.school_id', $school_ids)
        ->orderBy('school_associate.start_date', 'desc');

    return $query;
}