Ralph
Ralph

Reputation: 897

Laravel - Eloquent Joins

I'm just starting to learn laravel and its orm system, Eloquent. I'm not sure how to correctly join tables with it though. I have 3 models: User, Account, AccountType and I don't know how to join Account with Accounttype.

Account Model Class:

public function accounttype(){
    return $this->hasOne('AccountType');
} 

This is to pass accounts information to view:

$accounts = User::find(Auth::user()->id)->accounts;
$accounts->load('accounttype');
return View::make('accounts.accounts')->with('accounts', $accounts);

That will give the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'accounttypes.account_id' in 'where clause' (SQL: select * from accounttypes where accounttypes.account_id in (1, 2, 3))

It should be pulling by id in the accounttypes table not account_id. I can list the user accounts just fine, I just don't know how to join the accounttypes table to the account table.

Database Tables:

Users

Accounts

AccountTypes

Each user can have more than one account and each account will have an account type.

Upvotes: 2

Views: 494

Answers (1)

The Alpha
The Alpha

Reputation: 146269

In User model:

public function accounts()
{
    return $this->hasMany('Account');
}

In Account model:

public function user()
{
    return $this->belongsTo('User');
}

public function accountType()
{
    return $this->belongsTo('AccountType', 'accounttype_id', 'id');
}

In AccountType model:

public function accounts()
{
    return $this->hasMany('Account', 'accounttype_id', 'id');
}

Then in your Controller:

// This will return a user with all accounts ($user->accounts will be collection)
$user = User::with('accounts')->find(Auth::user()->id);

Or:

$user = User::with('accounts.accountType')->find(Auth::user()->id);

// You may pass the $user as well and then loop the $user->accounts in view
return View::make('accounts.accounts')->with('accounts', $user->accounts);

In your view you may loop to get all accounts like:

@foreach($accounts as $account)
    {{ $account->name }}
    {{ $account->accountType->name }}
@endforeach

Since $user->accounts is a collection so you may either run a loop or specifically get an account using something like this:

{{ $accounts->first()->name }}; // Get first one's name
{{ $accounts->get(0)->name }}; // Get first one's name
{{ $accounts->get(1)->name }}; // Get second one's name
{{ $accounts->last()->name }}; // Get last one's name

if you pass the $user instead of $accounts like this:

return View::make('accounts.accounts')->with('user', $user);

Then change the loop as well, like this:

@foreach($user->accounts as $account)
    {{ $account->name }}
    {{ $account->accountType->name }}
@endforeach

You can make sure if that user has accounts in the view before you start the loop, like:

@if($user->accounts->count())
    // Loop Here
@endif

Upvotes: 3

Related Questions