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.

• Sᴀᴍ Onᴇᴌᴀ: Ahoy! Please edit to explain the relationships more thoroughly. Is there a one-to-many relationship between questions and answers? and also does an answer belong to an appointment, separately from through the question or using a belongsToThrough relationship? You could consider using the format from this post
• user3402600: you can add a different relation or a scope within questions model or consider using a builder class instead. Re builder class, dont know the scale of your app - but, ask yourself a question - what will happen you need to change patient_id to something different?
• Ozan Kurt: Why do answers belongTo appointments? Shouldn't it be just answer->question->form->appointment?

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.