Display two tables that has one-to-many relationship

Question

When I click the button to filter the respondent table, I want to show the data to which the respondent belongs in the household table. The household table has a one-to-many relationship with the respondent table, and I want to filter the household table based on the selection made in the respondent table.

Controller

public function retrieveRowFromDatabase(Request $request)
{
    $ages = Age::all();
    $conditions = PhysicalCondition::all();
    $SDOHs = SDOH::all();
    $categories = SDOHCategory::all();
    $sexes = Sex::all();
    $diseases = Disease::all();
    $vaccines = Vaccine::all();
    $envHazards = EnvHazard::all();
    $puroks = Purok::all();
    $households = HouseholdHead::with('categoryHouseholds.sdoh_category')->get();
    
    $disease = $request->input('disease');
    $start = $request->input('start');
    $end = $request->input('end');
    
    $respondents = Respondent::where('disease_id', $disease)
        ->when($start && $end, function ($query) use ($start, $end) {
            $query->whereBetween('created_at', [$start, $end]);
        })
        ->get();

    $selectedRespondentId = $request->respondents;
    $selectedRespondent = Respondent::find($selectedRespondentId);

    if ($selectedRespondent) {
        $household = $selectedRespondent->household_head_id;

        return view('map', compact('household'));
    } else {
        return redirect()->back()->with('error', 'Respondent not found');
    }
    
    return view('map',
        compact('households', 'SDOHs', 'categories', 'diseases', 'vaccines', 'envHazards', 'respondents', 'ages',
            'puroks', 'conditions', 'sexes'));
}

Answer 1

HouseholdHead::query()
  ->with('categoryHouseholds.sdoh_category')
  ->when(!empty($request->respondents), function (Builder $query) use ($request) {
    $query->whereHas('respondents', function (Builder $query) use ($request) {
      $query->whereIn('id', $request->respondents); // assuming respondents is an array of ids
    });
  })
  ->get();