Laravel - Fetch All Grandchildren directly (or as array)-CodePudding

Simplified version of my schema:

students

student_bursaries

id
student_id

student_bursary_enrolments

id
student_bursary_id

student_bursary_enrolments_courses

id
student_bursary_enrolment_id
course

I want to have a public function on the Student model so that I can fetch all its courses directly.

eg:

$id = 80;
$student = Student::findOrFail($id);
$courses = $student->courses();

I tried something like this:

public function courses() {
    return $this
        ->join('student_bursaries','student_bursaries.student_id','=','students.id')
        ->join('student_bursary_enrolments','student_bursary_enrolments.student_bursary_id','=','student_bursaries.id')
        ->join('student_bursary_enrolment_courses','student_bursary_enrolment_courses.student_bursary_enrolment_id','=','student_bursary_enrolments.id')
        ->select('student_bursary_enrolment_courses.id','student_bursary_enrolment_courses.course');
}

But that just returns 'all the courses', not the ones specific to $this - so I would have to pass it an id, which kind of defeats the point of "$this".

That would be the ultimate solution, but I am willing to go another route and doing something thats not part of the model, like so:

$records = Student::with(['bursaries','bursaries.enrolments','bursaries.enrolments.courses'])->where('id',80)->get();

But that returns a bunch of models, I only want a list of course names and their ID, so I tried the following, but it didnt work:

$records = Student::with(['bursaries','bursaries.enrolments','bursaries.enrolments.courses'])->where('id',80)->get()->pluck('bursaries.enrolments.courses.id','bursaries.enrolments.courses.course');

Result:

$records = Student::with(['bursaries','bursaries.enrolments','bursaries.enrolments.courses'])->where('id',80)->get()->pluck('bursaries.enrolments.courses.id','bursaries.enrolments.courses.course');

=> Illuminate\Support\Collection {#10344
 all: [
   "" => null,
 ],

}

CodePudding user response：

since you need the result as a collection of courses, you should start with that model

public function courses() {
    return StudentBursaryEnrolmentsCourse::whereHas('enrolment', function($enrolment) {
        $enrolment->whereHas('bursary', function($bursary) {
            $bursary->where('student_id', $this->id);
        });
    })->get();
}

Or independantly from the context $this

$studentId = 80;
$courses = StudentBursaryEnrolmentsCourse::whereHas('enrolment', function($enrolment) use ($studentId) {
    $enrolment->whereHas('bursary', function($bursary) use ($studentId) {
        $bursary->where('student_id', $studentId);
    });
})->get();

This assumes that (you can change them to your actual setup):

Course class = StudentBursaryEnrolmentsCourse
Enrolments relation in course class = enrolment
butsary relation in enrolments class = bursary

You can use pluck() before the get() like this

$studentId = 80;
$courses = StudentBursaryEnrolmentsCourse::whereHas('enrolment', function($enrolment) use ($studentId) {
    $enrolment->whereHas('bursary', function($bursary) use ($studentId) {
        $bursary->where('student_id', $studentId);
    });
})->pluck('courses.id','course');