Order by pivot table created_at in Laravel

Question

In my database, I have the following tables:

1. courses (id, name, created_at, updated_at)
2. students (id, name, created_at, updated_at)
3. course_student (id, course_id, student_id, created_at, updated_at)

I'm trying to retrieve all the courses in which a student has enrolled order detrimentally by the created_at value in the pivot table course_student

This is how I defined my models:

Student model:

   public function students () {
    return $this->belongsToMany(Student::class, 'course_student', 'course_id', 'student_id')->withTimestamps();
}

Course model:

public function courses () {
    return $this->belongsToMany(Course::class, 'course_student', 'student_id', 'course_id')->withTimestamps();
}

And this is what I tried in my controller but it's not working.

public function enrolled() {
    $courses = Course::whereHas('students', function($query) {
        $query->where('user_id', auth()->id())
            ->orderBy('course_student.created_at','desc');
        })->get();

    return view('courses.enrolled', compact('courses'));
}

any idea how can I accomplish this?

Answer 1

Get the courses from the student:

$student = Student::where('user_id', auth()->id())->first();
$courses = $student->courses()->orderByDesc('course_student.created_at')->get();