Using controllers to retrieve data from multiple tables

Question

Currently I have a SQLite database with 3 tables; accounts, interactions and checklists. In the accounts controller I have it pulling data from all three tables to display the content.
The accounts table has multiple accounts stored in it and the interactions table is linked through a foreign key (account_id).
On the accounts page I want only the interactions that have the same account id to be listed on the corresponding account. at present I have them appearing in the view, but the view is currently listing every interaction in the table, I know this is because I'm using Interaction::all() But I've been unable to figure out how exactly to do this, any help would be greatly appreciated.

• please note, I'm not using models.
• Each account can have many interactions

accountController

    public function show($id)
{
    $account = Account::find($id);

    $checklists = Checklist::where('account_id', $id)->first();
    //$interactions = Interaction::get();
    $interactions = Interaction::all();
    //dd($interactions);


    return view('account_view')->with('account', $account)->with('checklists', $checklists)->with('interactions', $interactions);

}

Answer 1

Do you have model relationships set up in your models? if so, you can load the related objects using with:

$account = Account::with('interactions', 'checklist')->find($id);
return view('account_view', compact("account"));

In your blade file:

$account;
$account->checklist; //your checklist with account_id of $account->id
$account->interactions; //your interactions with account_id of $account->id

To set up the relations in your Account model:

public function checklist(){
  return $this->hasOne(Checklist::class);
}
public function interactions(){
  return $this->hasMany(Interaction::class);
}