Eloquent eager loading deep nested relation with "where" condition

Question

is there a more elegant way to do what the code below does without use "foreach"? I am trying to load answers from questions but only the answers that contains a direct relation with an Appointment

public function show(Request $request, Patient $patient)
{
    $user = $request->user();

    $appointments = Appointment::with('forms.questions')
        ->where('patient_id', $patient->id)
        ->where('user_id', $user->id)
        ->get();

    foreach ($appointments as $appointment) {
        foreach ($appointment->forms as $form) {
            foreach ($form->questions as $question) {
                $question->load(['answers' => function ($q) use ($appointment) {
                    $q->where('appointment_id', $appointment->id);
                }]);
            }
        }
    }

    return view('patients.show', compact('patient', 'appointments'));
}

I tried with no luck

public function show(Request $request, Patient $patient)
{
    $user = $request->user();

    $appointments = Appointment::with(['forms.questions.answers' => function ($query) {
        $query->where('answers.appointment_id', 'appointments.id')
    }])
        ->where('patient_id', $patient->id)
        ->where('user_id', $user->id)
        ->get();


    return view('patients.show', compact('patient', 'appointments'));
}

EDIT: Below are the relationships

• Form

  • questions (hasMany)
  • appointments (belongsToMany)

• Question

  • answers (hasMany)

• Answer

  • question (belongsTo)
  • appointment (belongsTo)

• Appointment

  • answers (hasMany)
  • forms (belongsToMany)
  • patient (hasOne)
  • user (hasOne)

• Patient

  • user (BelongsTo)
  • appointments (hasMany)

• User

  • appointments (hasMany)

A table called appointment_form is a pivot table for appointments and forms table.

Answer 1

The solution below comes after rethinking the actual implementation of the business logic.

Since one Appointment can have zero or many Answers, I do not need to relate Appointments with a Form.

All I have to do is only save Answers with the reference of Question and Appointment.

And on appointments.show when I need only the Forms (with Questions) that have Answers related with current Appointment just do:

$fromAppointment = function ($builder) use ($appointment) {
    $builder->where('appointment_id', $appointment->id);
};

$forms = Form::whereHas('questions.answers', $fromAppointment)
    ->with(['questions.answers' => $fromAppointment])
    ->get();

And on appointments.edit when I need all the Forms even those that do not have Answers, all I need to do is:

$forms = Form::with(['questions.options', 'questions.answers' => function ($builder) use ($appointment) {
    $builder->where('appointment_id', $appointment->id);
])
->get();

And the relationship between Appointments and Forms was dropped.